Wrong results from a mergejoin

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Wrong results from a mergejoin
Date: 2026-10-13 01:32:57
Message-ID: CAMbWs49LWNm7_OxuW1V71wLi+Ev3KbfY1bMY36uydNKwSPnbtQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The query below gives wrong results with mergejoin:

create table t1 (a int);
create table t2 (a int);
create table t3 (a int);
insert into t1 values (1);
insert into t2 values (2);
insert into t3 values (100);
analyze t1, t2, t3;

set enable_hashjoin to off;
set enable_nestloop to off;

select * from t1
left join lateral (select t1.a from t3) s(x) on true
join t2 on s.x + 1 = t2.a;
a | x | a
---+---+---
(0 rows)

This should return one row: (1, 1, 2).

The problem is in prepare_sort_from_pathkeys(). When the sort key is
not in the input plan's targetlist, it adds a resjunk entry for it by
copying the EC member's expression as-is, without replacing lateral
references with nestloop Params. Here the sort key is "PHV(t1.a) +
1", and the t1.a inside the PHV is left as a plain Var in the
targetlist of the scan on t3. The executor then fetches it from the
t3 tuple, so the merge join ends up comparing t3.a + 1 with t2.a.

If the sort sits atop a join rather than a scan, setrefs.c notices the
stray Var and we get an error instead:

select 1 from t1
left join lateral (select t1.a, t3.a from t3, t3 t4) s(x, y) on true
join t2 on s.x + 1 = t2.a
where s.y is not null;
ERROR: variable not found in subplan target lists

The fix seems straightforward: run replace_nestloop_params() on the
expression before adding it to the targetlist. The annoying part is
that prepare_sort_from_pathkeys() doesn't have 'root', so we have to
pass it in from all its callers.

- Richard

Attachment Content-Type Size
v1-0001-Fix-lateral-references-in-sort-keys-added-by-crea.patch application/octet-stream 16.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Harrison Booth 2026-10-13 01:33:52 Re: Assorted Win32 error handling fixes (CreateThread, pgwin32_select, pg_usleep)
Previous Message Ayush Tiwari 2026-10-13 01:30:52 Re: [PATCH] Clear walsender reader pointers after cleanup