SQLite/Room ORDER BY 与 CASE 语句会忽略列 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	stuff
1	0
2	a
4	A
3	z
5	Z

---

查询 #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

---

在 DB Fiddle 上查看

Answer 1

任何 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;

请参阅演示。