On: Wednesday, 29 January, 2020 06:45, Markus Winand <markus.win...@winand.at>
wrote:
>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:
Carrying the COLLATE from an expression into the column definition is
incorrect. The definition of a generated column is:
<identifier> [type affinity] [GENERATED ALWAYS AS (<expression>)] [COLLATE
<identifier>] [<constraints> ...]
so why would the so including a COLLATE as part of the expression applies to
the expression, and not to the column. Except for the ordering of <identifier>
which must be first, followed by the type which must come second, the other
bits can appear in pretty much any ordering you heart desires (it is a Dim Sum
of clauses).
If the <type affinity> of the column not specified then its "type affinity" is
BLOB (None).
If no COLLATE is specified for the column, then the default collation BINARY
applies.
If no NULL constraint is specified then NULL values are permitted.
If no DEFAULT expression is specified then the default value is NULL.
Why would you think that the COLLATE applied to the evaluation of an expression
would "carry out" to the column itself? Especially in light of the fact that
the type/collation of the expression in a "DEFAULT (<expression>)" does not
carry out to the column definition?
The collation sequence applies to "comparison" operations of TEXT values, and
not to anything else (it is ignored for all other value types). It is an
attribute of a column (like column affinity) and not an attribute of a value.
In other words,
x AS (y == 'yahoo' collate nocase)
applies the affinity nocase to the evaluation of the == comparison operation.
The result of evaluation of the expression is a data value with a concrete type
(integer) and no associated collation. This value then has whatever column
affinity is specified applied to it and inherits the collation of the
containing column.
x AS (y collate nocase)
is the same thing as
x AS (y)
since there is no "comparison" operation in the expression to which the
collation can be applied (it is a useless attribute to the expression). The
result of the expression is a value of the concrete type of y with no
associated collation. This value then has the whatever column affinity is
specified applied to it and inherits the collation of the containing column.
ie, "values" do not have a collation associated with them, column definitions
and comparison operators on text have collation sequences associated with them.
>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.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
