Re: Bug 4906 -- Left join of subselect incorrect - Mailing list pgsql-bugs

Mathieu Fenniak <mathieu@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)
              {