Thread: BUG #1916: selection criteria from one outer join on clause applied to other joins

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)

"Kevin Grittner" <kevin.grittner@wicourts.gov> writes:
> Obviously, adding an outer join to a query which is already returning rows
> should never reduce the number of rows returned.

I think this is a case of overenthusiastic propagation of implied
equalities.  Do you know offhand if it fails in 8.0.* or earlier
branches?

            regards, tom lane

On Wed, 28 Sep 2005 00:34:37 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <kevin.grittner@wicourts.gov> writes:
> > Obviously, adding an outer join to a query which is already returning rows
> > should never reduce the number of rows returned.
>
> I think this is a case of overenthusiastic propagation of implied
> equalities.  Do you know offhand if it fails in 8.0.* or earlier
> branches?

fails on 7.4.7 (rh9) and 8.0.0 (win) for me.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Any idea on when a fix might be available, or what
> conditions must exist in a query for this failure to occur?

It would be fairly easy to fix just by disabling all consideration
of deducing anything from outer-join conditions.  I want to think
a little more about whether we can be less drastic, but that might
be what ends up going into the back branches.  In any case you can
expect to see fixes in 8.0.4 et al.

            regards, tom lane

"Kevin Grittner" <kevin.grittner@wicourts.gov> writes:
> Description:        selection criteria from one outer join on clause applied
> to other joins

I've committed a fix for this.  Many thanks for the test case.

            regards, tom lane