SQLite Forum

Bug: `GENERATED ALWAYS` constraint ends up in column type
Login

Bug: `GENERATED ALWAYS` constraint ends up in column type

(1) By Bjoern Hoehrmann (bjoern) on 2021-06-12 14:34:50 [link] [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 [source]