[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    [BUGS] BUG #5255: COUNT(*)  returns wrong result with LEFT JOIN
From:       "Matteo Beccati" <php () beccati ! com>
Date:       2009-12-25 10:43:40
Message-ID: 200912251043.nBPAheeY048752 () wwwmaster ! postgresql ! org
[Download RAW message or body]


The following bug has been logged online:

Bug reference:      5255
Logged by:          Matteo Beccati
Email address:      php@beccati.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)

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[prev in list] [next in list] [prev in thread] [next in thread]