On Wed, Jan 25, 2017 at 9:55 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 25 Jan 2017, at 5:45pm, Ersin Akinci <ersin.aki...@gmail.com> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Aha. Makes sense. And as you noted in your bug report, Simon, the
single quoted version should create a computed index, as well, but for
some reason it doesn't.
Thanks for the clarification!
-Ersin
--
Ersin Y. Akinci -- ersinakinci.com
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
