Query Generates Error "illegal rescan of motion node: invalid plan"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Certain queries generate the error below. This could mean the query includes a LATERAL JOIN (implicit or explicit), which is unsupported in Greenplum as of version 6.20.3.

ERROR:  illegal rescan of motion node: invalid plan (nodeMotion.c:1632)
HINT:  Likely caused by bad NL-join, try setting enable_nestloop to off

Setting the GUC "optimizer_trace_fallback" to on reveals the LATERAL JOIN.

set optimizer_trace_fallback=on;

<SELECT QUERY WITH LATERAL JOIN>

INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  Feature not supported: LATERAL
ERROR:  illegal rescan of motion node: invalid plan (nodeMotion.c:1632)
HINT:  Likely caused by bad NL-join, try setting enable_nestloop to off

 

Environment

Product Version: 6.20

Resolution

Possible workaround is to set GUC join_collapse_limit to 1: 

set join_collapse_limit = 1

Otherwise query needs to be rewritten to avoid LATERAL JOIN.