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
