pg ANY/SOME ambiguity wrt sql standard?

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;

Is parsed as an array-operator and there is a semantical error because no
array is provided. Now ANY could be an aggregate function call, and it
should be fine.

Well, it is not that fine, because allowing ANY and SOME as function name
generates a lot of conflicts, obviously.

The reverse (let us recognize an array expression in an function call
wouldn't work either, as the parser need the special handling of ANY/SOME
in order to look for subselects.

Thus I'm afraid that I'll have to rewrite the A_Expr structure into a
FuncCall to 'any' or 'some' somewhere.
Comments? Any better idea?

So my question is "Any idea?" instead of "Any better idea?" :-(

--
Fabien Coelho - coelho@cri.ensmp.fr

Fabien COELHO <coelho@cri.ensmp.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

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;

[...]

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?

It does not seemed to be fixed in the copy I found, but it may not be the
last version.

As a "temporary" fix, what about "_ANY" and "_SOME" as aggregate names?

--
Fabien Coelho - coelho@cri.ensmp.fr

Fabien COELHO <coelho@cri.ensmp.fr> writes:

As a "temporary" fix, what about "_ANY" and "_SOME" as aggregate names?

Ick :-(. The use of leading underscores is an ugly C-ism that we should
not propagate into SQL names.

How about bool_or() and bool_and()? Or at least something based on OR
and AND? I don't find ANY/ALL to be particularly mnemonic for this
usage anyway.

regards, tom lane

Tom Lane wrote:

Fabien COELHO <coelho@cri.ensmp.fr> writes:

As a "temporary" fix, what about "_ANY" and "_SOME" as aggregate names?

Ick :-(. The use of leading underscores is an ugly C-ism that we should
not propagate into SQL names.

I second this... the whole __ is hard to type and remember.

Sincerely,

Joshua D. Drake

How about bool_or() and bool_and()? Or at least something based on OR
and AND? I don't find ANY/ALL to be particularly mnemonic for this
usage anyway.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

As a "temporary" fix, what about "_ANY" and "_SOME" as aggregate names?

Ick :-(. The use of leading underscores is an ugly C-ism that we should
not propagate into SQL names.

Ok.

How about bool_or() and bool_and()? Or at least something based on OR
and AND? I don't find ANY/ALL to be particularly mnemonic for this
usage anyway.

Yep.

The standard "EVERY" is fine for postgres, the issue is only with
ANY/SOME.

Do you think that bool_and should be proposed anyway for homogeneity
with bool_or?

--
Fabien Coelho - coelho@cri.ensmp.fr

Fabien COELHO <coelho@cri.ensmp.fr> writes:

The standard "EVERY" is fine for postgres, the issue is only with
ANY/SOME.
Do you think that bool_and should be proposed anyway for homogeneity
with bool_or?

I think EVERY is actively misleading, because it does *not* imply that
every input is TRUE. The spec says these aggregates should ignore
nulls, and so a true result only implies that there were no FALSE
inputs.

OTOH one could argue that the ignore-nulls behavior makes this not a
true analog of AND, either ...

regards, tom lane

Dear Tom,

The standard "EVERY" is fine for postgres, the issue is only with
ANY/SOME. Do you think that bool_and should be proposed anyway for
homogeneity with bool_or?

I think EVERY is actively misleading, because it does *not* imply that
every input is TRUE. The spec says these aggregates should ignore
nulls, and so a true result only implies that there were no FALSE
inputs.

OTOH one could argue that the ignore-nulls behavior makes this not a
true analog of AND, either ...

Argh, how stupid I am, I missread the specification! Then the patch I sent
yesterday is wrong if NULL values are encountered:-( I should learn how to
read sometimes...

However, I did not name the boolean and aggregate EVERY, it is BOOL_AND
(in the patch), because I tend to prefer homogeneity.

I'll resubmit a patch later.

--
Fabien Coelho - coelho@cri.ensmp.fr