SQLite Forum

Removing not null constraint doesn't behave correctly
Login
(Please forgive my intrusion into a subthread here.)

I see two different problems here, indicating one or more "bugs" as I believe we understand the term.

Bug1:

As the OP indicated in post 1, in a comment, the [docs here](https://sqlite.org/lang_altertable.html#altertabaddcol), about 60% through section 5, state that the OP's procedure is appropriate for his task. Specifically, it says, "The following simpler procedure is appropriate for removing CHECK or FOREIGN KEY or NOT NULL constraints ...", which is precisely what he attempted to do. That attempt, which followed the 9 step procedure faithfully (I think), seemed to succeed. However, a later operation that should have succeeded, an add column via ALTER TABLE, resulted in a what appears to be a syntax error.

This is enough, IMO, to say that there is a discrepancy between what the docs say is appropriate and what will reliably leave the running DBMS in a state where it can operate in other ways as documented.  It seems to be a bug for that reason.

Bug2:

The specific error that occurs at the end of the OP's repro steps is reported as a syntax error. But it is not. The syntax is fine, as evidenced by it being accepted after closing and reopening the DB or studying the railroad chart.

I maintain that the parser, (which I assume generates the "syntax error" diagnostic), should not produce different results when parsing the same SQL at different times. Either it is looking at different SQL to produce the error seen by the OP, or its behavior has been affected by something very likely to be the infamous undefined behavior that results from bad pointer usage. Either way, it reflects some undesirable state left behind by the OP's use of the "appropriate" 9 step procedure.

This may well be manifestation a more serious bug than simple mismatch between documented behavior and implemented behavior.