Re: BUG #17320: A SEGV in optimizer

[ this'd been on my to-do list for a long time, I finally got to it ]

Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> writes:
> If I tried to execute the following query, I got the follwoing
> error. This looks like rooted from the same mistake.

> WITH RECURSIVE x ( x ) AS
> (SELECT 1
> UNION
> (WITH x AS (SELECT * FROM x)
> SELECT * FROM x)
> ) SEARCH BREADTH FIRST BY x SET NCHAR
> SELECT * FROM x;

> ERROR: could not find attribute 2 in subquery targetlist

> By the way, if I executed the following query, I get the following
> another assertion failure.

> WITH RECURSIVE x ( x ) AS
> (SELECT 1
> UNION
> (WITH y AS (SELECT * FROM x)
> SELECT * FROM y)
> ) SEARCH BREADTH FIRST BY x SET NCHAR
> SELECT * FROM x;

> TRAP: FailedAssertion("cte_rtindex > 0", File: "rewriteSearchCycle.c", Line: 398, PID: 31288)

This is pretty remarkable, because those two queries *should* be
semantically identical. I guessed that something is getting confused
by the duplicated CTE name, and it didn't take long to see what:
rewriteSearchAndCycle() is failing to check ctelevelsup while looking
for a match to the recursive CTE's name. Thus, it thinks that the
reference to the lower WITH item (either "x" or "y" in these two
examples) is the recursive reference it seeks, which it ain't.

Eventually perhaps we should support this, since a recursive query
of this form is allowed in other contexts. But I'm content to
throw error for now, as per the attached patch. This catches all
the cases shown in this thread, as well as the seemingly unrelated
error shown in bug #17318.

(I looked for other places that might be carelessly matching on
ctename without checking levelsup, and found none.)

regards, tom lane