Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/02/13 14:27, Simon Slavin wrote: > Any chance of killing it in SQLite 4 ? Continuing to beat a truly dead horse, it would be nice to help developers fix their existing codebase via something like an additional "lint mode". http://www.sqlite.org/src/info/25e09aa2ab Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAlEYS6EACgkQmOOfHg372QR+kgCePgJMHJ55coumXdEIsIOlOhJr Ns8An1bmLOFeJ/5vYMBa8nQJSw31MmLC =czW1 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis
On Sun, Feb 10, 2013 at 5:27 PM, Simon Slavin wrote: > > On 10 Feb 2013, at 8:39pm, Richard Hipp 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 > > Any chance of killing it in SQLite 4 ? I'm a big fan of getting rid of > things which were once (understandably) implemented for reasons like this. > Yes. One of the big purposes of SQLite4 is to get rid of cruft like this. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis
On 10 Feb 2013, at 8:39pm, Richard Hipp 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 Any chance of killing it in SQLite 4 ? I'm a big fan of getting rid of things which were once (understandably) implemented for reasons like this. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis
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 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 > 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 > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis
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 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users