The following bug has been logged on the website: Bug reference: 7790 Logged by: Luisa Francisco Email address: luisa.j.franci...@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 = 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 = t.parent_id ) SELECT * FROM t ORDER BY id; ) t1 WHERE parent_id IS NOT NULL ------------ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs