IN, SUBSET, INTERSECT Commonality

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.