Skip site navigation (1) Skip section navigation (2)

Bug 4906 -- Left join of subselect incorrect

From: Mathieu Fenniak <mathieu(at)fenniak(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug 4906 -- Left join of subselect incorrect
Date: 2009-07-20 19:47:03
Message-ID: 530A4611-C6B9-4E1D-9C72-7B86E8E6F362@fenniak.net (view raw or flat)
Thread:
Lists: pgsql-bugs
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



Attachment: setup.sql.gz
Description: application/x-gzip (4.2 KB)

Responses

pgsql-bugs by date

Next:From: Jaime CasanovaDate: 2009-07-20 22:15:12
Subject: Re: fix: plpgsql: return query and dropped columns problem
Previous:From: Jaime CasanovaDate: 2009-07-20 16:34:27
Subject: Re: fix: plpgsql: return query and dropped columns problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group