In the [syntax diagram for expressions][1], we see that the COLLATE operator
is applied to an "expr". The COLLATE operator has high precedence, so it binds
more tightly that most everything else. For the phrase:
~~~~
b IN ('one') COLLATE nocase
~~~~
The "`('one')`" part is *not* an expression. That subphrase is part of the
IN expression. Hence, the expression must be parsed thusly:
~~~~
(b IN ('one')) COLLATE nocase
~~~~
So, in other words, the COLLATE operator is being applied to the result of
the IN operator, not to the operands of the IN operator.
Contrast the above with the equality constraint:
~~~~
b == 'one' COLLATE nocase
~~~~
Here, the "`'one'`" part is an expression, and so the COLLATE operator
is able to bind to it. And since COLLATE has higher precedence than ==,
we get the following parse:
~~~~
b == ('one' COLLATE nocase);
~~~~
So here, the COLLATE operator applies to the right operand of the == operator
and thus affects the operation of the == operator.
If you want to apply a collation to an IN operator, you need to put the
COLLATE operator on the left operand, which is an expression. Hence, you
probably want this:
~~~~
b COLLATE nocase IN ('one')
~~~~
Which parses out as:
~~~~
(b COLLATE nocase) IN ('one')
~~~~
[1]: https://www.sqlite.org/lang_expr.html