Public bug reported:
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)
** Affects: sqlite3 (Ubuntu)
Importance: Undecided
Status: New
** Tags: amd64 apport-bug eoan
** Attachment added: "Script with sql statements to demonstrate bug"
https://bugs.launchpad.net/bugs/1865364/+attachment/5332361/+files/sqlite_nocase_bug.sql
--
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
https://bugs.launchpad.net/bugs/1865364
Title:
SELECT WHERE using COLLATE NOCASE fails using multiple tests and using
IN(...)
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/sqlite3/+bug/1865364/+subscriptions
--
ubuntu-bugs mailing list
ubuntu-bugs@lists.ubuntu.com
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
