Hi Richard, Thank you for taking the time to reply. Personally I never use double-quoted strings as string literals, but this issue was reported by users of a tool that generates table creation SQL based on user input, and that encloses in parenthesis any default values entered by the user to accommodate expressions. This is no longer an issue in this case as I implemented a workaround.
Perhaps this could be fixed in SQLite 4 if backwards compatibility is less a concern? Regards, Bogdan Ureche On Sun, Feb 10, 2013 at 2:39 PM, Richard Hipp <[email protected]> wrote: > The fact that SQLite will treat a double-quoted string as a string literal > rather than as a quoted identifier is a horrible mis-feature. It was added > 10 years or so ago in an attempt to be more MySQL-compatible. I have come > to sorely regret that change. I'd love to get rid of this mis-feature, but > cannot do so now, since there are millions of applications in the wild that > use SQLite and some percentage of those (hopefully a very small percentage, > but still non-zero) will break if I remove the mis-feature. > > The point is that allowing double-quoted strings is a tragic design error. > > You should never make use of this feature. Ever. All of your string > literals should use the SQL-standard single-quote notation. > > You are correct that the mis-feature is inconsistent in its application. > But remember that it is a mis-feature. You shouldn't be using it, and so > inconsistencies in its implementation shouldn't matter to you. > Furthermore, since the only reason for preserving this mis-feature is for > backwards compatibility in legacy applications, I see no need to try to > make it more consistent. Any attempts at making it consistent would likely > just break a few legacy applications, and if I were willing to do that I > would simply remove the mis-feature all together. > > On Sun, Feb 10, 2013 at 2:52 PM, Bogdan Ureche <[email protected]> > wrote: > > > Using SQLite 3.7.15.2. The following statements execute with no error: > > > > CREATE TABLE [test1] ( > > [id] INTEGER, > > [name] CHAR DEFAULT 'test'); > > > > CREATE TABLE [test2] ( > > [id] INTEGER, > > [name] CHAR DEFAULT ('test')); > > > > CREATE TABLE [test3] ( > > [id] INTEGER, > > [name] CHAR DEFAULT "test"); > > > > > > However, the following returns "default value of column [name] is not > > constant." > > > > CREATE TABLE [test4] ( > > [id] INTEGER, > > [name] CHAR DEFAULT ("test")); > > > > > > There are no identifiers in this context named "test" - no other tables > or > > columns, and "test" is not a keyword. Changing "test" to a keyword like > > "key" or "glob" returns the same error. > > > > The SQLite documentation reads: > > > > "If a keyword in double quotes (ex: "key" or "glob") is used in a context > > where it cannot be resolved to an identifier but where a string literal > is > > allowed, then the token is understood to be a string literal instead of > an > > identifier." > > > > So in the last statement, "test" (or "key") is used in a context where it > > cannot be resolved to an identifier but the token is *not* understood to > be > > a string literal. > > > > To resume: while "test" is evaluated correctly as a string literal, > after > > enclosing it in parenthesis ("test") it no longer is. > > > > Does this work as intended? > > > > BTW, in my opinion the above quote should be changed to: > > > > "If a string in double quotes (be it keyword or not) is used in a context > > where it cannot be resolved to an identifier but where a string literal > is > > allowed, then the token is understood to be a string literal instead of > an > > identifier." > > > > Regards, > > Bogdan Ureche > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > [email protected] > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
