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
