Lists: | pgsql-bugs |
---|
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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 | Content-Type | Size |
---|---|---|
setup.sql.gz | application/x-gzip | 4.2 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mathieu Fenniak <mathieu(at)fenniak(dot)net> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug 4906 -- Left join of subselect incorrect |
Date: | 2009-07-20 23:28:56 |
Message-ID: | 29374.1248132536@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Mathieu Fenniak <mathieu(at)fenniak(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mathieu Fenniak <mathieu(at)fenniak(dot)net> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug 4906 -- Left join of subselect incorrect |
Date: | 2009-07-21 02:05:25 |
Message-ID: | 3146.1248141925@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Mathieu Fenniak <mathieu(at)fenniak(dot)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
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/plain | 4.6 KB |
From: | Mathieu Fenniak <mathieu(at)fenniak(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug 4906 -- Left join of subselect incorrect |
Date: | 2009-07-21 13:16:12 |
Message-ID: | 64F33E00-E55A-449D-9425-C37963D05BD1@fenniak.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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