Logic error when using CHECK constraints while adding a column

(1.1) By Simon Binder (sbinder) on 2022-01-16 13:52:50 edited from 1.0 [source]

I have compiled the 3.37.2 amalgamation with the recommended -DSQLITE_DQS=0 option.

Then, opening the shell and running the following two statements causes a logic error that is unexpected to me:

CREATE TABLE a (id INTEGER NOT NULL);
ALTER TABLE a ADD COLUMN enabled INTEGER NOT NULL DEFAULT 0 CHECK (enabled IN (0, 1));

This error only happens when compiling without DQS which surprises me as neither statement is using a string literal. Further, no error is reported when the check is added in the CREATE TABLE statement (CREATE TABLE a (id INTEGER NOT NULL, enabled INTEGER NOT NULL DEFAULT 0 CHECK (enabled IN (0, 1)));) -- regardless of whether DQS is enabled or not.

Is this the expected behavior? Can anyone explain to me why?

Edit: The error message from the shell is simply Error: in prepare, SQL logic error (1).

(2.1) By Harald Hanche-Olsen (hanche) on 2022-01-16 14:08:15 edited from 2.0 in reply to 1.0 [link] [source]

Deleted

(3.1) By Larry Brasfield (larrybr) on 2022-01-16 16:24:19 edited from 3.0 in reply to 1.1 [link] [source]

(Edited to add resolution.)

Thanks for the report. This does represent a bug, one which has been fixed. When DQS is not 0 for the build, the parser (and semantic analysis) is more lax. This laxity interacts with the checking involved in performing ALTER anything. As your CREATE (altered) TABLE demonstrates, the check should succeed.

(4.2) By Gwendal Roué (groue) on 2022-02-15 19:10:58 edited from 4.1 in reply to 3.1 [link] [source]

Hello,

I've been reported a similar issue.

Somewhere between SQLite 3.36.0 and 3.37.0, the following statements have started to fail:

CREATE TABLE books (price DOUBLE);
ALTER TABLE books ADD COLUMN title TEXT CHECK (LENGTH(title) > 0);

The workaround is indeed related to DQS: one has to enable the non-standard behavior about string literals for the statements to run as expected.

This is quite unfortunate, because it the DQS value that is recommended by the SQLite documentation that triggers this regression!