Thread: 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/