So what confuses me is that I would think that what comes after "DEFAULT" would have to be a string literal if it's not an identifier. So why does it let you put something in there without needing to put it in quotes? Is that an SQLite thing or an SQL thing I've never noticed?
SQLite version 3.23.1 2018-04-10 17:39:29 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo1 (foo text default WhyDoesThisWork); sqlite> create table foo2 (foo text default But Not This); Error: near "This": syntax error sqlite> create table foo3 (foo text default 'Shouldn''t it be like this?'); sqlite> insert into foo1 default values; sqlite> insert into foo3 default values; sqlite> select * from foo1; foo WhyDoesThisWork sqlite> select * from foo3; foo Shouldn't it be like this? sqlite> In http://www.sqlite.org/lang_createtable.html is has the options "signed-number", "literal-value", or "(expr)" In http://www.sqlite.org/lang_expr.html under "Literal Values (Constants)": "A string constant is formed by enclosing the string in single quotes (')"... -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, May 17, 2018 12:01 PM To: SQLite mailing list Subject: Re: [sqlite] After update from 3.20 to 3.23 alter table throws error for boolean with default value FALSE On 5/17/18, heribert <herib...@scharnagl.com> wrote: > Hello, > > after updating to release 3.23 the following sql-command do not work any > longer > > 'alter table Inbox add column WasSend boolean default FALSE' > The command works with 3.20 upto now. Actually not. What 3.20 was doing was making the default equal to the string 'FALSE' not to the boolean value of false. (Try it for yourself and see!) This is not a deadly error since SQLite will interpret any string that does not begin with a digit as false if it is used as a boolean, so the string 'FALSE' evaluates to false, by happy accident. The bug in 3.20 does cause problem, however, if you use "... default TRUE" :-) > With 3.23 i got the error: 'Cannot add a column with non-constant default' I think if you use one of the prerelease snapshots for 3.24 that this problem will be fixed. By "fixed" I mean that it now works correctly, and renders and actual boolean result, if the default is either TRUE or FALSE. > > So i looked into the SQLite keyword list... but i didn't find neither > FALSE nor TRUE. So, why did the FALSE work with 3.20? For backwards compatibility reasons, TRUE and FALSE are identifiers, not keywords. They only work like boolean literals if you have no columns named "true" or "false". In that way, legacy database files that do have tables with columns named "true" or "false" will continue to function as they always have. There are about 1 trillion SQLite database files in circulation. For that reason, we work very hard to avoid causing problem for those legacy database files as we enhance SQLite, or as we fix historical bugs. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
