Thread: Possible Bug in 9.2beta3
Hi:
I think I might have found a bug in the 9.2beta3 version. I'm kind of
new to SQL, so forgive me if I'm just misinterpreting correct
behavior. Given the query below, execute it. You should get a
seven-row result.
Next, uncomment the final UNION four lines from the end. When I do
that I then get a two row result. I'm not an expert on recursive
CTEs, but I don't believe a UNION should decrease the number of rows
returned.
Next, change the condition in the final WHERE clause (seven lines from
the end) from "e.row_type='false'" to just "false". Again, I'm not an
expert but my understanding is that any boolean expression returning
false should be equivalent in a given WHERE clause, and you can see
there's no row_type column with value 'false'.
If this is not a bug and I'm just confused, then I apologize and would
greatly appreciate any suggestions as to what I could read that would
unconfuse me. Otherwise, let me know if you need any other details
about my environment. Thanks very much. -Adam Mackler
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
WITH RECURSIVE
tab(id_key,link) AS ( VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17) ),
iter (id_key, row_type, link) AS (
SELECT 0, 'base', 17
UNION(
WITH remaining(id_key, row_type, link, min) AS (
SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
FROM tab INNER JOIN iter USING (link)
WHERE tab.id_key > iter.id_key
),
first_remaining AS (
SELECT id_key, row_type, link
FROM remaining
WHERE id_key=min
),
effect AS (
SELECT tab.id_key, 'new'::text, tab.link
FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
/* Try changing this WHERE clause to other false expressions */
WHERE e.row_type='false'
)
SELECT * FROM first_remaining
/* Try uncommenting the next line */
--UNION SELECT * FROM effect
)
)
SELECT DISTINCT * FROM iter
On Mon, Aug 13, 2012 at 7:17 PM, Adam Mackler <AdamMackler@gmail.com> wrote: > > Next, uncomment the final UNION four lines from the end. When I do > that I then get a two row result. I'm not an expert on recursive > CTEs, but I don't believe a UNION should decrease the number of rows > returned. I haven't dug through all of it yet but it's definitely the case that UNION can reduce the number of rows returned. If there are any duplicates in one side of the union they'll be eliminated by the UNION. That said your input set doesn't have any duplicates so that shouldn't be relevant. For some reason I'm getting a syntax error trying to reproduce your problem but I have an old build of Postgres lying around so I'm going to update and try again. -- greg
Adam Mackler <AdamMackler@gmail.com> writes:
> WITH RECURSIVE
> tab(id_key,link) AS ( VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17) ),
> iter (id_key, row_type, link) AS (
> SELECT 0, 'base', 17
> UNION(
> WITH remaining(id_key, row_type, link, min) AS (
> SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
> FROM tab INNER JOIN iter USING (link)
> WHERE tab.id_key > iter.id_key
> ),
> first_remaining AS (
> SELECT id_key, row_type, link
> FROM remaining
> WHERE id_key=min
> ),
> effect AS (
> SELECT tab.id_key, 'new'::text, tab.link
> FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
> /* Try changing this WHERE clause to other false expressions */
> WHERE e.row_type='false'
> )
> SELECT * FROM first_remaining
> /* Try uncommenting the next line */
> --UNION SELECT * FROM effect
> )
> )
> SELECT DISTINCT * FROM iter
Right offhand I'm inclined to think that the reference to "iter"
inside the first sub-WITH ought to be disallowed. I don't recall
the exact rules about where a recursive reference can appear, but
it sure doesn't seem like that ought to be OK, does it?
regards, tom lane
Are you asking me or the other experts? I had not even heard of a common table expression a few weeks ago, so I doubt I'm qualified to opine what ought to be possible; I just know what I'm trying to do. Basically I've got a recursive CTE with rows, some of which have information that I want to be split between two rows in a result table. I couldn't see a way to do that except to "copy" the working table of the outer recursive CTE to an intermediary inner CTE, so that then I could refer to that intermediary CTE twice, once for each row that I want to result from one row in the outer recursive CTE. If that makes sense. It wouldn't surprise me at all to learn of a better way to do what I want, but to answer your question: on the one hand yes, I was obviously trying to "get around" a limitation that was expressed to me in a specific error message about only referring to a recursive CTE working-table variable in one location. On the other hand, I don't see why I shouldn't be able to look at that working table more than once on each iteration. I imagine the "optimal" answer requires more knowledge of what's going on under the covers than I have, as well as familiarity with the SQL standard and more experience & expertise than I have in how to solve problems using SQL. As far as facility with SQL, I'm still pretty much stumbling in the dark and learning by trial-and-error and reading whatever I can find that seems on point, so, again, my current idea of what ought to be possible is based on a rather uninformed foundation. Adam Mackler On Wed, Aug 15, 2012 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Adam Mackler <AdamMackler@gmail.com> writes: >> WITH RECURSIVE >> tab(id_key,link) AS ( VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17) ), >> iter (id_key, row_type, link) AS ( >> SELECT 0, 'base', 17 >> UNION( >> WITH remaining(id_key, row_type, link, min) AS ( >> SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER () >> FROM tab INNER JOIN iter USING (link) >> WHERE tab.id_key > iter.id_key >> ), >> first_remaining AS ( >> SELECT id_key, row_type, link >> FROM remaining >> WHERE id_key=min >> ), >> effect AS ( >> SELECT tab.id_key, 'new'::text, tab.link >> FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key >> /* Try changing this WHERE clause to other false expressions */ >> WHERE e.row_type='false' >> ) >> SELECT * FROM first_remaining >> /* Try uncommenting the next line */ >> --UNION SELECT * FROM effect >> ) >> ) >> SELECT DISTINCT * FROM iter > > Right offhand I'm inclined to think that the reference to "iter" > inside the first sub-WITH ought to be disallowed. I don't recall > the exact rules about where a recursive reference can appear, but > it sure doesn't seem like that ought to be OK, does it? > > regards, tom lane -- Adam Mackler
Adam Mackler <adammackler@gmail.com> writes:
> On Wed, Aug 15, 2012 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Right offhand I'm inclined to think that the reference to "iter"
>> inside the first sub-WITH ought to be disallowed. I don't recall
>> the exact rules about where a recursive reference can appear, but
>> it sure doesn't seem like that ought to be OK, does it?
> Are you asking me or the other experts?
More the latter. The verbiage in SQL:2008 7.13 <query expression>
syntax rule 2) g) is sufficiently dense that it's not easy to tell
what they're forbidding, and as usual with the SQL committee, no word
of rationale shall escape their lips. So I have no idea whether these
restrictions are really logically necessary to ensure a predictable
result, or they just represent the lowest common denominator of the
implementations written by the companies with seats on the committee.
But it sort of looks like subrule iii) 3) restricts the recursive
reference to appear in the main body of the subquery, not in a WITH
attached to it.
Be that as it may, our implementation seems to be able to cope with it;
the issue that we had here was pretty easily resolvable once I'd traced
through the execution. Patch is committed, though too late for this
week's releases.
regards, tom lane