Обсуждение: Bug 4906 -- Left join of subselect incorrect

Hi all,

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.  The issue does not
occur in PostgreSQL 8.3.6.

My apologies for the large test setup; I attempted build up the same
test case, but was unable to reproduce the issue.  I had to tear down
my database as much as I could while maintaining the issue.

Query A:
select *
   FROM expense ex
   JOIN expenseentry ee ON ex.id = ee.expenseid
   LEFT JOIN (
     SELECT projectclient.projectid, projectclient.clientid,
projectclient.billingpercentage
     FROM projectclient
     WHERE projectclient.projectid IN (
       SELECT project.id
       FROM project
       WHERE project.clientbillingallocationmethod <> 2)
     ) pc ON ee.projectid = pc.projectid

Query B:
select *
   FROM expense ex
   JOIN expenseentry ee ON ex.id = ee.expenseid
   LEFT JOIN (
     SELECT projectclient.projectid, projectclient.clientid,
projectclient.billingpercentage
     FROM projectclient
     INNER JOIN project ON (projectclient.projectid = project.id)
     WHERE project.clientbillingallocationmethod <> 2
   ) pc ON ee.projectid = pc.projectid

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.

Yeah, it seems to be confused about whether it can interchange the
order of the semijoin and left join.  I'll take a look.

            regards, tom lane

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)
              {

On 20-Jul-09, at 8:05 PM, Tom Lane wrote:
>
> This should fix it.  Thanks for the report!
>
>             regards, tom lane

Just tested this patch, and it works perfectly.  Thank-you.

Mathieu