sqlite3 double quote behavior

[Roel Schroeven]

Chris Angelico schreef op 13/12/2022 om 20:01:
> On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <roel at roelschroeven.net> wrote:
> >
> > Stefan Ram schreef op 13/12/2022 om 8:42:
> > > "John K. Parejko" <parejkoj at gmail.com> writes:
> > > >I was just burned by this, where some tests I’d written
> > > >against an sqlite database did not fail in the way that they
> > > >“should” have, because of this double-quoted string issue.
> > >
> > >    In standard SQL, double quotes denote identifiers that are
> > >    allowed to contain special characters.
> > Or that are equal SQL keywords, which can be a reason to double-quote
> > them. SQL engines sometimes add new keywords; explicitly marking string
> > literals as string literals prevents future conflicts and confusion.
> >
> > 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.

-- 
"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