(text/x-fossil-wiki)
Consider the following statement:
<pre>
SELECT (1 IN (CAST('1' as TEXT) COLLATE NOCASE)); -- expected: 0, actual: 1
</pre>
I expect the right side to not have an affinity, as stated in the documentation:
<pre>
The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list [...]
</pre>
However, it seems that using a COLLATE operator results in an affinity conversion, since the expression above yields 1 rather than 0. Without the COLLATE, no affinity conversion is performed:
<pre>
SELECT (1 IN (CAST('1' as TEXT))); -- 0
</pre>
If the list has more than one element, also no affinity conversion is performed, as expected:
<pre>
SELECT (1 IN (CAST('1' as TEXT) COLLATE NOCASE, 2)); -- 0
</pre>
|