SQLite Forum

A question about the "type" field in the result of PRAGMA table_xinfo for the generated column.
Login

A question about the "type" field in the result of PRAGMA table_xinfo for the generated column.

(1) By ChirsL on 2020-06-15 08:26:17 [link] [source]

An example for SQLite version 3.32.2:

If a table "Test"'s structure is

CREATE TABLE Test ( a INTEGER, b INTEGER GENERATED ALWAYS AS (a), c INTEGER GENERATED ALWAYS AS (a) NOT NULL, d INTEGER NOT NULL GENERATED ALWAYS AS (a), e INTEGER AS (a) );

The result of PRAGMA table_xinfo(Test); will be:

cid,name,type,notnull,dflt_value,pk,hidden 0,a,INTEGER,0,,0,0 1,b,INTEGER GENERATED ALWAYS,0,,0,2 2,d,INTEGER GENERATED ALWAYS,1,,0,2 3,e,INTEGER,1,,0,2 4,c,INTEGER,0,,0,2

So the question is, since from the document(https://sqlite.org/gencol.html), we know that the "GENERATED ALWAYS" is a column constraint, and it's not a column type, is it proper to appears in the "type" field of the result of PRAGMA table_xinfo?

And if this is a designed behavior, then why do the column "d" and "e" which are also generated column just display a type name "INTEGER" without "GENERATED ALWAYS" appended to the right?

Thank you for seeing my question.

(2) By Larry Brasfield (LarryBrasfield) on 2020-06-15 15:07:20 in reply to 1 [source]

I am going to reform your example and reframe your question because the issue can be brought out more clearly that way and some confounding composition errors are removed. (And, the answer to my question will answer yours.)

If a table is created thusly:

CREATE TABLE Test (
    a INTEGER,
    gann INTEGER GENERATED ALWAYS AS (a) NOT NULL,
    nnga INTEGER NOT NULL GENERATED ALWAYS AS (a)
);

, then, after .mode list, PRAGMA table_xinfo(Test); produces this output:

cid|name|type|notnull|dflt_value|pk|hidden
0|a|INTEGER|0||0|0
1|gann|INTEGER GENERATED ALWAYS|1||0|2
2|nnga|INTEGER|1||0|2

Why, when columns gann and nnga have identical column constraints, merely specified in a different order, do their table_xinfo fields differ in the 'type' column?

(3) By Richard Hipp (drh) on 2020-06-15 15:44:13 in reply to 2 [link] [source]

My testcase is:

  .mode box
  CREATE TABLE t1(a INT, b INT GENERATED ALWAYS AS (a+2));
  PRAGMA table_xinfo=t1;

Which generates this output:

  ┌─────┬──────┬──────────────────────┬─────────┬────────────┬────┬────────┐
  │ cid │ name │         type         │ notnull │ dflt_value │ pk │ hidden │
  ├─────┼──────┼──────────────────────┼─────────┼────────────┼────┼────────┤
  │ 0   │ a    │ INT                  │ 0       │            │ 0  │ 0      │
  │ 1   │ b    │ INT GENERATED ALWAYS │ 0       │            │ 0  │ 2      │
  └─────┴──────┴──────────────────────┴─────────┴────────────┴────┴────────┘

This is a cosmetic problem. It will not cause incorrect answers. It is also a problem that is deep down inside the Lemon parser generator, and will be tricky to fix. It boils down to an unavoidable grammar ambiguity that Lemon is resolving the wrong way. As the result is harmless, the fix has a low priority.

(4) By Larry Brasfield (LarryBrasfield) on 2020-06-15 16:06:11 in reply to 1 [link] [source]

I see that I failed to see your question: Is it proper that what seems to be a column constraint appears in the 'type' column of table_xinfo?

I see no other existing column of table_xinfo that would be more proper.

As for propriety, this table_xinfo documentation makes few claims, and none are violated insofar as I can see.

I will refrain from philosophical delving into a wider "What is proper?" inquiry. (You're welcome.)

(5) By ChirsL on 2020-06-15 16:31:13 in reply to 3 [link] [source]

Thank you for the reply. I was just not feel intuitive about it at first sight. If it’s a little tricky to fix, I am okay to stay with the behavior.