Re: IN and ANY

Dennis Bjorklund  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
 is (mis)using the term to mean "is defined as".
In SQL92 I see
        1) Let IVL be an .             ( IVL )           is equivalent to the :             ( 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