Horton

Thread

Commits

  1. Allow subquery pullup to wrap a PlaceHolderVar in another one.

  2. In the planner, replace an empty FROM clause with a dummy RTE.

  1. BUG #18284: Filter in left lateral join not respected

    The Post Office <noreply@postgresql.org> — 2024-01-11T14:34:55Z

    The following bug has been logged on the website:

Bug reference:      18284
Logged by:          Holger Reise
Email address:      holger.reise@vitagroup.ag
PostgreSQL version: 16.1
Operating system:   Alpine 13.2.1, 64-bit
Description:        

The following script returns two rows with `666`, whereas `null` is to be
expected:

WITH r1 AS (VALUES(null)),
r2 AS (VALUES(null), (null))
SELECT ljl.val_filtered
FROM r1
LEFT JOIN(
	SELECT j666.val FROM r2
	JOIN (SELECT 666 AS val) as j666 ON true
)AS lj_r2 ON true
LEFT JOIN LATERAL(
	SELECT lj_r2.val AS val_filtered
	WHERE false
) AS ljl ON true;

  2. Re: BUG #18284: Filter in left lateral join not respected

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-11T16:01:39Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> The following script returns two rows with `666`, whereas `null` is to be
> expected:

> WITH r1 AS (VALUES(null)),
> r2 AS (VALUES(null), (null))
> SELECT ljl.val_filtered
> FROM r1
> LEFT JOIN(
> 	SELECT j666.val FROM r2
> 	JOIN (SELECT 666 AS val) as j666 ON true
> )AS lj_r2 ON true
> LEFT JOIN LATERAL(
> 	SELECT lj_r2.val AS val_filtered
> 	WHERE false
> ) AS ljl ON true;

Hmph ... this has been broken for a good long while.  Bisecting
shows it gave the right answer before

4be058fe9ec5e630239b656af21fc083371f30ed is the first bad commit
commit 4be058fe9ec5e630239b656af21fc083371f30ed
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon Jan 28 17:54:10 2019 -0500

    In the planner, replace an empty FROM clause with a dummy RTE.

so I'm betting that missed a condition about when it is safe to
flatten RTE_RESULT RTEs.  Will look, thanks for the report!

			regards, tom lane

  3. Re: BUG #18284: Filter in left lateral join not respected

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-11T17:30:39Z

    I wrote:
> Hmph ... this has been broken for a good long while.  Bisecting
> shows it gave the right answer before
> commit 4be058fe9ec5e630239b656af21fc083371f30ed
> so I'm betting that missed a condition about when it is safe to
> flatten RTE_RESULT RTEs.  Will look, thanks for the report!

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.  Somehow that didn't cause any visible
problems before 4be058fe9, or more likely we just didn't get any
relevant trouble reports.  This seems to be quite a rare situation:
spot testing says that we never reach this code for a PHV with
target_rte->lateral true in any of our regression tests.

The attached seems to be enough to fix it, though of course it needs
a regression test.

			regards, tom lane

  4. Re: BUG #18284: Filter in left lateral join not respected

    Richard Guo <guofenglinux@gmail.com> — 2024-01-12T06:50:33Z

    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

  5. Re: BUG #18284: Filter in left lateral join not respected

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-12T14:59:39Z

    Richard Guo <guofenglinux@gmail.com> writes:
> On Fri, Jan 12, 2024 at 1:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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)

Ah, of course.  The previous test case had FROM-less subselects,
so prior to 4be058fe9 we couldn't flatten those and the bug didn't
manifest.  But with ordinary FROM clauses, boom!

Now I'm fairly astonished that this was never reported before.
You'd think in ten years somebody would've noticed.

			regards, tom lane