pgsql: Consider a clause to be outerjoin_delayed if it references the

From: tgl(at)postgresql(dot)org (Tom Lane)
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Consider a clause to be outerjoin_delayed if it references the
Date: 2008-06-27 20:54:45
Message-ID: 20080627205445.7BD63754857@cvs.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Log Message:
-----------
Consider a clause to be outerjoin_delayed if it references the nullable side
of any lower outer join, even if it also references the non-nullable side and
so could not get pushed below the outer join anyway. We need this in case
the clause is an OR clause: if it doesn't get marked outerjoin_delayed,
create_or_index_quals() could pull an indexable restriction for the nullable
side out of it, leading to wrong results as demonstrated by today's bug
report from toruvinn. (See added regression test case for an example.)

In principle this has been wrong for quite a while. In practice I don't
think any branch before 8.3 can really show the failure, because
create_or_index_quals() will only pull out indexable conditions, and before
8.3 those were always strict. So though we might have improperly generated
null-extended rows in the outer join, they'd get discarded from the result
anyway. The gating factor that makes the failure visible is that 8.3
considers "col IS NULL" to be indexable. Hence I'm not going to risk
back-patching further than 8.3.

Tags:
----
REL8_3_STABLE

Modified Files:
--------------
pgsql/src/backend/optimizer/plan:
initsplan.c (r1.138.2.1 -> r1.138.2.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/initsplan.c?r1=1.138.2.1&r2=1.138.2.2)
pgsql/src/test/regress/expected:
join.out (r1.33 -> r1.33.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/join.out?r1=1.33&r2=1.33.2.1)
join_1.out (r1.15 -> r1.15.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/join_1.out?r1=1.15&r2=1.15.2.1)
pgsql/src/test/regress/sql:
join.sql (r1.24 -> r1.24.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/join.sql?r1=1.24&r2=1.24.2.1)

Browse pgsql-committers by date

  From Date Subject
Next Message Magnus Hagander 2008-06-28 13:34:32 Re: pgsql: Make the pg_stat_activity view call a SRF
Previous Message Tom Lane 2008-06-27 20:54:37 pgsql: Consider a clause to be outerjoin_delayed if it references the