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