Re: Common Table Expressions (WITH RECURSIVE) patch - Mailing list pgsql-hackers

>>>>> "Jeff" == Jeff Davis <jdavis@truviso.com> writes:
Jeff> Aggregates should be blocked according to the standard.Jeff> Also, causes an infinite loop. This should be fixed
for8.4.
 
>> Does the standard require anywhere that non-conforming statements>> must be diagnosed? (seems impractical, since it
wouldforbid>> extensions)
 
Jeff> 2.g.iii.4.B explicitly says aggregates should be rejected,Jeff> unless I have misinterpreted.

Yes, you've misinterpreted. When the spec says that a query "shall
not" do such-and-such, it means that a conforming client isn't allowed
to do that, it does NOT mean that the server is required to produce an
error when it sees it.

Chapter and verse on this is given in the Framework doc, at 6.3.3.2:
 In the Syntax Rules, the term "shall" defines conditions that are required to be true of syntactically conforming SQL
language.When such conditions depend on the contents of one or more schemas, they are required to be true just before
theactions specified by the General Rules are performed. The treatment of language that does not conform to the SQL
Formatsand Syntax Rules is implementation-dependent.  If any condition required by Syntax Rules is not satisfied when
theevaluation of Access or General Rules is attempted and the implementation is neither processing non-conforming SQL
languagenor processing conforming SQL language in a non-conforming manner, then an exception condition is raised:
"syntaxerror or access rule violation".
 

Including an aggregate violates a "shall" in a syntax rule, therefore the
query is non-conforming, therefore the server can either process it in an
implementation-dependent manner or reject it with an exception.
>> Yeah, though the standard's use of DISTINCT in this way is something>> of a violation of the POLA.
Jeff> I agree that's kind of a funny requirement. But that's prettyJeff> typical of the SQL standard. If DB2 or SQL
Serverfollow theJeff> standard here, we should, too. If not, it's open for discussion.
 

MSSQL does not:

"The following items are not allowed in the CTE_query_definition of arecursive member:
   * SELECT DISTINCT   * GROUP BY   * HAVING   * Scalar aggregation   * TOP   * LEFT, RIGHT, OUTER JOIN (INNER JOIN is
allowed)  * Subqueries   * A hint applied to a recursive reference to a CTE inside a     CTE_query_definition.
 
"

For DB2 the docs do not appear to specify either way; they don't seem to
forbid the use of SELECT DISTINCT inside a recursive CTE, but neither do
they seem to mention any unusual effect of including it.
Jeff> * ORDER BY, LIMIT, and OFFSET are rejected for recursiveJeff> queries. The standard does not seem to say that
theseshould beJeff> rejected.>> Note that supporting those in subqueries (including CTEs) is a>> separate optional
featureof the standard.
 
Jeff> I don't feel strongly about this either way, but I prefer that weJeff> are consistent when possible. We do
supportthese things in aJeff> subquery, so shouldn't we support them in all subqueries?
 

Ideally we should. DB2 appears to (other than OFFSET which it doesn't
seem to have at all). But it's not at all clear that it would be either
useful or easy to do so.

-- 
Andrew.