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
setup.sql.gz
Description: GNU Zip compressed data
-- Sent via pgsql-bugs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
