While merging commits from c9f7f926484d69e2806e35343af7e472fadfede7
through 3db72ebcbe20debc6552500ee9ccb4b2007f12f8 into a fork, my
colleague Rushabh Lathia and I noticed that the merge caused certain
queries to return wrong answers. Rushabh then discovered that this
also happens in unmodified PostgreSQL. I believe the problem is most
likely with one of these three commits:
3bef56e116 Invent "join domains" to replace the below_outer_join hack.
b448f1c8d8 Do assorted mop-up in the planner.
2489d76c49 Make Vars be outer-join-aware.
The following test case, mostly due to Rushabh, demonstrates the
problem on current master:
rhaas=# create table one_row as values (1);
SELECT 1
rhaas=# create table three_oids as select * from (values (10::oid),
(20::oid), (30::oid)) v(oid);
SELECT 3
rhaas=# create table just_one_oid as select * from (values (10::oid)) v(oid);
SELECT 1
rhaas=# SELECT r.oid, s.oid FROM just_one_oid s LEFT JOIN (select
three_oids.oid from one_row, three_oids LEFT JOIN pg_db_role_setting s
ON three_oids.oid = s.setrole AND s.setdatabase = 0::oid) r ON r.oid =
s.oid;
oid | oid
-----+-----
10 | 10
20 | 10
30 | 10
(3 rows)
The answer is clearly wrong, because the join clause requires r.oid
and s.oid to be equal, but in the displayed output, they are not. I'm
not quite sure what's happening here. The join to pg_db_role_setting
gets removed, which seems correct, because it has a primary key index
on (setrole, setdatabase), and the join clause requires both of those
columns to be equal to some specific value. The output of the whole
subselect is correct. But the join between the subselect and
just_one_oid somehow goes wrong: the join filter that ought to be
there is missing.
Nested Loop Left Join (cost=0.00..272137387.88 rows=16581375000 width=8)
-> Seq Scan on just_one_oid s (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..139272.12 rows=6502500 width=4)
-> Nested Loop (cost=0.00..81358.62 rows=6502500 width=4)
-> Seq Scan on one_row (cost=0.00..35.50 rows=2550 width=0)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on three_oids (cost=0.00..35.50
rows=2550 width=4)
--
Robert Haas
EDB: http://www.enterprisedb.com
