Re: Common Table Expressions (WITH RECURSIVE) patch

On Mon, 2008-09-08 at 22:53 +0100, Andrew Gierth wrote:
> 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.
>

Interesting. Thanks for the clear reference.

So, we either need to reject it or define some implementation-dependent
behavior, right?

The patch currently rejects HAVING, which means that it's a little
difficult to use an aggregate effectively. I lean toward rejecting
aggregates if we reject HAVING, for consistency. If we allow it, we
should allow HAVING as well.

> 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:

Thanks again for a reference.

If MSSQL rejects SELECT DISTINCT for a recursive <query expression>,
then I think we should reject it. Right now the patch allows it but
provides a result that is inconsistent with the standard.

If we reject SELECT DISTINCT for recursive queries now, we can always
meet the standard later, or decide that the standard behavior is too
likely to cause confusion and just continue to block it.

> 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.

Ok. If it's easy to support it should probably be done.

As an aside, you seem to be an expert with the standard, have you had a
chance to look at the question I asked earlier?:

http://archives.postgresql.org/pgsql-hackers/2008-09/msg00487.php

Regards,
Jeff Davis