pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan

Hi,

While testing the optimiser extension that extends planner's scope by
pre-sorted outer paths I found that current master hit a crash in the
test_plan_advice TAP test
(src/test/modules/test_plan_advice/t/001_replan_regress.pl):

ERROR: plan node has no RTIs: 380

The error originates in pgpa_scan.c:

if (within_join_problem)
elog(ERROR, "plan node has no RTIs: %d", (int) nodeTag(plan));

It is triggered by the pg_lsn regression test query:

SELECT DISTINCT (i || '/' || j)::pg_lsn f
FROM generate_series(1, 10) i,
generate_series(1, 10) j,
generate_series(1, 5) k
WHERE i <= 10 AND j > 0 AND j <= 10
ORDER BY f;

I have the following query plan:

Unique
-> Nested Loop
-> Sort
-> Nested Loop
-> Function Scan on generate_series j
Filter: ((j > 0) AND (j <= 10))
-> Function Scan on generate_series i
Filter: (i <= 10)
-> Function Scan on generate_series k

The assumption baked into pg_plan_advice is that when walking a join
subtree (within_join_problem = true), every leaf node will be a
base-relation scan with RTIs. Before now, that assumption was always
valid: no PostgreSQL core code placed a Sort node between a NestLoop and
a FunctionScan. But extensions might want to employ more sorted paths to
find better plan - it might happen in complex analytics tasks. My case
is presorted outer side of a LEFT JOIN in case of ORDER-BY .. LIMIT
present on the outer table only.

I'm not aware about how this module is designed, but I think it should
not unconditionally error. A Sort injected between a join and a
non-relation scan leaf is a legitimate plan node that the walker should
handle gracefully in case it is loaded with other extensions.

Just for the reproduction, see the branch [1] over fresh PostgreSQL master.

[1] https://github.com/danolivo/pgdev/tree/bounded-left-join-outer

--
regards, Andrei Lepikhov,
pgEdge