Re: Common Table Expressions (WITH RECURSIVE) patch

>>>>> "Jeff" == Jeff Davis <jdavis(at)truviso(dot)com> writes:

Jeff> Aggregates should be blocked according to the standard.
Jeff> Also, causes an infinite loop. This should be fixed for 8.4.

>> Does the standard require anywhere that non-conforming statements
>> must be diagnosed? (seems impractical, since it would forbid
>> 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 the actions specified by the General
Rules are performed. The treatment of language that does not conform to
the SQL Formats and Syntax Rules is implementation-dependent. If any
condition required by Syntax Rules is not satisfied when the evaluation
of Access or General Rules is attempted and the implementation is
neither processing non-conforming SQL language nor processing
conforming SQL language in a non-conforming manner, then an exception
condition is raised: "syntax error 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 pretty
Jeff> typical of the SQL standard. If DB2 or SQL Server follow the
Jeff> 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 a
recursive 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 recursive
Jeff> queries. The standard does not seem to say that these should be
Jeff> rejected.

>> Note that supporting those in subqueries (including CTEs) is a
>> separate optional feature of the standard.

Jeff> I don't feel strongly about this either way, but I prefer that we
Jeff> are consistent when possible. We do support these things in a
Jeff> 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.