The documentation at [\[1\]][1] on collation says:
The rules for determining which collating function to use for a
binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT)
are as follows:
1. If either operand has an explicit collating function
assignment using the postfix COLLATE operator, then the
explicit collating function is used for comparison, with
precedence to the collating function of the left operand.
It also mentions the following:
The expression "x IN (SELECT y ...)" is handled in the same way as
the expression "x = y" for the purposes of determining the
collating sequence. The collating sequence used for expressions of
the form "x IN (y, z, ...)" is the collating sequence of x.
Given the above I would have expected that SELECT with an IN expression
to behave the same as a SELECT with an '=' expression when COLLATE
NOCASE is added to either one. But that does not seem to be the case:
-- 3.32.0 2020-04-18 14:12:00 d5b0def96ba6d90f47bc96fab1ccf9c501d84885d086744035b16fd96f3e248c
CREATE TABLE a(b TEXT);
INSERT INTO a
VALUES
('one'),
('ONE');
SELECT *
FROM a
WHERE b = 'one' COLLATE nocase;
-- b
-- ----------
-- one
-- ONE
SELECT *
FROM a
WHERE b IN ('one') COLLATE nocase;
-- b
-- ----------
-- one
This appear to be an oversight in (hopefully) the implementation or the
documentation.
[1]: https://www.sqlite.org/datatype3.html#assigning_collating_sequences_from_sql