
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

Wed, 28 Apr 2004 15:21:00 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
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

Wed, 28 Apr 2004 15:40:06 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: pg ANY/SOME ambiguity wrt sql standard?
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

Wed, 28 Apr 2004 10:49:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg ANY/SOME ambiguity wrt sql standard? 

> 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

Fri, 30 Apr 2004 18:10:22 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: pg ANY/SOME ambiguity wrt sql standard? 
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

Fri, 30 Apr 2004 20:56:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg ANY/SOME ambiguity wrt sql standard? 
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








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
Fri, 30 Apr 2004 18:17:16 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: pg ANY/SOME ambiguity wrt sql standard?

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

Sat, 1 May 2004 11:29:57 +0200 (MEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: pg ANY/SOME ambiguity wrt sql standard? 
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

Sat, 01 May 2004 11:45:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg ANY/SOME ambiguity wrt sql standard? 

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

Sun, 2 May 2004 11:12:44 +0200 (MEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: pg ANY/SOME ambiguity wrt sql standard? 