Bug: `GENERATED ALWAYS` constraint ends up in column type
(1) By Bjoern Hoehrmann (bjoern) on 2021-06-12 14:34:50 [source]
Some of the tokens forming a GENERATED ALWAYS column constraint
sometimes end up in the column type. I think this is inconsistent
with the syntax diagrams (when these tokens are interpreted as
type-name they cannot also be interpreted as constraint syntax)
and internally inconsistent (e.g., this happens with anonymous
column constraints, but does not happen if the column constraint
is named):
sqlite> create table t1(id int, x int generated always as (true));
sqlite> create view v1 as select * from t1;
sqlite> create table t2(id int, x int constraint c generated always as (true));
sqlite> create view v2 as select * from t2;
sqlite> select * from PRAGMA_TABLE_XINFO('t1');
cid name type notnull dflt_value pk hidden
--- ---- -------------------- ------- ---------- -- ------
0 id int 0 0 0
1 x int generated always 0 0 2
sqlite> select * from PRAGMA_TABLE_XINFO('t2');
cid name type notnull dflt_value pk hidden
--- ---- ---- ------- ---------- -- ------
0 id int 0 0 0
1 x int 0 0 2
(2) By Richard Hipp (drh) on 2021-06-12 15:28:08 in reply to 1 [link] [source]
That's the way it gets parsed in SQLite.
When the GENERATED ALWAYS keywords were added in SQLite 3.31.0 (2020-01-22), we needed to ensure that legacy SQLite database files that might use the words "generated" or "always" as column or table names would continue to work. For that reason, "generated" and "always" can still be understood as identifiers in addition to keywords. Furthermore, the typename of a column is any sequence of identifiers. Hence "int generated always" ends up being parsed as the typename for the column.
If the typename contains parenthesized arguments (ex: VARCHAR(32)), then the parser knows that the typename ends at the close parenthesis. In that case the GENERATED ALWAYS keywords are understood as being part of the computed column clause, not as part of the typename.
If sometimes seeing "GENERATED ALWAYS" in the typename is a problem for you, you can simply leave those keywords out of your table definition. "AS" is the only keyword that is needed to make use of computed columns. The "GENERATED ALWAYS" prefix is just some filler words that are thrown into the SQL standard, perhaps to make SQL seem more like COBOL or something. Those words are not required and do not really accomplish anything, other than slowing down schema parsing a little.
(3) By Richard Hipp (drh) on 2021-07-30 20:11:48 in reply to 1 [link] [source]
Problem addressed by this check-in: https://sqlite.org/src/timeline?c=3c954863f45271a3&n=12&y=ci