The IN, SUBSET and INTERSECT operators, being
set-like, share certain behaviors in common in Texis version 7 (or
compatibilityversion
7) and later:
A 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 varchar
value
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 "red
", "green
" or
"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 inmode
is intersect
).
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
whose 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 varchar
have
limited support currently; it is recommended that only varchar
(and strlst
) types be used.
See also here for version 6 and earlier IN issues.