Non-keyword quoted identifiers parsed as string literals

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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: <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?

My `sqlite3 --version`:

    3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <https://www.sqlite.org/quirks.html#dblquote>

Ben
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Non-keyword quoted identifiers parsed as string literals

Keith Medcalf
In reply to this post by William Chargin
On Sunday, 1 September, 2019 00:26, William Chargin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
In reply to this post by William Chargin
This is documented behaviour. Use single quotes for literal strings. SQLite will assume you meant 'literlal' if your write "literal" and there is no column of that name. There is no need to quote names in SQLite unless the name contains non-alpha characters.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von William Chargin
Gesendet: Sonntag, 01. September 2019 08:26
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Non-keyword quoted identifiers parsed as string literals

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?

My `sqlite3 --version`:

    3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

William Chargin
> This is documented behaviour. Use single quotes for literal strings.
> SQLite will assume you meant 'literlal' if your write "literal" and
> there is no column of that name. There is no need to quote names in
> SQLite unless the name contains non-alpha characters.

Thanks, yes. I was quoting the names because they _did_ contain
non-alpha characters. The actual example was that I had a table

    CREATE TABLE primitives_Commit(
        id TEXT NOT NULL PRIMARY KEY,
        oid,
        author,
        "author.date"
    );

and another table with a column "author.user", and had mistakenly tried
to SELECT things FROM primitives_Commit WHERE "author.user" IS NOT NULL.

The reason for the funny names is that the table schema was dynamically
generated by an abstraction layer. I have since rewritten that layer
such that this is no longer necessary, and gotten rid of double quotes
entirely, which seems to be for the best. :-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users