Обсуждение: pg ANY/SOME ambiguity wrt sql standard?

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;

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.

However I really cannot see (my usual lack of imagination) how to handle
this from the parser.

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?

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

> 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:<br /><blockquote cite="mid8707.1083373014@sss.pgh.pa.us" type="cite"><pre wrap="">Fabien COELHO <a
class="moz-txt-link-rfc2396E"href="mailto:coelho@cri.ensmp.fr"><coelho@cri.ensmp.fr></a> writes:
</pre><blockquotetype="cite"><pre wrap="">As a "temporary" fix, what about "_ANY" and "_SOME" as aggregate names?
</pre></blockquote><prewrap="">
 
Ick :-(.  The use of leading underscores is an ugly C-ism that we should
not propagate into SQL names.
 </pre></blockquote> I second this... the whole __ is hard to type and remember.<br /><br /> Sincerely,<br /><br />
JoshuaD. Drake<br /><br /><br /><br /><blockquote cite="mid8707.1083373014@sss.pgh.pa.us" type="cite"><pre wrap="">How
aboutbool_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 <a class="moz-txt-link-abbreviated"
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a></pre></blockquote><br /><br /><pre
class="moz-signature"cols="72">-- 
 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - <a class="moz-txt-link-abbreviated" href="mailto:jd@commandprompt.com">jd@commandprompt.com</a> - <a
class="moz-txt-link-freetext"href="http://www.commandprompt.com">http://www.commandprompt.com</a>
 
PostgreSQL Replicator -- production quality replication for PostgreSQL</pre>

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