If the standard requires static typing then anything it says about static types 
does not apply to SQLite3 because SQLite3 uses dynamic strong typing and does 
not use static fixed typing.

That means that the "type" assigned in a column is merely a "suggestion" on how 
to store the data for this column, and that data will be converted to this type 
if possible (this is called the column affinity).  A particular "value" (stored 
at the intersection of a column and a row) may be of any "type", 
notwithstanding what the column declaration of the table has to say about it.

The supported "types" are:

 NULL - a NULL value
 INTEGER a 64-bit signed integer
 REAL - a 64-bit IEEE-754 double precision float
 TEXT - a sequence of bytes in the encoding of the underlying database (set by 
pragma encoding)
 BLOB - a sequence of bytes with no meaning

A "value" has a concrete "type" associated with it and nothing else.
A "column" has a preferred storage "type" (called the affinity) and a bunch of 
constraints, plus a collation specifying the collation sequence to apply when 
the "value" contained in some particular row of that column contains TEXT.

For example:

sqlite> create table x(x integer default ('1'));
sqlite> insert into x default values;
sqlite> select typeof(x), x from x;
integer|1

The column "x" in the table "x" has integer affinity (would prefer data to be 
stored as integers if it can be).
The default value is a text string '1'.
When you execute the insert, x takes the default value '1'.
When this value is stored the column affinity is applied and the value that is 
actually stored is the integer 1.

Similarly,

sqlite> drop table x;
sqlite> create table x(x integer default ('test'));
sqlite> insert into x default values;
sqlite> select typeof(x), x from x;
text|test

