Re: [BUG] Planner bug causes incorrect query results in PG14 and PG15

Hi hackers:
This bug affects only versions below pg 16. Ver 16 has a fundamental infrasturctural update, eliminating the root cause of the bug. But for versions below 16, they lack the mechanism intrduced in ver 16, so we have to take another approach to handle the bug.
The problem of join_clause_is_movable_into() is the 4th check in this function, i.e.,'if (bms_overlap(currentrelids, rinfo->nullable_relids))' is so radical that it may prohibit some clause that can be moved to a parameterized path. For example:
select * from C ,D left join lateral(select c4*2,* from E)SQ on d1=e1 where c2=coalesce(d2,e2,0);
Because the subquery laterally depends on C, at the top level query, the path of SQ is parameterized by C. The paths for joinrel (D left join SQ) are also parameterized by C. The clause 'c2=coalesce(d2,e2,0)' will be judged not movable to the paths for joinrel (D left join SQ) because SQ is on the nullable side of left join, unable to pass the 4th test of join_clause_is_movable_into(). However, there is actually no problem to move this clause to the paths for joinrel (D left join SQ), because this clause, when used as a restriction for joinrel (D left join SQ), won't cause premature filtering of relation SQ. The clause only cause problem when moved to the parameterized path of SQ before the left join that can nullify SQ is performed.
In view of the excessive restrictiveness of join_clause_is_movable_into(), in pg 14/15 code, the movability check is commented out in get_joinrel_parampathinfo() as follows:

/*
* In principle, join_clause_is_movable_into() should accept anything
* returned by generate_join_implied_equalities(); but because its
* analysis is only approximate, sometimes it doesn't. So we
* currently cannot use this Assert; instead just assume it's okay to
* apply the joinclause at this level.
*/
#ifdef NOT_USED
Assert(join_clause_is_movable_into(rinfo,
joinrel->relids,
join_and_req));
#endif

The lack of examination for rinfo's movability to joinrel's path directly results in the incorrect query result mentioned in the previous mail.
To fix this issue, I introduce another function, join_clause_is_movable_into(), which make more accurate assessment of the movability of a restriction. In this new function, if currentrelids span both
sides of a outer join, the outer join can't invalidate the movability of the the restriction. We tested the patch with the failed query in the previous mail, this time the result is correct. We also test with some other queries, for example,
explain select * from c ,d left join lateral(select c4*2,* from e)i on d1=e1 where c2=coalesce(d2,e2,0);
The current implementation of pg14/15 generates this plan:

Nested Loop (cost=280.91..1092816.52 rows=158291 width=52)
Join Filter: (COALESCE(d.d2, e.e2, 0) = c.c2)
-> Seq Scan on c (cost=0.00..28.50 rows=1850 width=16)
-> Merge Left Join (cost=280.91..589.62 rows=86 width=36)
Merge Cond: (d.d1 = e.e1)
Filter: (c.c2 = COALESCE(d.d2, e.e2, 0))
-> Sort (cost=128.89..133.52 rows=1850 width=16)
Sort Key: d.d1
-> Seq Scan on d (cost=0.00..28.50 rows=1850 width=16)
-> Sort (cost=152.02..156.64 rows=1850 width=20)
Sort Key: e.e1
-> Seq Scan on e (cost=0.00..33.12 rows=1850 width=20)

The clause 'COALESCE(d.d2, e.e2, 0) = c.c2' is checked twice in the plan, tough the redundant check won't result in wrong query result, it add unnecessary the cost to the plan.

After patching, pg15 produces a plan identical to that of ver 16 and above versions:
Nested Loop (cost=262.41..1058193.77 rows=158291 width=52)
-> Seq Scan on c (cost=0.00..28.50 rows=1850 width=16)
-> Merge Left Join (cost=262.41..571.12 rows=86 width=36)
Merge Cond: (d.d1 = e.e1)
Filter: (c.c2 = COALESCE(d.d2, e.e2, 0))
-> Sort (cost=128.89..133.52 rows=1850 width=16)
Sort Key: d.d1
-> Seq Scan on d (cost=0.00..28.50 rows=1850 width=16)
-> Sort (cost=133.52..138.14 rows=1850 width=20)
Sort Key: e.e1
-> Seq Scan on e (cost=0.00..33.12 rows=1850 width=20)
The repeated check is gotten rid of.

I also ran make check-world with the patch applied, all tests that were executed passed.
I would especially appreciate feedback on the correctness of this fix.

Thanks, Geng Lei
________________________________
发件人: g l
发送时间: 2026年11月1日 19:41
收件人: pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)org>
主题: [BUG] Planner bug causes incorrect query results in PG14 and PG15

Hi hackers:
I encounter a bug that result in wrong query result. This bug is present throughout pg14 and pg15, even in 15.19, the latest minor version for pg 15, this bug remains. The query below can reproduce it:

opt=# select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 15.19 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit
(1 row)

we create 3 tables and populate 1 row to each table:
opt=# create table c (c1 int, c2 int, c3 int, c4 int);
CREATE TABLE
opt=# create table d (d1 int, d2 int, d3 int, d4 int);
CREATE TABLE
opt=# create table e (e1 int, e2 int, e3 int, e4 int);
CREATE TABLE
opt=# insert into c values (40,100,88,88);
INSERT 0 1
opt=# insert into d values (70,1,88,88);
INSERT 0 1
opt=# insert into e values (3,5,0,0);
INSERT 0 1

