The IN, SUBSET and INTERSECT operators, being
set-like, share certain behaviors in common in Texis version 7 (or
compatibilityversion 7) and later:
varchar value on either side of these operators is treated
as a single-item
strlst set - regardless of the current
varchartostrlstsep setting. This aids usage of IN/
SUBSET/INTERSECT in Vortex when
arrayconvert is active
for parameters: it provides consistent results whether the Vortex
variable is single- or multi-value. A single
will not be unexpectedly (and incorrectly) split into multiple values
using its last character as a separator.
However, the operators differ on interpretation of empty
varchar values. With IN, an empty varchar value is
considered a single-item empty-string set, because IN is most
often used with single-value (i.e. non-set-like) parameters. This
makes the clause "WHERE varcharColumn IN ('red', 'green',
'blue')" only return "
blue" varcharColumn values - not empty-string values
too, as SUBSET would. This empty-string interpretation
difference is the one way in which IN differs from SUBSET
(and INTERSECT, if
With SUBSET/INTERSECT however, an empty varchar value is considered an empty set, because SUBSET/INTERSECT are more clearly set-like operators where both operands are sets, and an empty string is more likely to be intended to mean "empty set". This is also more consistent with convert() and INSERT behavior: an empty string converted or inserted into a strlst value becomes an empty strlst, not a one-item (empty-string) strlst.
The current (or indexed)
stringcomparemode setting value is
used during IN/SUBSET/INTERSECT operations; thus
case-insensitive comparisions can be accomplished by modifying the
setting. At search time, the Texis optimizer will choose the index
stringcomparemode setting is closest to the current value.
Caveat: IN/SUBSET/INTERSECT behavior with
multi-value types other than
strlst is currently undefined and
should be avoided. Single-value types other than
limited support currently; it is recommended that only
strlst) types be used.
See also here for version 6 and earlier IN issues.