SELECT WHERE using COLLATE NOCASE fails using multiple tests and using IN(...)
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
sqlite3 (Ubuntu) |
New
|
Undecided
|
Unassigned |
Bug Description
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_
#
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
ProcVersionSign
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)