Skip site navigation (1) Skip section navigation (2)

LEFT OUTER JOIN sub-SELECT produces a column != NULL when all NULLs are expected

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 (view raw or flat)
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

Responses

pgsql-bugs by date

Next:From: stagirusDate: 2010-09-25 19:31:55
Subject: Re: Mapping Hibernate boolean to smallint(Postgresql)
Previous:From: Devrim GÜNDÜZDate: 2010-09-25 16:18:01
Subject: Re: BUG #5678: libpq.so.4 is needed for pgadmin3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group