Re: IN and ANY

Dennis Bjorklund <db(at)zigo(dot)dhs(dot)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