On Sunday, 1 September, 2019 00:26, William Chargin <[email protected]> wrote:
>I tracked down a perplexing issue to the following behavior: > sqlite> CREATE TABLE tab (col); > sqlite> SELECT nope FROM tab; -- fails; good > Error: no such column: nope > sqlite> SELECT "nope" FROM tab; -- works? > sqlite> INSERT INTO tab (col) VALUES (77); > sqlite> SELECT col FROM tab WHERE nope IS NOT NULL; -- fails; good > Error: no such column: nope > sqlite> SELECT col FROM tab WHERE "nope" IS NOT NULL; -- works? > 77 >It seems that "nope" is being interpreted as a string literal here, >while quoted names of valid columns are not: > sqlite> SELECT "nope", "col" FROM tab; > nope|77 >I see that this is discussed briefly in the documentation, though the >exception as written only applies to quoted keywords, which "nope" is >not: <https://www.sqlite.org/lang_keywords.html> >But it seems especially surprising that the parse tree should depend >on the actual identifier values and table schemata, making the grammar >not context-free. >Is this working as intended? Are there plans to make SQLite reject >such examples as malformed queries instead of implicitly coercing? Yes, this is working as intended. Double-quotes strings refer to column names if the semantics permit a column name to appear at that location and the column name exists. Otherwise it is treated as a constant single-quoted string. >My `sqlite3 --version`: > 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f Version 3.29 added options to DBCONFIG to require that quotes be interpreted according to the standard (double quotes are identifiers ONLY and single quotes are strings ONLY and compilation defines so that you can permanently make this the new default in your custom version of SQLite3. https://www.sqlite.org/draft/releaselog/3_29_0.html The default is to keep the old behaviour as the confusion surrounding the use of double and single quotes is quite pervasive and forcing the correct behaviour would cause applications which currently work to stop working if they use quotes incorrectly (which is extremely common). sqlite> .dbconfig enable_fkey on enable_trigger on enable_view on fts3_tokenizer off load_extension on no_ckpt_on_close off enable_qpsg off trigger_eqp off reset_database off defensive off writable_schema off legacy_alter_table off dqs_dml off dqs_ddl off sqlite> CREATE TABLE tab (col); sqlite> SELECT nope FROM tab; Error: no such column: nope sqlite> SELECT "nope" FROM tab; Error: no such column: nope sqlite> INSERT INTO tab (col) VALUES (77); sqlite> SELECT col FROM tab WHERE nope IS NOT NULL; Error: no such column: nope sqlite> SELECT col FROM tab WHERE "nope" IS NOT NULL; Error: no such column: nope -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
