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