[Bug 1865364] [NEW] SELECT WHERE using COLLATE NOCASE fails using multiple tests and using IN(...)

[Simon Lambourn]

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
Foundations Bugs, 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