DENSE_RANK ignores COLLATE NOCASE

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

DENSE_RANK ignores COLLATE NOCASE

Yuriy Garin
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users