Hi!
I think there might be a glitch in the way SQLite 3.31.x derives the collation
information from the expression of a generated column.
In particular, COLLATE inside the AS parens seems to be ignored, but it is
honoured after the parens:
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE tmp (str VARCHAR(255), str_nc1 GENERATED ALWAYS AS (str
COLLATE NOCASE), str_nc2 GENERATED ALWAYS AS (str) COLLATE NOCASE);
sqlite> INSERT INTO tmp VALUES ('a'), ('A'), ('b'), ('B');
sqlite> SELECT * FROM tmp ORDER BY str;
A|A|A
B|B|B
a|a|a
b|b|b
sqlite> SELECT * FROM tmp ORDER BY str_nc1;
A|A|A
B|B|B
a|a|a
b|b|b
sqlite> SELECT * FROM tmp ORDER BY str_nc2;
a|a|a
A|A|A
b|b|b
B|B|B
sqlite> SELECT * FROM tmp ORDER BY (str COLLATE NOCASE);
a|a|a
A|A|A
b|b|b
B|B|B
sqlite>
I believe the "ORDER BY str_nc1” should yield the same row order as the last
two queries.
-markus
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
