The following bug has been logged online:

Bug reference: 1916
Logged by: Kevin Grittner
Email address: kevin.grittner (AT) wicourts (DOT) 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)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Kevin Grittner" <kevin.grittner (AT) wicourts (DOT) gov> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Wed, 28 Sep 2005 00:34:37 -0400, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
fails on 7.4.7 (rh9) and 8.0.0 (win) for me.

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg (AT) kgb (DOT) 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 : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Kevin Grittner" <Kevin.Grittner (AT) wicourts (DOT) gov> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Kevin Grittner" <kevin.grittner (AT) wicourts (DOT) gov> writes:
I've committed a fix for this. Many thanks for the test case.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match