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 <bogdan...@gmail.com> 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
