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