SQL standard question about Common Table Expressions

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: SQL standard question about Common Table Expressions
Date: 2008-09-08 07:25:19
Message-ID: 1220858719.12678.117.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I am looking into the SQL standard to try to determine precisely how the
CTE feature should behave.

Taking a simple case like:

with recursive
foo(i) as
(values(1)
union all
select i+1 from foo where i < 5)
select * from foo;

And looking at the SQL standard 200n 7.13: General Rules: 2.c, it
provides an algorithm for evaluating the recursive query.

In this algorithm, AQEk is a <query expression>. Syntactically, I only
see two <query expression>s, and one is the entire query. The other is:
"(values(1) union all select i+1 from foo where i < 5)", so I'll assume
that AQEk must be equal to that*.

The confusing thing to me is step 2.c.ix.3.B. If the query expression
AQEk is equal to the WQEk, step 2.c.ix.3.B will always set the working
table WTk to some kind of non-empty value, because the "values(1) union
all..." will always return at least one row. This will then cause it to
loop forever.

Where am I going wrong?

Also, 2.c.ii says "If AQEk is immediately contained in some WQEi...". In
the 200n standard, it appears that it's impossible for a <query
expression> to immediately contain another <query expression>. In the
2003 standard it can, but they added another level of indirection in the
200n standard by using an intervening <table subquery>. I'm not an
authority, but I believe this is a mistake.

Regards,
Jeff Davis

* Having AQEk = WQEi disturbs me, too, because in the "Framework" part
of the standard, section 6.3.3.1, the definition of contains does not
seem to allow for them to be equal.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message M2Y 2008-09-08 07:34:56 Re: Some newbie questions
Previous Message Heikki Linnakangas 2008-09-08 06:45:18 Re: [HACKERS] TODO item: Implement Boyer-Moore searching (First time hacker)