Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Add support for deparsing semi-joins to contrib/postgres_fdw
- 824dbea3e41e 17.0 cited

BUG #18852: Unexpected expression in subquery output

PG Bug reporting form <noreply@postgresql.org> — 2025-03-16T13:39:16Z

The following bug has been logged on the website:

Bug reference:      18852
Logged by:          Robins Tharakan
Email address:      tharakan@gmail.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;

- The issue surfaced after commit 824dbea3e41efa3b35094163c834988dea7eb139
[1]
Testing for start crashing
Checking (682c5be25c2~0) - 682c5be25c - fail
Checking (682c5be25c2~10) - 2b73a8cd33 - fail
Checking (682c5be25c2~30) - 3691edfab9 - fail
.
.
Checking (682c5be25c2~3470) - 278eb13c48 - pass
Checking (682c5be25c2~3450) - 7db01fbcef - fail
Checking (682c5be25c2~3460) - 719b342d36 - fail
Checking (682c5be25c2~3465) - c2a465b2c9 - fail
Checking (682c5be25c2~3467) - 7636725b92 - fail
Checking (682c5be25c2~3468) - bfc677c3bc - fail
Checking (682c5be25c2~3469) - 824dbea3e4 - fail
Surfacing Commit is 824dbea3e41efa3b35094163c834988dea7eb139

-
robins

Reference:
1.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=824dbea3e41efa3b35094163c834988dea7eb139

Re: BUG #18852: Unexpected expression in subquery output

Tender Wang <tndrwang@gmail.com> — 2025-03-17T15:21:33Z

PG Bug reporting form <noreply@postgresql.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@gmail.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

Re: BUG #18852: Unexpected expression in subquery output

Robins Tharakan <tharakan@gmail.com> — 2025-03-19T01:01:16Z

On Tue, 18 Mar 2025 at 01:51, Tender Wang <tndrwang@gmail.com> wrote:

>
> PG Bug reporting form <noreply@postgresql.org> 于2025年3月17日周一 16:35写道：
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      18852
>>
>>
> 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;
>


FWIW, there's additional discussion happening in the original thread -

https://www.postgresql.org/message-id/flat/CAPpHfdsf_9OBXS1KzPQi%2BTAnNWPZGAzZ67xKn4-5uWhbg2dj-Q%40mail.gmail.com#555e7e7264f7c879f70810bf5b49c66f


-
robins
https://robins.in