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

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
------------

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