Обсуждение: recursive query returning extra rows in 8.4

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/

chris smith-9 wrote
> 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).
>
>
> SELECT *
> FROM numbers
> ORDER BY iteration, depth;

Likely this is a bug that was fixed in one of the five newer 8.4 point
releases.  You can use "SELECT DISTINCT *" to get rid of the extra data if
you choose not to upgrade.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/recursive-query-returning-extra-rows-in-8-4-tp5774573p5774607.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
> chris smith-9 wrote
>> 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).

> Likely this is a bug that was fixed in one of the five newer 8.4 point
> releases.  You can use "SELECT DISTINCT *" to get rid of the extra data if
> you choose not to upgrade.

In particular I think this matches the first item in the 8.4.14 release
notes:

  * Fix planner's assignment of executor parameters, and fix executor's
    rescan logic for CTE plan nodes (Tom Lane)

    These errors could result in wrong answers from queries that scan the
    same WITH subquery multiple times.

When I worked for Red Hat, it was hard to get permission to push PG update
releases unless a nontrivial security fix was involved.  That's probably
why they're still on 8.4.13.  You could file a bug asking for an update
because of this issue, but I can't say how quickly anything would happen.

            regards, tom lane

On 16/10/13 01:56, Tom Lane wrote:
> David Johnston <polobo@yahoo.com> writes:
>> chris smith-9 wrote
>>> 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).
>
>> Likely this is a bug that was fixed in one of the five newer 8.4 point
>> releases.  You can use "SELECT DISTINCT *" to get rid of the extra data if
>> you choose not to upgrade.
>
> In particular I think this matches the first item in the 8.4.14 release
> notes:
>
>    * Fix planner's assignment of executor parameters, and fix executor's
>      rescan logic for CTE plan nodes (Tom Lane)
>
>      These errors could result in wrong answers from queries that scan the
>      same WITH subquery multiple times.

I did read the release notes looking for something but obviously not
very well.

Thanks for the info.

--
Postgresql & php tutorials
http://www.designmagick.com/