Lists: | pgsql-bugs |
---|
From: | "Matteo Beccati" <php(at)beccati(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5255: COUNT(*) returns wrong result with LEFT JOIN |
Date: | 2009-12-25 10:43:40 |
Message-ID: | 200912251043.nBPAheeY048752@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5255
Logged by: Matteo Beccati
Email address: php(at)beccati(dot)com
PostgreSQL version: 8.5alpha3
Operating system: NetBSD 5.0.1
Description: COUNT(*) returns wrong result with LEFT JOIN
Details:
Discovered this while fixing the php test suite to deal with 8.5 changes.
With the following data set a SELECT * query returns 1 rows, while SELECT
COUNT(*) returns 2.
CREATE TABLE a (id int PRIMARY KEY);
CREATE TABLE b (id int PRIMARY KEY, a_id int);
INSERT INTO a VALUES (0), (1);
INSERT INTO b VALUES (0, 0), (1, NULL);
test=# SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR
a.id > 0);
id | a_id | id
----+------+----
1 | |
(1 row)
test=# SELECT COUNT(*) FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS
NULL OR a.id > 0);
count
-------
2
(1 row)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Matteo Beccati" <php(at)beccati(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5255: COUNT(*) returns wrong result with LEFT JOIN |
Date: | 2009-12-25 16:38:53 |
Message-ID: | 5034.1261759133@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Matteo Beccati" <php(at)beccati(dot)com> writes:
> With the following data set a SELECT * query returns 1 rows, while SELECT
> COUNT(*) returns 2.
Hm, looks like the join-elimination patch is firing mistakenly. It's not
so much the count(*) that does it as the lack of any select-list
references to a:
regression=# explain SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0);
QUERY PLAN
-----------------------------------------------------------------
Hash Left Join (cost=64.00..132.85 rows=720 width=12)
Hash Cond: (b.a_id = a.id)
Filter: ((a.id IS NULL) OR (a.id > 0))
-> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
-> Hash (cost=34.00..34.00 rows=2400 width=4)
-> Seq Scan on a (cost=0.00..34.00 rows=2400 width=4)
(6 rows)
regression=# explain SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0);
QUERY PLAN
-----------------------------------------------------
Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(1 row)
I guess we missed something about when it's safe to do this optimization.
regards, tom lane
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Matteo Beccati" <php(at)beccati(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5255: COUNT(*) returns wrong result with LEFT JOIN |
Date: | 2009-12-25 17:13:00 |
Message-ID: | 6159.1261761180@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
I wrote:
> I guess we missed something about when it's safe to do this optimization.
I've applied the attached patch to fix this.
regards, tom lane
Attachment | Content-Type | Size |
---|---|---|
join-removal-fix.patch | text/x-patch | 2.0 KB |
From: | Matteo Beccati <php(at)beccati(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5255: COUNT(*) returns wrong result with LEFT JOIN |
Date: | 2009-12-25 17:21:51 |
Message-ID: | 4B34F4AF.9090003@beccati.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Il 25/12/2009 18:13, Tom Lane ha scritto:
> I wrote:
>> I guess we missed something about when it's safe to do this optimization.
>
> I've applied the attached patch to fix this.
Thanks. Everything's working fine now!
Merry Xmas
--
Matteo Beccati
Development & Consulting - http://www.beccati.com/