Re: [sqlite] Non-keyword quoted identifiers parsed as string literals

[William Chargin]

Thank you both for your quick and helpful replies! The `quirks.html`
page certainly clears things up. Glad to see that there are new options
to disable this; I reached out to the maintainers of the language
bindings that I use to see if we can get that enabled [1].

[1]: https://github.com/JoshuaWise/better-sqlite3/issues/301

Best,
WC
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Non-keyword quoted identifiers parsed as string literals

[Keith Medcalf]

On Sunday, 1 September, 2019 00:26, William Chargin  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: 

>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Non-keyword quoted identifiers parsed as string literals

[Ben Kurtovic]

> Is this working as intended? Are there plans to make SQLite reject such
> examples as malformed queries instead of implicitly coercing?

This problematic behavior, including discussion on how to disable it, is 
documented here: https://www.sqlite.org/quirks.html#dblquote 


Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Non-keyword quoted identifiers parsed as string literals

[William Chargin]

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: 

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?

My `sqlite3 --version`:

3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users