Mathieu Fenniak <math...@fenniak.net> writes:
> After running the attached setup.sql.gz SQL script on a PostgreSQL
> 8.4.0 database, the following two queries which should be logically
> identical return different results. As far as I can tell from the
> query analysis, the LEFT JOIN on query A is happening after
> "ee.projectid = pc.projectid" is filtered; therefore the rows where
> projectid is NULL are not visible in query A.
This should fix it. Thanks for the report!
regards, tom lane
Index: src/backend/optimizer/README
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/README,v
retrieving revision 1.49
diff -c -r1.49 README
*** src/backend/optimizer/README 27 Feb 2009 22:41:37 -0000 1.49
--- src/backend/optimizer/README 21 Jul 2009 01:53:14 -0000
***************
*** 214,223 ****
!= (A leftjoin B on (Pab)) join C on (Pbc)
SEMI joins work a little bit differently. A semijoin can be reassociated
! into or out of the lefthand side of another semijoin, but not into or out
! of the righthand side. Likewise, an inner join, left join, or antijoin
! can be reassociated into or out of the lefthand side of a semijoin, but
! not into or out of the righthand side.
ANTI joins work approximately like LEFT joins, except that identity 3
fails if the join to C is an antijoin (even if Pbc is strict, and in
--- 214,223 ----
!= (A leftjoin B on (Pab)) join C on (Pbc)
SEMI joins work a little bit differently. A semijoin can be reassociated
! into or out of the lefthand side of another semijoin, left join, or
! antijoin, but not into or out of the righthand side. Likewise, an inner
! join, left join, or antijoin can be reassociated into or out of the
! lefthand side of a semijoin, but not into or out of the righthand side.
ANTI joins work approximately like LEFT joins, except that identity 3
fails if the join to C is an antijoin (even if Pbc is strict, and in
Index: src/backend/optimizer/plan/initsplan.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/initsplan.c,v
retrieving revision 1.154
diff -c -r1.154 initsplan.c
*** src/backend/optimizer/plan/initsplan.c 11 Jun 2009 14:48:59 -0000
1.154
--- src/backend/optimizer/plan/initsplan.c 21 Jul 2009 01:53:14 -0000
***************
*** 630,637 ****
* min_lefthand + min_righthand. This is because there might
be other
* OJs below this one that this one can commute with, but we
cannot
* commute with them if we don't with this one.) Also, if the
current
! * join is an antijoin, we must preserve ordering regardless of
! * strictness.
*
* Note: I believe we have to insist on being strict for at
least one
* rel in the lower OJ's min_righthand, not its whole
syn_righthand.
--- 630,637 ----
* min_lefthand + min_righthand. This is because there might
be other
* OJs below this one that this one can commute with, but we
cannot
* commute with them if we don't with this one.) Also, if the
current
! * join is a semijoin or antijoin, we must preserve ordering
! * regardless of strictness.
*
* Note: I believe we have to insist on being strict for at
least one
* rel in the lower OJ's min_righthand, not its whole
syn_righthand.
***************
*** 639,645 ****
if (bms_overlap(left_rels, otherinfo->syn_righthand))
{
if (bms_overlap(clause_relids,
otherinfo->syn_righthand) &&
! (jointype == JOIN_ANTI ||
!bms_overlap(strict_relids,
otherinfo->min_righthand)))
{
min_lefthand = bms_add_members(min_lefthand,
--- 639,645 ----
if (bms_overlap(left_rels, otherinfo->syn_righthand))
{
if (bms_overlap(clause_relids,
otherinfo->syn_righthand) &&
! (jointype == JOIN_SEMI || jointype == JOIN_ANTI
||
!bms_overlap(strict_relids,
otherinfo->min_righthand)))
{
min_lefthand = bms_add_members(min_lefthand,
***************
*** 655,661 ****
* can interchange the ordering of the two OJs; otherwise we
must add
* lower OJ's full syntactic relset to min_righthand. Here, we
must
* preserve ordering anyway if either the current join is a
semijoin,
! * or the lower OJ is an antijoin.
*
* Here, we have to consider that "our join condition" includes
any
* clauses that syntactically appeared above the lower OJ and
below
--- 655,661 ----
* can interchange the ordering of the two OJs; otherwise we
must add
* lower OJ's full syntactic relset to min_righthand. Here, we
must
* preserve ordering anyway if either the current join is a
semijoin,
! * or the lower OJ is either a semijoin or an antijoin.
*
* Here, we have to consider that "our join condition" includes
any
* clauses that syntactically appeared above the lower OJ and
below
***************
*** 672,677 ****
--- 672,678 ----
{
if (bms_overlap(clause_relids,
otherinfo->syn_righthand) ||
jointype == JOIN_SEMI ||
+ otherinfo->jointype == JOIN_SEMI ||
otherinfo->jointype == JOIN_ANTI ||
!otherinfo->lhs_strict ||
otherinfo->delay_upper_joins)
{
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
