[BUGS] 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

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