The following bug has been logged on the website:
Bug reference: 7790
Logged by: Luisa Francisco
Email address: luisa.j.francisco@gmail.com
PostgreSQL version: 9.2.2
Operating system: 32-bit Windows 7 SP1
Description: =
Expected output should have no nulls in it, but it does:
--------
CREATE TABLE item_tree(
id text PRIMARY KEY, =
parent_id text
);
INSERT INTO item_tree (id, parent_id) VALUES
('body', null), =
('head', 'body'), =
('mouth', 'head'), =
('eye', 'head'), =
('tooth', 'mouth'), =
('tongue', 'mouth'), =
('sclera', 'eye'), =
('cornea', 'eye')
;
WITH RECURSIVE t(id, parent_id) AS ( =
SELECT id, parent_id =
FROM item_tree i
WHERE parent_id IS NOT NULL
AND id NOT IN (
SELECT parent_id =
FROM item_tree =
WHERE parent_id IS NOT NULL)
UNION ALL
SELECT t.id, i.parent_id =
FROM item_tree i =
JOIN t =
ON i.id =3D t.parent_id =
)
SELECT * FROM t ORDER BY id;
-----------
Output is as follows:
id parent_id
------ ---------
cornea eye
cornea NULL
cornea head
cornea body
sclera eye
sclera head
sclera NULL
sclera body
tongue body
tongue head
tongue NULL
tongue mouth
tooth body
tooth head
tooth mouth
tooth NULL
However, enclosing the query with a outer select-null-filter works even if
all the inner filters were deleted as below:
---------
SELECT * FROM (
WITH RECURSIVE t(id, parent_id) AS ( =
SELECT id, parent_id =
FROM item_tree i
UNION ALL
SELECT t.id, i.parent_id =
FROM item_tree i =
JOIN t =
ON i.id =3D t.parent_id =
)
SELECT * FROM t ORDER BY id;
) t1 WHERE parent_id IS NOT NULL
------------