From: | Kirill Simonov <xi(at)resolvent(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | LEFT OUTER JOIN sub-SELECT produces a column != NULL when all NULLs are expected |
Date: | 2010-09-25 18:33:16 |
Message-ID: | 4C9E406C.6060406@resolvent.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
I found a bug where a column from a LEFT OUTER JOIN sub-SELECT is not
equal to NULL when the whole row must be NULL because the join condition
is not satisfied.
Here is the test case:
------------------------------
DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS a;
CREATE TABLE a (
code CHAR NOT NULL,
CONSTRAINT a_pk PRIMARY KEY (code)
);
CREATE TABLE b (
a CHAR NOT NULL,
num INTEGER NOT NULL,
CONSTRAINT b_pk PRIMARY KEY (a, num),
CONSTRAINT b_a_fk FOREIGN KEY (a) REFERENCES a (code)
);
CREATE TABLE c (
name CHAR NOT NULL,
a CHAR,
CONSTRAINT c_pk PRIMARY KEY (name),
CONSTRAINT c_a_fk FOREIGN KEY (a) REFERENCES a (code)
);
INSERT INTO a (code) VALUES ('p');
INSERT INTO a (code) VALUES ('q');
INSERT INTO b (a, num) VALUES ('p', 1);
INSERT INTO b (a, num) VALUES ('p', 2);
INSERT INTO c (name, a) VALUES ('X', 'p');
INSERT INTO c (name, a) VALUES ('Y', 'q');
INSERT INTO c (name, a) VALUES ('Z', NULL);
SELECT c.name,
a.code,
a.b_cnt,
a.const
FROM c
LEFT OUTER JOIN (SELECT a.code,
COALESCE(b_grp.cnt, 0) AS b_cnt,
-1 AS const
FROM a
LEFT OUTER JOIN
(SELECT COUNT(1) AS cnt, b.a FROM b GROUP BY
b.a) AS b_grp
ON (a.code = b_grp.a)
) AS a ON (c.a = a.code)
ORDER BY c.name ASC;
------------------------------
This produces:
name | code | b_cnt | const
------+------+-------+-------
X | p | 2 | -1
Y | q | 0 | -1
Z | | 0 | -1
(3 rows)
The expected result is:
name | code | b_cnt | const
------+------+-------+-------
X | p | 2 | -1
Y | q | 0 | -1
Z | | |
(3 rows)
Tested under 8.4.4 and 9.0 (installed from Ubuntu packages).
Other database servers (SQLite, MySQL, Oracle, MS SQL Server) produce
the expected output.
Thanks,
Kirill
From | Date | Subject | |
---|---|---|---|
Next Message | stagirus | 2010-09-25 19:31:55 | Re: Mapping Hibernate boolean to smallint(Postgresql) |
Previous Message | Devrim GÜNDÜZ | 2010-09-25 16:18:01 | Re: BUG #5678: libpq.so.4 is needed for pgadmin3 |