Hi:
I think I might have found a bug in the 9.2beta3 version. I'm kind of
new to SQL, so forgive me if I'm just misinterpreting correct
behavior. Given the query below, execute it. You should get a
seven-row result.
Next, uncomment the final UNION four lines from the end. When I do
that I then get a two row result. I'm not an expert on recursive
CTEs, but I don't believe a UNION should decrease the number of rows
returned.
Next, change the condition in the final WHERE clause (seven lines from
the end) from "e.row_type='false'" to just "false". Again, I'm not an
expert but my understanding is that any boolean expression returning
false should be equivalent in a given WHERE clause, and you can see
there's no row_type column with value 'false'.
If this is not a bug and I'm just confused, then I apologize and would
greatly appreciate any suggestions as to what I could read that would
unconfuse me. Otherwise, let me know if you need any other details
about my environment. Thanks very much. -Adam Mackler
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
WITH RECURSIVE
tab(id_key,link) AS ( VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17) ),
iter (id_key, row_type, link) AS (
SELECT 0, 'base', 17
UNION(
WITH remaining(id_key, row_type, link, min) AS (
SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
FROM tab INNER JOIN iter USING (link)
WHERE tab.id_key > iter.id_key
),
first_remaining AS (
SELECT id_key, row_type, link
FROM remaining
WHERE id_key=min
),
effect AS (
SELECT tab.id_key, 'new'::text, tab.link
FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
/* Try changing this WHERE clause to other false expressions */
WHERE e.row_type='false'
)
SELECT * FROM first_remaining
/* Try uncommenting the next line */
--UNION SELECT * FROM effect
)
)
SELECT DISTINCT * FROM iter
