The IN and ANY constructs works with different kinds of collections to the 
right. This is the current situation:


      SUB QUERY      VALUE LIST       ARRAY

IN       X               X

ANY      X                              X


The SQL standard specifies that = ANY should be equivalent to IN 
(including the value list case) but it is not.

Take for example this:

select 'a' = ANY ('{a,b}');

which according to the SQL spec should evaluate to False but in pg
evaluates to True. Look for at this run in pg:

dennis=# select 'a' IN ('{a,b}');
 ?column?
----------
 f
(1 rad)
 
dennis=# select 'a' = ANY ('{a,b}');
 ?column?
----------
 t


I only have the SQL200x draft to check. And in section 8.4 it's
clearly stated that "rvc IN ipv" is equivalent to "rvc = ANY ipv" and an
ipv is defined to be either a subquery or a value list.

*** some time later ***

Hmm, the draft seems to be broken since I can only find ANY defined for
subqueries in other sections, and not for value lists. Strange but not 
uncommon. Now I don't know what the standard says about this. Maybe 
someone with the sql99 spec wants to check.

The question is also what makes sense. I'm not too fond of the above 
situation, but I'm not sure if anything can be done now.

-- 
/Dennis Bjï¿½rklund


Tue, 2 Mar 2004 06:57:01 +0100 (CET)
Dennis Bjorklund <db@zigo.dhs.org>
IN and ANY
Dennis Bjorklund <db@zigo.dhs.org> writes:
> Hmm, the draft seems to be broken since I can only find ANY defined for
> subqueries in other sections, and not for value lists. Strange but not 
> uncommon. Now I don't know what the standard says about this. Maybe 
> someone with the sql99 spec wants to check.

I think you are reading the term "equivalent" as meaning an equivalence
in both directions.  It looks to me that the spec's definition of
<in predicate> is (mis)using the term to mean "is defined as".
In SQL92 I see

         1) Let IVL be an <in value list>.
              ( IVL )
            is equivalent to the <table value constructor>:
              ( VALUES IVL )

         ...

         4) The expression
              RVC IN IPV
            is equivalent to
              RVC = ANY IPV

These two rules together define both forms of IN in terms of the
"= ANY (subquery)" construct.  But surely the first rule is not
meant to say that VALUES is a noise word.  So this has to be a
one-way implication.

Accordingly I think you are in error to suggest that "= ANY (valuelist)"
is supposed to work.  I think ANY is only supposed to have a table
subquery to the right.

I don't have a strong opinion about "IN array", but am worried that
allowing it would create ambiguity about which interpretation is meant.
Is the left-hand side supposed to be compared against the whole array or
each array member?

			regards, tom lane

Tue, 02 Mar 2004 01:45:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: IN and ANY 
Tom Lane wrote:
> I don't have a strong opinion about "IN array", but am worried that
> allowing it would create ambiguity about which interpretation is meant.
> Is the left-hand side supposed to be compared against the whole array or
> each array member?

That was exactly the problem. IN (array) does work if the left-hand side 
is also an array, and the right-hand side is taken as a value list:

regression=# select 1 where array[1] in (array[1]);
  ?column?
----------
         1
(1 row)

regression=# select 1 where array[1] in (array[1,2]);
  ?column?
----------
(0 rows)

There was discussion on this back in the May-June 2003 timeframe -- 
please check the archives.

Joe

Tue, 02 Mar 2004 08:01:23 -0800
Joe Conway <mail@joeconway.com>
Re: IN and ANY
On Tue, 2 Mar 2004, Joe Conway wrote:

> regression=# select 1 where array[1] in (array[1]);
>   ?column?
> ----------
>          1
> (1 row)
> 
> regression=# select 1 where array[1] in (array[1,2]);
>   ?column?
> ----------
> (0 rows)

The only reason I brought it up was that to me ANY and IN are pretty much
the same kind of operator. Up until now I thought that IN really was the
same as =ANY, but it isn't. I don't like that but of course I have to
accept it. I would rather have had some elem function/operator for arrays
like:

  1 ELEM IN array[1,2]

or

  1 = ANY ELEM arra[1,2]

But as usual I'm 6 months late with my objections :-)

-- 
/Dennis Bjï¿½rklund


Tue, 2 Mar 2004 21:44:38 +0100 (CET)
Dennis Bjorklund <db@zigo.dhs.org>
Re: IN and ANY
Dennis Bjorklund wrote:
> The only reason I brought it up was that to me ANY and IN are pretty much
> the same kind of operator. Up until now I thought that IN really was the
> same as =ANY, but it isn't. I don't like that but of course I have to
> accept it. I would rather have had some elem function/operator for arrays
> like:

I wrestled with the same issue last year.

Offhand I don't know how hard it would be to do, but I wonder if when we 
see:

   argL IN (argR)

we might be able to determine if argL and argR are of the same type or 
if argR is an array of argL, and act accordingly.

But then we need to decide, what happens when we see:
   argL IN (argR1, argR2, ...)
and argRn data type is an array of argL data type? Do we check all the 
argRn elements individually and return true if any of them equal argL? 
I'd guess so.

> But as usual I'm 6 months late with my objections :-)

An uncanny ability ;-)

Joe

