SQLite

View Ticket
Login
Ticket Hash: 57353f8243c637c0b4e2e97eb269e2875e1e1268
Title: COLLATE expression in the right side of an IN operator results in an affinity conversion
Status: Fixed Type: Code_Defect
Severity: Minor Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-11 18:08:06
Version Found In:
User Comments:
mrigger added on 2019-06-11 11:50:02:

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