sqlite3 double quote behavior

[Roel Schroeven]

Roel Schroeven schreef op 13/12/2022 om 22:18:
> Chris Angelico schreef op 13/12/2022 om 20:01:
> > > Perhaps it's a better idea to use [identifier] or `identifier` instead
> > > though (I just learned about those on
> > > https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> > > used in MS Access and SQL Server, `` is used in MySQL) but both work in
> > > SQLite. That should prevent any ambiguity and confusion, if it doesn't
> > > bother you too much that it's not standard SQL.
> > >
> >
> > Why not just use "identifier" which is standard SQL?
>
> If you accidentally type [identifire] or `identifire`, SQLite will
> produce an unknown identifier error, alerting you immediately to your typo.
> If you accidentally type "identifire", SQLite will silently treat it as
> a string literal instead of an identifier, causing more difficult to
> diagnose problems.
Example:

-- Preparation:
sqlite> create table foo ("columna" text, "columnb" text);
sqlite> insert into foo values ("xyzzy", "xyzzy");

-- Variant with "":
sqlite> select count(*) from foo where "columna" = "colummb";
0

Not at all at first sight clear why there seem to be no matching rows, 
if you even notice straightaway that the result is not correct.

-- Variant with []:
sqlite> select count(*) from foo where [columna] = [colummb];
Error: no such column: colummb

Immediately clear that there is a problem, and what the problem is.


-- 
"In the old days, writers used to sit in front of a typewriter and stare out of
the window. Nowadays, because of the marvels of convergent technology, the thing
you type on and the window you stare out of are now the same thing.”
         -- Douglas Adams