SQLite Forum

Why was there no error message at CREATE TABLE?
Login

Why was there no error message at CREATE TABLE?

(1) By Tim Streater (Clothears) on 2020-09-19 15:20:32 [source]

I just came within a whisker of distributing an app with this error:

Third-Mini% 
Third-Mini% sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table xx (a text, b integer);
sqlite> .schema
CREATE TABLE xx (a text, b integer);
sqlite> .tables
xx
sqlite> create table bad (a text b integer);
sqlite> .schema
CREATE TABLE xx (a text, b integer);
CREATE TABLE bad (a text b integer);
sqlite> .tables
bad  xx 
sqlite> select a from bad;
sqlite> select b from bad;
Error: no such column: b
sqlite> 

Should there not have been a syntax error reported at the CREATE TABLE stage?

(2) By Richard Hipp (drh) on 2020-09-19 15:40:18 in reply to 1 [link] [source]

No. Neither "text" or "integer" or any other datatype name is a keyword in SQLite. They are just identifiers. And SQLite allows any sequence of identifiers to be the datatype, to accommodate types like "NATIVE VARYING CHARACTER" and "UNSIGNED SHORT INTEGER" and whatnot. SQLite sees "TEXT B INTEGER" as just a string of identifiers and considers that whole string to be the datatype.

This case does, however, seem like it might be a useful new entry on the quirks page.

(4) By Tim Streater (Clothears) on 2020-09-19 17:06:19 in reply to 2 [link] [source]

I see, thanks.

A note somewhere would be welcome.

(5) By Tim Streater (Clothears) on 2020-09-19 21:50:58 in reply to 2 [link] [source]

It might also be useful to indicate what would be the affinity of column a in:

create table xxx (a text b integer);

So perhaps somewhere in section 3.1 on:

https://www.sqlite.org/datatype3.html

would be the place.

(6) By Keith Medcalf (kmedcalf) on 2020-09-19 22:13:15 in reply to 5 [link] [source]

It is already there in section 3.1 plain for all the world to see.

The answer is that by Rule 1 the affinity is INTEGER.

sqlite> create table xxx (a text b integer);
sqlite> pragma table_info(xxx);
┌─────┬──────┬────────────────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┐
│ cid │ name │      type      │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │
├─────┼──────┼────────────────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┤
│ 0   │ a    │ text b integer │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │
└─────┴──────┴────────────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┘

NB: This is my "improved" pragma that shows more information from the loaded schema, so regular release version SQLite3 will not return the additional information, although they will have the same internal schema information structures.

(3) By Richard Damon (RichardDamon) on 2020-09-19 15:42:03 in reply to 1 [link] [source]

If you parse that with the grammer diagrams,

CREATE TABLE bad(a text b integer)

parses as having a column a with type-name of 'text b integer'

as the type-name production allows for a series of names

Thus, the syntax IS valid.