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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general