SELECT WHERE using COLLATE NOCASE fails using multiple tests and using IN(...)

[Simon Lambourn]

COLLATE NOCASE seems to be ignored when using the following constructs in a WHERE clause:

Col=value1 COLLATE NOCASE OR Col=value2
Col IN(value COLLATE NOCASE)
Col IN(value) COLLATE NOCASE [both are accepted, not sure which is valid syntax]

It works in the simple case Col=value1 COLLATE NOCASE

I have written a simple test script which demonstrates working queries and failing queries, which is attached, and reproduced here:

# demo of sqlite bug with collate nocase
# usage: sqlite3 :memory:
# > .read sqlite_nocase_bug.sql
#
create temp table test (col text unique);
insert into test (col) values('Test');

select '1:', * from test where col='Test'; -- works
select '2:', * from test where col='test' collate nocase; -- works
select '3:', * from test where col='test' collate nocase or col='other' collate nocase; -- returns no rows
select '4:', * from test where col in ('test') collate nocase; -- returns no rows
select '5:', * from test where col in ('test' collate nocase); -- returns no rows

ProblemType: Bug
DistroRelease: Ubuntu 19.10
Package: sqlite3 3.29.0-2ubuntu0.1
ProcVersionSignature: Ubuntu 5.3.0-40.32-generic 5.3.18
Uname: Linux 5.3.0-40-generic x86_64
ApportVersion: 2.20.11-0ubuntu8.4
Architecture: amd64
CurrentDesktop: ubuntu:GNOME
Date: Sun Mar 1 18:17:53 2020
SourcePackage: sqlite3
UpgradeStatus: Upgraded to eoan on 2019-10-23 (129 days ago)