SQLite

Consider the following statement:

SELECT (1 IN (CAST('1' as TEXT) COLLATE NOCASE)); -- expected: 0, actual: 1

I expect the right side to not have an affinity, as stated in the documentation:

The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list [...]

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:

SELECT (1 IN (CAST('1' as TEXT))); -- 0

If the list has more than one element, also no affinity conversion is performed, as expected:

SELECT (1 IN (CAST('1' as TEXT) COLLATE NOCASE, 2)); -- 0