Tue, 02 Mar 2004 13:42:03 -0800
Joe Conway <mail@joeconway.com>
Re: IN and ANY
Joe Conway <mail@joeconway.com> writes:
> But then we need to decide, what happens when we see:
>    argL IN (argR1, argR2, ...)
> and argRn data type is an array of argL data type? Do we check all the 
> argRn elements individually and return true if any of them equal argL? 
> I'd guess so.

This seems like an awfully mistake-prone "feature", even if it's
theoretically not ambiguous.  I think we should leave things as they
are.  We'd not be adding any actual functionality, only some notational
consistency, and that doesn't seem worth the risk of confusion as to
exactly what IN will do.

[ wanders away trying to remember that quote about "foolish consistency" ]

			regards, tom lane

Tue, 02 Mar 2004 16:54:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: IN and ANY 
Tom Lane wrote:
> [ wanders away trying to remember that quote about "foolish consistency" ]
> 

http://www.bartleby.com/59/3/foolishconsi.html

"A foolish consistency is the hobgoblin of little minds"

A great person does not have to think consistently from one day to the 
next. This remark comes from the essay ï¿½Self-Relianceï¿½ by Ralph Waldo 
Emerson. Emerson does not explain the difference between foolish and 
wise consistency.

Joe

Tue, 02 Mar 2004 13:56:16 -0800
Joe Conway <mail@joeconway.com>
Re: IN and ANY

Joe Conway <mail@joeconway.com> writes:

> I wrestled with the same issue last year.
> 
> Offhand I don't know how hard it would be to do, but I wonder if when we see:
> 
>    argL IN (argR)
> 
> we might be able to determine if argL and argR are of the same type or if argR
> is an array of argL, and act accordingly.

That would be nice and simple and clean, except that postgres's type system
doesn't work that way. It works backwards from every other language. Instead
of every variable and function return value having a rigid type and forcing
the interpretation of the surrounding expression, postgres seems to work top
down. 

So in this case if argL or argR are functions or other expressions with
unknown types it tries to figure out how to interpret them to produce the type
it's looking for. In other words, what type those expressions are depends on
what the expression expects. What would you do with "foo() IN (array[1,2])" if
there are two functions called foo, one which returns an integer and one which
returns an integer[] ?

One of these days I'm going to suggest doing away with the whole "unknown"
concept and make function return types not part of the polymorphic signature.
That would make a lot of the type system weirdness go away. But it would mean
breaking with a lot of tradition. And of course it would be completely and
utterly not backwards compatible and I'll probably be laughed at.

-- 
greg


02 Mar 2004 22:19:25 -0500
Greg Stark <gsstark@mit.edu>
Re: IN and ANY
Greg Stark <gsstark@mit.edu> writes:
> That would be nice and simple and clean, except that postgres's type system
> doesn't work that way. It works backwards from every other language. Instead
> of every variable and function return value having a rigid type and forcing
> the interpretation of the surrounding expression, postgres seems to work top
> down. 

[blinks...]  I don't think so.

			regards, tom lane

Tue, 02 Mar 2004 22:40:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: IN and ANY 
Greg Stark wrote:
> So in this case if argL or argR are functions or other expressions with
> unknown types it tries to figure out how to interpret them to produce the type
> it's looking for. In other words, what type those expressions are depends on
> what the expression expects. What would you do with "foo() IN (array[1,2])" if
> there are two functions called foo, one which returns an integer and one which
> returns an integer[] ?

Well, first off, it isn't now, nor has it ever been (to my knowledge), 
possible to overload a function with more than one return type. It also 
isn't possible to declare a polymorphic return type unless at least one 
argument is polymorphic (meaning there must be at least one argument), 
in which case the return type is absolutely deterministic at time of 
execution.

I'm not sure it's worth discussing this too much further, due to Tom's 
objection to the concept in general, but anyway...you should use the 
source. Starting with gram.y, we have this:

r_expr:  row IN_P select_with_parens
   {
     SubLink *n = makeNode(SubLink);
     n->subLinkType = ANY_SUBLINK;
     n->lefthand = $1;
     n->operName = makeList1(makeString("="));
     n->subselect = $3;
     $$ = (Node *)n;
   }

Here you can see that "IN" is actually transformed internally
to an "= ANY" SubLink node. In parse_expr.c you'll see (around line 518 
in current sources) where the ANY SubLink is processed. Note that 
exprType() is used on the expressions in order to determine 
(recursively) the argument data types, so that the appropriate operator 
can be picked. Hence, if we *wanted* to know if exprType(right_expr) was 
an array of exprType(left_expr), we could.

> One of these days I'm going to suggest doing away with the whole "unknown"
> concept and make function return types not part of the polymorphic signature.
> That would make a lot of the type system weirdness go away. But it would mean
> breaking with a lot of tradition.

That doesn't make too much sense either. Polymorphic return types were 
not possible prior to 7.4.

Joe


Tue, 02 Mar 2004 19:53:40 -0800
Joe Conway <mail@joeconway.com>
Re: IN and ANY

Uhm. oops. I'll just crawl back under this rock for a bit longer before I
speak on this topic again. sorry.

-- 
greg


02 Mar 2004 23:23:34 -0500
Greg Stark <gsstark@mit.edu>
Re: IN and ANY