SQLite

View Ticket
Login
Ticket Hash: 9ece23d2caf8853a4d06fddeafbe6ec182721b6d
Title: Default collation sequences and affinities lost when window function added to query
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-07-08 12:03:27
Version Found In:
User Comments:
dan added on 2019-07-08 11:48:30:

For example, this:

  CREATE TABLE t1(a BLOB, b INTEGER, c COLLATE nocase);
  INSERT INTO t1 VALUES(1, 2, 'abc');
  INSERT INTO t1 VALUES(3, 4, 'ABC');

  SELECT c=='Abc', 0 FROM t1;

returns two tuples: (1, 0) and (1, 0).

But this:

  SELECT c=='Abc', rank() OVER (ORDER BY b) FROM t1

returns (0, 1) and (0, 2). The default collation sequence of column "c" was lost when the window function was added to the query.


dan added on 2019-07-08 11:50:20:

Discovered and reported to the mailing list by Yuriy Garin:

https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg115803.html


dan added on 2019-07-08 12:03:08:

This has been present since window functions were added for version 3.25.0.

Fixed by [28196d89].