On 23 Jan 2017, at 9:33pm, Ersin Akinci <[email protected]> wrote:
> CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
> (yearz_doesnt_exist);
> CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
> ('yearz_doesnt_exist');
> CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
> ("yearz_doesnt_exist");
>
> The first two of these statements will fail with the error 'Error: no such
> column: yearz_doesnt_exist', but the last one goes through.
The first one is doing what you said.
In the second index you are, technically, asking it to create a computed index
on a constant string (that’s what the apostrophes mean). I don’t understand
why this doesn’t work for me. I’m using SQLite 3.14.0.
The third one /should/ do what the first one does, since double-quotes indicate
entity-names.
> After executing
> the last statement, if we look at the output of index_list and index_info,
> we get:
>
> sqlite> PRAGMA index_list(reports);
> 0|index_reports_on_yearz_doesnt_exist|0|c|0
> sqlite> PRAGMA index_info(index_reports_on_yearz_doesnt_exist);
> 0|-2|
I don’t get that. Instead I get
sqlite> CREATE INDEX index_reports_quotes ON reports
...> ("yearz_doesnt_exist");
sqlite> PRAGMA index_info('index_reports_quotes');
0|-2|
sqlite> PRAGMA index_xinfo('index_reports_quotes');
0|-2||0|BINARY|1
1|-1||0|BINARY|0
The "-2" in each case is undocumented. I suspect that that it means
'calculated column' and it’s just not documented yet.
[later] I see DRH has cleared this up. SQLite failed to find a column with
that name and is interpreting the string as a text string. Therefore it is
indeed creating a calculated index.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
