Re: BUG #7790: null filters in CTEs don't work

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