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

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

[Hick Gunter]

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:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von William Chargin
Gesendet: Sonntag, 01. September 2019 08:26
An: sqlite-users@mailinglists.sqlite.org
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: 

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


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