Hi all,
Using postgres 8.4.13 (latest that redhat provides in rhel6) the query
below returns an extra row compared to running the same thing in later
versions (tried 9.0, 9.1, 9.2 - they don't return the extra row).
Just wondering if anyone had thoughts on why, and/or how to remove the
duplicate row. It gets worse the more rows in the initial 'data' section.
WITH RECURSIVE data AS
(
SELECT CAST('/a/' AS TEXT) AS path, CAST(1 AS INTEGER) AS depth
UNION ALL
SELECT '/a/a/', 2
),
numbers AS
(
SELECT path, depth AS iteration, depth AS depth, 'A'
FROM data
WHERE depth =
(
SELECT MIN(depth)
FROM data
)
UNION ALL
(
WITH sub_sumbers AS
(
SELECT path, (iteration + 1) AS iteration, depth
FROM numbers
WHERE iteration <
(
SELECT MAX(depth)
FROM data
)
)
SELECT path, iteration, depth, 'b'
FROM sub_sumbers
UNION ALL
SELECT path, depth, depth, 'c'
FROM data
WHERE depth =
(
SELECT MAX(iteration)
FROM sub_sumbers
)
)
)
SELECT *
FROM numbers
ORDER BY iteration, depth;
path | iteration | depth | ?column?
-------+-----------+-------+----------
/a/ | 1 | 1 | A
/a/ | 2 | 1 | b
/a/ | 2 | 1 | b
/a/a/ | 2 | 2 | c
(4 rows)
The 'b' row is duplicated (but not in later versions of postgres).
Thanks for any suggestions/advice.
--
Postgresql & php tutorials
http://www.designmagick.com/
