SQLite/Room ORDER BY with a CASE statement ignores column COLLATE-CodePudding

Theres something I don't understand with regards to a CASE statement in a ORDER BY clause using SQLite (initially I was using it with Android Room but its not specific to that).

This is a distillation of the problem. Query 1 returns what I was expecting (a case insensitive, ordered list of the stuff). Whereas Query 2 appears to ignore the column's COLLATE NOCASE. Why is that?

Thanks!

Schema (SQLite v3.30)

CREATE TABLE "test" (
    "id"    INTEGER,
    "stuff" TEXT COLLATE NOCASE,
    PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO test (stuff) VALUES ("0");
INSERT INTO test (stuff) VALUES ("a");
INSERT INTO test (stuff) VALUES ("z");
INSERT INTO test (stuff) VALUES ("A");
INSERT INTO test (stuff) VALUES ("Z");

Query #1

SELECT *
FROM test
ORDER BY
    stuff ASC;

id	stuff
1	0
2	a
4	A
3	z
5	Z

Query #2

SELECT * 
FROM test
ORDER BY 
    CASE WHEN true THEN stuff END ASC;

id	stuff
1	0
4	A
5	Z
2	a
3	z

View on DB Fiddle

CodePudding user response：

The result of any CASE expression is an expression, even if its return value is a simple reference to a column like THEN stuff.
For this returned expression there is no explicitly defined Collating Sequence, so for the purposes of the ORDER BY clause comparisons the collating sequence that is used is BINARY.

The same would happen if the ORDER BY clause was just:

ORDER BY stuff || '' ASC

The above expression stuff || '' returns just the value of the column stuff, but still it is considered an expression and the BINARY collating sequence will be used.

If you want to apply a specific collating sequence to the result of the CASE expression you must use it explicitly in the ORDER BY clause:

SELECT * 
FROM test 
ORDER BY CASE WHEN true THEN stuff END COLLATE NOCASE ASC;

See the demo.