BUG #1916: selection criteria from one outer join on clause applied to other joins - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1916
Logged by:          Kevin Grittner
Email address:      kevin.grittner@wicourts.gov
PostgreSQL version: 8.1beta2
Operating system:   Linux and Windows
Description:        selection criteria from one outer join on clause applied
to other joins
Details:

Below is a much simplified test case.  In the real application, it actually
makes sense for the framework code to combine selection criteria from
multiple sources to limit the outer join and let the database perform the
set logic.

Obviously, adding an outer join to a query which is already returning rows
should never reduce the number of rows returned.

dtr=> create table t1 (f1 smallint not null, f2 smallint not null, primary
key (f1, f2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
CREATE TABLE
dtr=> create table t2 (f1 smallint not null, f2 smallint not null, f3
smallint not null, f4 varchar(10), primary key (f1, f2, f3));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for
table "t2"
CREATE TABLE
dtr=> insert into t1 values (1, 10);
INSERT 0 1
dtr=> insert into t1 values (1, 20);
INSERT 0 1
dtr=> insert into t1 values (2, 10);
INSERT 0 1
dtr=> insert into t1 values (2, 20);
INSERT 0 1
dtr=> insert into t2 values (1, 20, 100, 'xxx');
INSERT 0 1
dtr=> select t1.* from t1
dtr-> left join t2 a on (a.f1 = 1 and a.f1 = t1.f1 and a.f2 = t1.f2)
dtr-> where t1.f1 = 1 and a.f4 = 'xxx';
 f1 | f2
----+----
  1 | 20
(1 row)

dtr=> select t1.* from t1
dtr-> left join t2 a on (a.f1 = 1 and a.f1 = t1.f1 and a.f2 = t1.f2)
dtr-> left join t2 b on (b.f1 = 1 and b.f1 = t1.f1 and b.f2 = t1.f2 and b.f1
= 2)
dtr-> where t1.f1 = 1 and a.f4 = 'xxx';
 f1 | f2
----+----
(0 rows)

dtr=> explain analyze
dtr-> select t1.* from t1
dtr-> left join t2 a on (a.f1 = 1 and a.f1 = t1.f1 and a.f2 = t1.f2)
dtr-> left join t2 b on (b.f1 = 1 and b.f1 = t1.f1 and b.f2 = t1.f2 and b.f1
= 2)
dtr-> where t1.f1 = 1 and a.f4 = 'xxx';
                                                        QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------
 Nested Loop Left Join  (cost=0.00..11.79 rows=1 width=4) (actual
time=0.015..0.015 rows=0 loops=1)
   Join Filter: (("inner".f2 = "outer".f2) AND ("inner".f1 = "outer".f1))
   ->  Nested Loop  (cost=0.00..7.86 rows=1 width=4) (actual
time=0.014..0.014 rows=0 loops=1)
         Join Filter: ("outer".f2 = "inner".f2)
         ->  Index Scan using t2_pkey on t2 a  (cost=0.00..3.92 rows=1
width=4) (actual time=0.013..0.013 rows=0 loops=1)
               Index Cond: ((f1 = 1) AND (2 = f1))
               Filter: ((f4)::text = 'xxx'::text)
         ->  Index Scan using t1_pkey on t1  (cost=0.00..3.92 rows=1
width=4) (never executed)
               Index Cond: ((f1 = 1) AND (2 = f1))
   ->  Index Scan using t2_pkey on t2 b  (cost=0.00..3.92 rows=1 width=4)
(never executed)
         Index Cond: ((f1 = 1) AND (f1 = 2))
 Total runtime: 0.099 ms
(12 rows)