Metadata missing for generated columns

(1) By J-L Hainaut (JLHainaut) on 2020-06-25 14:43:12 updated by 1.1 [source]

I have particularly appreciated the recent addition of "generated column" in SQLite. However, it seems that their description is missing in system table "table_info" (sqlite 3.32). 
Let us consider this small schema:

<code>
create table S(Sid integer not null primary key);
create table T(Tid integer not null primary key,
               C1 integer,
               C2 integer generated always as (2*C1) references S);
create index XC2 on T(C2);
create view VT as select C2 from T;
</code>

Generated column "C2" does not appear in system table table_info('T'):

<code>
select * from pragma_table_info('T');
+-----+------+---------+---------+------------+----+
| cid | name | type    | notnull | dflt_value | pk |
+-----+------+---------+---------+------------+----+
| 0   | Tid  | integer | 1       | --         | 1  |
| 1   | C1   | integer | 0       | --         | 0  |
+-----+------+---------+---------+------------+----+
</code>

However, "C2" in view VT is correctly referenced:

<code>
select * from pragma_table_info('VT')
+-----+------+--------------------------+---------+------------+----+
| cid | name | type                     | notnull | dflt_value | pk |
+-----+------+--------------------------+---------+------------+----+
| 0   | C2   | integer generated always | 0       | --         | 0  |
+-----+------+--------------------------+---------+------------+----+
</code>

Same for index "XC2":

<code>
select * from pragma_index_list('T')
+-----+------+--------+--------+---------+
| seq | name | unique | origin | partial |
+-----+------+--------+--------+---------+
| 0   | XC2  | 0      | c      | 0       |
+-----+------+--------+--------+---------+
</code>

select * from pragma_index_xinfo('XC2')
+-------+-----+------+------+--------+-----+
| seqno | cid | name | desc | coll   | key |
+-------+-----+------+------+--------+-----+
| 0     | 2   | C2   | 0    | BINARY | 1   |
| 1     | -1  | --   | 0    | BINARY | 0   |
+-------+-----+------+------+--------+-----+

... and for foreign keys:

<code>
select * from pragma_foreign_key_list('T')
+----+-----+-------+------+----+-----------+-----------+-------+
| id | seq | table | from | to | on_update | on_delete | match |
+----+-----+-------+------+----+-----------+-----------+-------+
| 0  | 0   | S     | C2   | -- | NO ACTION | NO ACTION | NONE  |
+----+-----+-------+------+----+-----------+-----------+-------+
</code>

It would be nice to see it fixed in a future release!

Thanks

Metadata missing for generated columns

(1.1) By J-L Hainaut (JLHainaut) on 2020-06-25 20:02:29 edited from 1.0 [link] [source]

I have particularly appreciated the recent addition of "generated column" in SQLite. However, it seems that their description is missing in system table "table_info" (sqlite 3.32). Let us consider this small schema:

create table S(Sid integer not null primary key); create table T(Tid integer not null primary key, C1 integer, C2 integer generated always as (2*C1) references S); create index XC2 on T(C2); create view VT as select C2 from T;

Generated column "C2" does not appear in system table table_info('T'):

select * from pragma_table_info('T'); +-----+------+---------+---------+------------+----+ | cid | name | type | notnull | dflt_value | pk | +-----+------+---------+---------+------------+----+ | 0 | Tid | integer | 1 | -- | 1 | | 1 | C1 | integer | 0 | -- | 0 | +-----+------+---------+---------+------------+----+

However, "C2" in view VT is correctly referenced:

select * from pragma_table_info('VT') +-----+------+--------------------------+---------+------------+----+ | cid | name | type | notnull | dflt_value | pk | +-----+------+--------------------------+---------+------------+----+ | 0 | C2 | integer generated always | 0 | -- | 0 | +-----+------+--------------------------+---------+------------+----+

Same for index "XC2":

select * from pragma_index_list('T') +-----+------+--------+--------+---------+ | seq | name | unique | origin | partial | +-----+------+--------+--------+---------+ | 0 | XC2 | 0 | c | 0 | +-----+------+--------+--------+---------+ select * from pragma_index_xinfo('XC2') +-------+-----+------+------+--------+-----+ | seqno | cid | name | desc | coll | key | +-------+-----+------+------+--------+-----+ | 0 | 2 | C2 | 0 | BINARY | 1 | | 1 | -1 | -- | 0 | BINARY | 0 | +-------+-----+------+------+--------+-----+

... and for foreign keys:

select * from pragma_foreign_key_list('T') +----+-----+-------+------+----+-----------+-----------+-------+ | id | seq | table | from | to | on_update | on_delete | match | +----+-----+-------+------+----+-----------+-----------+-------+ | 0 | 0 | S | C2 | -- | NO ACTION | NO ACTION | NONE | +----+-----+-------+------+----+-----------+-----------+-------+

It would be nice to see it fixed in a future release!

Thanks

(2) By Richard Hipp (drh) on 2020-06-25 15:41:17 in reply to 1.0 [link] [source]

Generated column "C2" does not appear in system table table_info('T'):

Please try using table_xinfo('T') instead. That seems to work for me.

(3) By J-L Hainaut (JLHainaut) on 2020-06-25 16:02:00 in reply to 2 [link] [source]

You are right. It works.

Thanks

(4) By midijohnny on 2023-03-17 21:41:11 in reply to 3 [link] [source]

👍