we then create 2 nonstrict function:
opt=# create function lax1 (a integer,b integer,c integer) returns integer
as $$
select case
when a+b+c is null then 1000
else 2
end
$$
LANGUAGE SQL;
CREATE FUNCTION
opt=# create function lax2 (a integer,b integer,c integer) returns integer
as $$
select case
when a+b+c is null then 1000
else 3
end
$$
LANGUAGE SQL;
CREATE FUNCTION

Now execute the query:
opt=# explain select * from c left join (d join lateral(select sum(e2) x,e3,e4 from e where e1<>c1 group by e3,e4)i on d1>x) on c2>d2+coalesce(e3)
where lax1(c4,d4,e4)=lax2(c3,d3,e3);
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
Nested Loop Left Join (cost=46.93..22402728.38 rows=380277 width=48)
Filter: (CASE WHEN (((c.c4 + d.d4) + e.e4) IS NULL) THEN 1000 ELSE 2 END = CASE WHEN (((c.c3 + d.d3) + e.e3) IS NULL) THEN 1000 ELSE 3 END)
-> Seq Scan on c (cost=0.00..28.50 rows=1850 width=16)
-> Nested Loop (cost=46.93..12104.93 rows=206 width=32)
Join Filter: ((d.d1 > (sum(e.e2))) AND (c.c2 > (d.d2 + COALESCE(e.e3))) AND (CASE WHEN (((c.c4 + d.d4) + e.e4) IS NULL) THEN 1000 ELSE 2 END = CASE WHEN (((c.
c3 + d.d3) + e.e3) IS NULL) THEN 1000 ELSE 3 END))
-> Seq Scan on d (cost=0.00..28.50 rows=1850 width=16)
-> Materialize (cost=46.93..51.93 rows=200 width=16)
-> HashAggregate (cost=46.93..48.93 rows=200 width=16)
Group Key: e.e3, e.e4
-> Seq Scan on e (cost=0.00..33.12 rows=1841 width=12)
Filter: (e1 <> c.c1)
(11 rows)

opt=# select * from c left join (d join lateral(select sum(e2) x,e3,e4 from e where e1<>c1 group by e3,e4)i on d1>x) on c2>d2+coalesce(e3)
where lax1(c4,d4,e4)=lax2(c3,d3,e3);
c1 | c2 | c3 | c4 | d1 | d2 | d3 | d4 | x | e3 | e4
----+-----+----+----+----+----+----+----+---+----+----
40 | 100 | 88 | 88 | | | | | | |
(1 row)

switching to version 20devel, creating the same dataset and functions, then perform the same query, I get a different result:
opt=# select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit
(1 row)

opt=# explain select * from c left join (d join lateral(select sum(e2) x,e3,e4 from e where e1<>c1 group by e3,e4)i on d1>x) on c2>d2+coalesce(e3)
where lax1(c4,d4,e4)=lax2(c3,d3,e3);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=46.93..15545449.00 rows=380277 width=48)
Filter: (CASE WHEN (((c.c4 + d.d4) + e.e4) IS NULL) THEN 1000 ELSE 2 END = CASE WHEN (((c.c3 + d.d3) + e.e3) IS NULL) THEN 1000 ELSE 3 END)
-> Seq Scan on c (cost=0.00..28.50 rows=1850 width=16)
-> Nested Loop (cost=46.93..7477.93 rows=41111 width=32)
Join Filter: ((d.d1 > (sum(e.e2))) AND (c.c2 > (d.d2 + e.e3)))
-> Seq Scan on d (cost=0.00..28.50 rows=1850 width=16)
-> Materialize (cost=46.93..49.93 rows=200 width=16)
-> HashAggregate (cost=46.93..48.93 rows=200 width=16)
Group Key: e.e3, e.e4
-> Seq Scan on e (cost=0.00..33.12 rows=1841 width=12)
Filter: (e1 <> c.c1)
(11 rows)

opt=# select * from c left join (d join lateral(select sum(e2) x,e3,e4 from e where e1<>c1 group by e3,e4)i on d1>x) on c2>d2+coalesce(e3)
where lax1(c4,d4,e4)=lax2(c3,d3,e3);
c1 | c2 | c3 | c4 | d1 | d2 | d3 | d4 | x | e3 | e4
----+----+----+----+----+----+----+----+---+----+----
(0 rows)

The cause of error in pg14/15 is that the path for i is parameterized by table c. the path for joinrel (DI) is still parameterized by table c. When creating the nestloop join path for joinrel (DI), the clause
lax1(c4,d4,e4)=lax2(c3,d3,e3) is deduced from equivalence class in line 1463 of function get_joinrel_parampathinfo (in terms of pg15.19 source code). although this clause can't pass the join_clause_is_movable_into(rinfo, joinrel->relids, join_and_req) test, it is 'pushed' to the parameterized path of joinrel (DI) anyway, prematurely filtering out the row of (DI). But simply discard EC deduced clauses that can't pass the join_clause_is_movable_into test doesn't seem a good idea, because such clauses don't necessarily cause problem. for example, consider this query:
select * from c ,d left join lateral(select c4*2,* from e)i on d1=e1 where c2=coalesce(d2,e2,0);
this query push down EC deduced clause "c2=coalesce(d2,e2,0)" that can't pass the join_clause_is_movable_into test to the parameterized path of joinrel (DI) too, but there's no problem, becaused it doesn't give rise to premature filtering out of the nullable side of a outer join.
I would especially appreciate advice on how to solve this issue in pg14/15.

Thanks, Geng Lei