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]

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]

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

Problem addressed by this check-in:
<https://sqlite.org/src/timeline?c=3c954863f45271a3&n=12&y=ci>