On 25 Jan 2017, at 5:45pm, Ersin Akinci <[email protected]> wrote:
> Thanks Richard and Simon for your insights. I think I'm still missing
> a few things, though.
>
> 1. What does it mean when SQLite tries to create an index on a string?
> Simon suggested that it's creating a calculated index, but I'm not
> sure what means. (Does it just mean an "index" literally just on the
> word "yearz_doesnt_exist"? That seems quite silly.)
SQLite supports indexes on calculations. Or other expressions. So you should
be able to do things like
CREATE INDEX fred ON MyTable (CASE WHEN stock < 0 THEN 0 ELSE stock END)
Since a constant string is an expression, and single quotes must be used to
quote strings, this means you can do something like
CREATE INDEX mabel ON MyTable ('hello mom')
It won’t do anything useful, but it should work.
> 2. And regardless, why is there a different result when using single
> quotes vs. double quotes?
The two quotes mean completely different things to SQLite. Single quotes are
used to quote strings. Double quotes are used to quote entity names (like
column names, table names, etc.). Properly speaking the version of your
command with double quotes should be rejected for the reason you expected but,
as DRH explained, for historical reasons if there’s no column with the given
name SQLite thinks you want the contents of the quotes as a string. And that’s
what it’s doing.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
