recursive query returning extra rows in 8.4

[Chris <dmagick@xxxxxxxxx>]

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/



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general