Wrong results from a mergejoin

[Richard Guo]

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

v1-0001-Fix-lateral-references-in-sort-keys-added-by-crea.patch
Description: Binary data