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

From: luisa(dot)j(dot)francisco(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7790: null filters in CTEs don't work
Date: 2013-01-06 04:58:07
Message-ID: E1TriJD-0005aH-KR@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 7790
Logged by: Luisa Francisco
Email address: luisa(dot)j(dot)francisco(at)gmail(dot)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
------------

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message zardozwildman 2013-01-06 06:14:04 BUG #7791: create database with owner, owner does not get usage on schema
Previous Message Craig Ringer 2013-01-06 04:10:06 Re: BUG #7771: packaging error causes build failure