Hi!
> I think we should flatten join alias Vars in the subquery's targetlist
> and quals before the recheck. Attached is a WIP patch doing that.
I applied v1 and confirmed it works correctly. I also tested the
same master without v1. Without assertions, I found a pattern
where a query silently returns a wrong result rather than hitting
the Assert, so I wanted to share it. I think this warrants a back-patch.
=== Example
create table t (a int);
insert into t values (1),(2),(3),(NULL);
select t1.a, ss.x, t2.a
from t t1,
lateral (select (j is null)::int
from ((select t1.a) s left join (select 1) v on false) j)
ss(x)
left join t t2 on ss.x = t2.a;
This returns 1 row, but the correct answer is 4 rows. Unmatched rows
were being discarded. The wrong result seems to show up when both of these hold:
- a column that the LATERAL subquery itself outputs is used in the
ON clause of an outer join that has that LATERAL subquery on one side
- that join has rows with no match
With this patch, these cases seem to be covered too.
Regards,
Tatsuya Kawata
