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
Touch seeded packages, which is subscribed to sqlite3 in Ubuntu.
https://bugs.launchpad.net/bugs/1865364
Title:
SELECT WHERE using COLLATE NOCASE fails using multiple tests and using
IN(...)
Status in sqlite3 package in Ubuntu:
New
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_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)
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/sqlite3/+bug/1865364/+subscriptions
--
Mailing list: https://launchpad.net/~touch-packages
Post to : touch-packages@lists.launchpad.net
Unsubscribe : https://launchpad.net/~touch-packages
More help : https://help.launchpad.net/ListHelp
