From: | Tender Wang <tndrwang(at)gmail(dot)com> |
---|---|
To: | tharakan(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18852: Unexpected expression in subquery output |
Date: | 2025-03-17 15:21:33 |
Message-ID: | CAHewXNmOEvrs0NNb3AwrF0eXZf=LpYWWqq6S_viyskUOmiJ+yA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
PG Bug reporting form <noreply(at)postgresql(dot)org> 于2025年3月17日周一 16:35写道:
> The following bug has been logged on the website:
>
> Bug reference: 18852
> Logged by: Robins Tharakan
> Email address: tharakan(at)gmail(dot)com
> PostgreSQL version: Unsupported/Unknown
> Operating system: Ubuntu
> Description:
>
> Hi,
>
> The following SQL returns this error:
>
> $ psql -f repro.sql >/dev/null
> psql:repro.sql:11: ERROR: unexpected expression in subquery output
>
> $ cat repro.sql
> CREATE SCHEMA a;
> CREATE SCHEMA b;
> CREATE EXTENSION postgres_fdw ;
> CREATE SERVER d FOREIGN DATA WRAPPER postgres_fdw;
> CREATE FOREIGN TABLE a.e () SERVER d;
> CREATE FOREIGN TABLE a.f (g text) SERVER d;
> CREATE FOREIGN TABLE b.h () SERVER d;
> SELECT FROM a.f RIGHT JOIN b.h ON EXISTS (SELECT FROM a.e WHERE (SELECT
> NULL) > g) LIMIT 9;
>
I can reproduce this issue on HEAD.
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?
--
Thanks,
Tender Wang
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Rasheed | 2025-03-17 23:35:59 | Re: BUG #18830: ExecInitMerge Segfault on MERGE |
Previous Message | Amit Langote | 2025-03-17 12:21:22 | Re: BUG #18830: ExecInitMerge Segfault on MERGE |