| 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 |
| 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 |