Re: BUG #18852: Unexpected expression in subquery output - Mailing list pgsql-bugs

SELECT FROM a.f RIGHT JOIN b.h ON EXISTS (SELECT FROM a.e WHERE (SELECT
NULL) > g) LIMIT 9;

The join order of the above query is b.h left join (a.f semi join a.e). The NULL > g is pushdown to a.f rel.

When we process semijoin, the (NULL > g) is added into fpinfo->remote_conds, then it is added into the finfo->joinclauses

when we process the left join.

So in deparseFromExprForRel(), we should append the fpinfo->joinclauses to buf, then we will enter get_relation_column_alias_ids(node, innerrel, relno, colon)

in is_subquery_var(). The innerrel is joinrel of a.f and a.e, the node is var (g), but innerrel->reltarget->expr is NIL. So the error triggers.

The (null > g) should not be the leftjoin joinclauses, if I understand correctly. And I see the comments in foreign_join_ok():

...

else if (jointype == JOIN_LEFT || jointype == JOIN_RIGHT || jointype == JOIN_FULL)
{
/*
* Conditions, generated from semi-joins, should be evaluated before
* LEFT/RIGHT/FULL join.
*/

...

I add below code in foreign_join_ok() if jointype is LEFT:

case JOIN_LEFT:
if (bms_is_empty(fpinfo_i->hidden_subquery_rels))
            fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                                                fpinfo_i->remote_conds);

Then I get the plan like below:

postgres=# explain verbose SELECT FROM a.f RIGHT JOIN b.h ON EXISTS (SELECT FROM a.e WHERE (SELECT
NULL) > g) limit 1;
                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.01..100.21 rows=1 width=0)
   Relations: (b.h) LEFT JOIN ((a.f) SEMI JOIN (a.e))
   Remote SQL: SELECT NULL FROM (b.h r2 LEFT JOIN (SELECT NULL FROM a.f r1 WHERE (($1::text > r1.g)) AND EXISTS (SELECT NULL FROM a.e r4)) s5 ON (TRUE)) LIMIT 1::bigint
   InitPlan 1
     ->  Result  (cost=0.00..0.01 rows=1 width=32)
           Output: NULL::text
(6 rows)

I'm not very familiar with fdw codes. Any thoughts?