Re: pg ANY/SOME ambiguity wrt sql standard?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Cc: PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg ANY/SOME ambiguity wrt sql standard?
Date: 2004-04-28 14:49:57
Message-ID: 29221.1083163797@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> writes:
> I'm looking into adding sql standard aggregates EVERY/ANY/SOME.
> It seems to me that there is a syntax ambiguity with ANY and SOME:

> CREATE TABLE bla(b BOOL);
> SELECT TRUE = ANY(b) FROM bla;

AFAICS this ambiguity is built into the SQL standard, and in fact it's
possible to generate cases that are legally parseable either way:

SELECT foo.x = ANY((SELECT bar.y FROM bar)) FROM foo;

The parenthesized sub-select could be a plain <value expression>,
in which case ANY must be an aggregate function call, or we could
regard it as a <table subquery>, in which case we've got a <quantified
comparison predicate>. These interpretations could both work, if the
sub-select yields only one row, but they won't necessarily give the same
answer.

So I think that the SQL committee shot themselves in the foot when they
decided it was a good idea to call the boolean-OR aggregate "ANY", and
our addition of an array option isn't the fundamental problem.

Anyone know if SQL2003 fixed this silliness?

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2004-04-28 15:03:46 Re: PITR Phase 1 - Code Overview (1)
Previous Message Thomas Hallgren 2004-04-28 14:38:40 Re: Advice regarding configuration parameters