The application of affinity integer for the column cannot convert the value 
'test' into an integer, so the value is stored as text.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Markus Winand
>Sent: Friday, 31 January, 2020 03:29
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Generated columns and COLLATE in the AS parens
>
>Let me first explain how collations work in standard SQL. I think my
>answers below make more sense then.
>
>In the SQL standard….
>
>- character string **types** have properties such as fix-length vs.
>variable length, the length (limit), character set and also the
>collation.
>
>- columns have types, which include the collation if it is a character
>string type.
>  However, it is not the column that has a collation. The column has a
>type, which might have a collation.
>
>- values have a static type (the so-called “declared type”). Static means
>it is determined at “compile time” from the syntax and the data
>dictionary.
>
>- Expressions also have a declared type. More elaborate: the result value
>of an expression has a type that is statically determined (again  the
>“declared type”).
>
>- expressions of which the declared type is a character string type, will
>also have all the properties of character string types (including the
>collation).
>
>- When character strings are compared, the effective collation is
>determined from the declared types of the operands.
>   The SQL standard defines rules how to do that (e.g. in 9075-2 9.15
>"Collation determination”)
>   The most important rule is the "collation derivation” order: explicit,
>implicit, none. That’s the rule that says
>   the COLLATE clause on expressions (“explicit” derivation) is stronger
>than the COLLATE clause following type names (“implicit” derivation).
>
>But note that the last statement is not the reason the collation of the
>generation expression takes precedence of the data type — IT DOESN’T!
>
>
>As far as I know, this is basically unchanged sind SQL-92. If you do not
>have access to the current release of the standard (of 2016, to which my
>references apply), you can also have a look at SQL-92 here (search for
>"4.2  Character strings”):
>
>https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
>
>Generated columns where introduced with SQL:2003 and are thus not in the
>linked document.
>
>
>Putting these mechanics of working with collations in context of
>generated columns:
>
>- Generated columns need a type like any other column.
>  If that type happens to be a character string type, the type includes
>the collation as part of the types properties.
>
>- Even though generated columns need a type, they allow skipping the type
>in the definition of the generated column—that’s very unique to generated
>columns.
>  E.g. specifying a DEFAULT clause does not lift the requirement to
>explicitly state the type of the column.
>
>- If a generated column doesn’t have an explicit data type mentioned, the
>the data type of the result of the expression is used.
>  Whatever data type that is, whatever properties that type has.
>  If it happens to be a character string type, it will also have a
>“collation” property.
>
>However, the declared type of the expression (including its properties)
>is only relevant if the column definition doesn’t explicitly state a data
>type.
>
>These are two different cases in standard SQL:
>
>  C1 TEXT GENERATED ALWAYS AS (…)
>  C2      GENERATED ALWAYS AS (…)
>
>The type of C1 is TEXT, including all its default properties.
>The type of C2 is the type of the result of the expression, including all
>its properties.
>
>The “what’s new” paper for SQL:2003 mentions that case explicitly.
>
>http://sigmodrecord.org/publications/sigmodRecord/0403/E.JimAndrew-
>standard.pdf
>
>The example on the last page:
>
>> CREATE TABLE EMPLOYEES (
>>  EMP_ID INTEGER,
>>  SALARY DECIMAL(7,2),
>>  BONUS DECIMAL(7,2),
>>  TOTAL_COMP GENERATED ALWAYS AS (SALARY + BONUS)
>>  )
>>
>> TOTAL_COMP is a generated column of the EMPLOYEES table. The data type
>of the TOTAL_COMP is the data type of the expression (SALARY_BONUS).
>
>I think that’s a typo and should read (SALARY + BONUS).
>
>> Users may optionally specify a data type for a generated column, in
>which case the specified data type must match with the data type of the
>associated expression.
>
>The last part is not 100% in line with the current standard. The types
>don’t need to match, they need to be assignable. I don’t know if this is
>just a simplification for this paper or if SQL:2003 really hat that
>requirement.
>
>Further comments to your statements below…
>
>> On 30 Jan 2020, at 23:06, Keith Medcalf <kmedc...@dessus.com> wrote:
>>
>>
>> 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).
>
>This is where the SQL standard is different: if there is no type, the
>column gets the type of the expression (ISO/IEC 9075-2, 11.4 SR13c)
>
>> If no COLLATE is specified for the column, then the default collation
>BINARY applies.
>
>Again, the SQL standard is different: If there is no COLLATE clause
>**and** not data type specified, the collation is inherited via the data
>type of the result of the expression.
>
>If the column definition explicitly states a data type, that type comes
>with a collation, which takes precedence of the type of the expression.
>
>> If no NULL constraint is specified then NULL values are permitted.
>
>If not NOT NULL constraint….
>
>> 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?
>
>Because this is what is written in the SQL standard. And it make sense
>when collations are a property of character string types, which they are
>in the SQL standard.
>
>> 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?
>
>Because you cannot use DEFAULT without specifying a type name at the very
>same time (in standard SQL).
>
>11.4, SR 11) If <generation clause> is omitted, then either <data type>
>or <domain name> shall be specified.
>
>If it would be possible to skip the data type when a DEFAULT clause is
>present, I guess the same rules as for generated columns would apply
>(take the type, including all its properties) of the expressions result.
>
>>
>> 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.
>
>It is an attribute of character string **types**.
>
>Character string **columns** have collations because their type has one.
>
>Values have a collation because their type has one.
>
>>
>> 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.
>
>But the case was bringing up is that the generated column doesn’t have a
>type declared.
>
>>
>> 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).
>
>Assuming Y is a character string and the collation of Y is not NOCASE,
>the type of both expressions is different (but not the value). As the
>type contains the collation, it is not necessarily useless.
>
>>  The result of the expression is a value of the concrete type of y with
>no associated collation.
>
>The result of the expression “(Y)” has the very same declared type as Y
>itself. If Y is a character string, the result of the expression has the
>same collation as Y itself.
>
>>  This value then has the whatever column affinity is specified applied
>to it and inherits the collation of the containing column.
>
>If the column has a defined data type or an explicit COLLATE outside the
>expression, these collations will be used. Otherwise: the collation of
>the declared type of the expression is used because the entire data type
>of the result of the expression is used.
>
>>
>> ie, "values" do not have a collation associated with them, column
>definitions and comparison operators on text have collation sequences
>associated with them.
>
>Values have a declared types, character string types have collations.
>
>Nobody cares about the collation of a character string until it takes
>part in a comparison.
>
>-markus
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to