Hi! It looks like DENSE_RANK doesn't work correctly with COLLATE NOCASE columns.
CREATE TABLE fruits ( name TEXT COLLATE NOCASE, color TEXT COLLATE NOCASE ); -- Note mixed case spelling. INSERT INTO fruits (name, color) VALUES ('apple', 'RED'); INSERT INTO fruits (name, color) VALUES ('APPLE', 'yellow'); INSERT INTO fruits (name, color) VALUES ('pear', 'YELLOW'); INSERT INTO fruits (name, color) VALUES ('PEAR', 'green'); SELECT DENSE_RANK() OVER (ORDER BY name) AS '#name', DENSE_RANK() OVER (PARTITION BY name ORDER BY color) AS '#color' FROM fruits; -- produces: -- #name #color -- ---------- ---------- -- 1 2 -- 2 1 -- 3 1 -- 4 2 But, expectation was: -- #name #color -- ---------- ---------- -- 1 1 -- 1 2 -- 2 1 -- 2 2 It looks like generated VM code ignores COLLATE: 33 SorterOpen 13 4 0 k(1,B) 00 But, I guess, it should be 33 SorterOpen 13 4 0 k(1,NOCASE) 00 Detailed SQL is attached. Thanks! -- Regards Yuriy _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users