SQLite Forum

create taable with syntax error works ?
Login

create taable with syntax error works ?

(1) By norm (npalardy) on 2021-09-17 18:46:05 [link] [source]

this statement "works" bt create the table with what appears to be columns missing (but the raw text of the create statement is identical)

NOTE there is a missing comma between "ProjectDescription String" and "ProjectStartDate"

Seems to me this should raise some kind of error

create table if not exists IDE_Projects( 
id Integer PRIMARY KEY Not NULL, 
ProjectName String Not NULL,  
ProjectDescription String  
ProjectStartDate DATE Not NULL
)

(2) By Larry Brasfield (larrybr) on 2021-09-17 18:54:45 in reply to 1 [link] [source]

See section "3.1. Determination Of Column Affinity" in Datatypes In SQLite Version 3. Your 3rd (and last) column has the strange type, "String ProjectStartDate DATE", which you can see (per criterion 5) resolves to NUMERIC.

(3.2) By norm (npalardy) on 2021-09-17 19:49:11 edited from 3.1 in reply to 2 [link] [source]

It creates the table with no errtor and the last column doesnt exist

the affinity doesnt seem to be the problem

create table if not exists IDE_Projects( 
  id INTEGER PRIMARY KEY NOT NULL, 
 ProjectName TEXT not null, 
  ProjectDescription text
  ProjectStartDate text not null 
 ) ;

but still the table is created - without complaint - but the column doesnt exist

insert into IDE_Projects( id, projectname, projectdescription, projectstartdate ) values ( 1, 'foo' , 'bar' , null ) ;

Error: table IDE_Projects has no column named projectstartdate

(4) By Larry Brasfield (larrybr) on 2021-09-17 19:54:05 in reply to 3.1 [link] [source]

You still have a strangely spelled affinity for the 3rd and final column specification. With your change, it meets criterion 2.

I did not say the affinity was your problem. Your problema is that, because of your strange spelling, you expect an error but SQLite does not see one.


a. This might be considered SQLite's problem, but it is a long-standing one. You may be interested in a soon-to-be-released SQLite feature, details of which can be seen here and in this forum in threads with "strict" (or casing equivalents) in their title.

(5) By Larry Brasfield (larrybr) on 2021-09-17 20:05:00 in reply to 3.2 [link] [source]

To approach this another way:

The syntax for type-name permits this CREATE TABLE statement: CREATE TABLE MyStory ( Firstly I came up with a plot, subsequently I fleshed it out creating a draft, whereupon I cleaned it up for publication );

Your story better resembles the SQL accepted by some other DBMS without quite being legal input to another DBMS, but that is not enough to make SQLite choke on it.

(6.1) By Thomas Hess (luziferius) on 2021-09-21 12:55:18 edited from 6.0 in reply to 3.2 [link] [source]

Look at ProjectDescription text line. It is missing a comma.

SQL largely ignores whitespace and SQLite accepts arbitrary strings as data types. So your ProjectDescription is a NOT NULL column with type set to “text ProjectStartDate text”, which is fine for SQLite.

(7) By norm (npalardy) on 2021-09-21 16:20:05 in reply to 6.1 [link] [source]

Yes I know the comma is missing

Thats the crux of why I think there's a bug

SQLIte is flexible but shouldnt there be SOME kind of error when there a pretty obvious syntactical issue like this ?

right now it silently accepts this statement and only later when I tried to insert data did it complain about the column not existing and that lead me to the create table where its quite plainly syntactically incorrect

(8) By Richard Damon (RichardDamon) on 2021-09-21 16:53:34 in reply to 7 [source]

But the syntax IS valid, it just has a very funny, but legal for SQLite, type for the second (and last) column.

(9) By Larry Brasfield (larrybr) on 2021-09-21 17:04:08 in reply to 7 [link] [source]

... there's a bug ... where its quite plainly syntactically incorrect

It's not a bug. The behavior is plainly and clearly documented at the doc pages I linked above to help you understand that the behavior conforms. In particular, the last syntax chart I linked shows that your odd spelling of a typename should be expected to be accepted by the parser, and the type affinity determination rules (linked earlier) reveal that it should survive semantic processing also.

SQLIte is flexible but shouldnt there be SOME kind of error when there a pretty obvious syntactical issue like this ?

What is pretty obvious to you is not as simple as you imagine. The typename acceptance laxity is a convenience for those migrating SQL constructs from Na other DBMSs to SQLite. Across that set of other DBMSs, a wide and fluctuating variety of typenames are legal. I would grant that, as wetware operators, we humans can generally recognize the trying-to-be-a-typename subset of names, but it is unreasonable to expect SQLite to do that. It would be a maintenance headache, and a death-by-dribbles ongoing "bug" source.

In several weeks, the STRICT option will be available. The set of accepted typenames for tables with that option is very small (as another link I provided above shows.) So you should use that if it is too hard to remember to separate your column definitions as SQL requires.


a. N is an unknown and unbounded number.

(11) By norm (npalardy) on 2021-10-01 22:07:33 in reply to 9 [link] [source]

I dont expect sqlite to map other dbms column types

I was just surprised that there wasnt any apparent error warning or any other indication that I had missed a single comma Every other DB I've used for the last nearly 40 years (many of those N other db's you refer to ) gives me an error

So you should use that if it is too hard to remember to separate your column definitions as SQL requires.

This sort of left handed jab really isnt necessary

I indeed thought I HAD correctly specified my columns and actually hunted code elsewhere for the issue long before I even noticed the single missing comma in about 100K lines of code

STRICT mode will be welcome

(13) By Larry Brasfield (larrybr) on 2021-10-01 22:57:52 in reply to 11 [link] [source]

So you should use [STRICT] if it is too hard to remember to separate your column definitions as SQL requires.

This sort of left handed jab really isnt necessary

It was not meant as a jab. It can be hard sometimes to read code closely enough to realize what it means, even when one is fully familiar with the language being used. (I say this from experience.) Let's try that another way:

If you dislike having to keep in mind that SQLite will slurp up a long sequence of words as a typename, you will probably appreciate the STRICT feature.

I was just surprised that there wasnt any apparent error warning or any other indication that I had missed a single comma Every other DB ...

The fact that you would submit such a sequence to the SQLite compiler, and expect something different, shows that your expectations developed from work with other DBMSs need some adjustment, at least until you qualify table definitions with STRICT. That's the practical response rather than railing about SQLite behavior that is decades old and certain not to change. I am sympathetic about the feeling of having a well-entrenched expectation found wanting, but it's the price to be paid when one chooses to not become familiar with one's tools, hoping assumptions about them will be enough.

(10) By Keith Medcalf (kmedcalf) on 2021-09-21 19:12:44 in reply to 7 [link] [source]

This is a SEMANTIC error, not a SYNTAX error.

Their is know way for the computer to no that you elected to be shooting up the wrong words.

(12) By norm (npalardy) on 2021-10-01 22:14:33 in reply to 10 [link] [source]

STRICT mode coming suggests otherwise Other DB's do it as well