带有CASE语句的SQLite/Room ORDER BY将忽略COLLATE列

Question

关于使用SQLite的ORDER BY子句中的CASE语句，我有一些不理解的地方(最初我在Android Room中使用它，但不是特定的(。

这是对问题的提炼。查询1返回我所期望的内容(一个不区分大小写的有序列表(。而查询2似乎忽略了列的COLLATE NOCASE。为什么？

谢谢！

架构(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");

---

查询#1

SELECT *
FROM test
ORDER BY
stuff ASC;

id	填充
1	0
2	a
4	A
3	z
5	Z

任何CASE表达式的结果都是表达式，即使其返回值是对类似THEN stuff的列的简单引用
对于此返回的表达式，没有明确定义的排序规则序列，因此为了进行ORDER BY子句比较，使用的排序规则顺序是BINARY

如果ORDER BY子句只是：，也会发生同样的情况

ORDER BY stuff || '' ASC

上面的表达式stuff || ''只返回列stuff的值，但它仍然被认为是表达式，并且将使用BINARY排序序列

如果要将特定的排序序列应用于CASE表达式的结果，则必须在ORDER BY子句中显式使用它：

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

请参阅演示