Обсуждение: BUG #18284: Filter in left lateral join not respected
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;
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
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 diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index d0df5374ef..aa83dd3636 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -2435,8 +2435,13 @@ pullup_replace_vars_callback(Var *var, else if (newnode && IsA(newnode, PlaceHolderVar) && ((PlaceHolderVar *) newnode)->phlevelsup == 0) { - /* No need to wrap a PlaceHolderVar with another one, either */ - wrap = false; + /* The same rules apply for a PlaceHolderVar */ + if (rcon->target_rte->lateral && + !bms_is_subset(((PlaceHolderVar *) newnode)->phrels, + rcon->relids)) + wrap = true; + else + wrap = false; } else {
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
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
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