Generated columns and COLLATE in the AS parens

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