Обсуждение: LEFT OUTER JOIN sub-SELECT produces a column != NULL when all NULLs are expected
LEFT OUTER JOIN sub-SELECT produces a column != NULL when all NULLs are expected
От
Kirill Simonov
Дата:
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
Kirill Simonov <xi@resolvent.net> writes: > 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. I've applied a patch for this; it'll be in next week's update releases. Thanks for the report! regards, tom lane