luisa.j.francisco@gmail.com writes:
> Expected output should have no nulls in it, but it does:
It's not apparent to me why you think the first query shouldn't produce
any rows with null parent_id? AFAICS, the recursive query will "crawl
up the tree" producing a row for every parent level above the given
base-case rows. Eventually you'll get up to a match to the row
('body', null), and there's nothing to stop that from being displayed.
It's a bit easier to see what's happening if you leave off the "ORDER
BY" so that the rows are printed in generation order:
regression=# 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);
id | parent_id
--------+-----------
tooth | mouth
tongue | mouth
sclera | eye
cornea | eye
(4 rows)
regression=# 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;
id | parent_id
--------+-----------
tooth | mouth
tongue | mouth
sclera | eye
cornea | eye
tooth | head
tongue | head
sclera | head
cornea | head
tooth | body
tongue | body
sclera | body
cornea | body
tooth |
tongue |
sclera |
cornea |
(16 rows)
regards, tom lane