Re: BUG #18284: Filter in left lateral join not respected - Mailing list pgsql-bugs

From Richard Guo
Subject Re: BUG #18284: Filter in left lateral join not respected
Date
Msg-id CAMbWs48r0MMEQoG8NWm7MTjDb_fbvCGEoYt_XpurDbrvuB5ZtA@mail.gmail.com
Whole thread Raw
In response to Re: BUG #18284: Filter in left lateral join not respected  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: BUG #18284: Filter in left lateral join not respected  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs

On Fri, Jan 12, 2024 at 1:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Huh.  It looks like the oversight is actually even more ancient than
that, dating clear back to 9e7e29c75 of 2013-08-17.  That commit
recognized that lateral-reference Vars had to be wrapped in
PlaceHolderVars during subquery pullup, but failed to make the same
conclusion for PlaceHolderVars. 

Exactly.
 
Somehow that didn't cause any visible
problems before 4be058fe9, or more likely we just didn't get any
relevant trouble reports. 

I believe it should be the latter case.  It's not hard to show this
problem before 4be058fe9.

create table t (a int);
insert into t values (1);

# select t2a_lateral from t t1
    left join (select coalesce(t2.a) as a from t t2) s on true
    left join lateral (select s.a as t2a_lateral from t t3) ss on false;
 t2a_lateral
-------------
           1
(1 row)

The t2a_lateral is supposed to be NULL not 1.

Thanks
Richard

pgsql-bugs by date:

Previous
From: Andy Fan
Date:
Subject: Re: BUG #18280: logical decoding build wrong snapshot for subtransactions
Next
From: PG Bug reporting form
Date:
Subject: BUG #18286: "ERROR: could not open relation with OID 16391" error was encountered when reindexing