sqlite3 double quote behavior

[Chris Angelico]

On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <roel at roelschroeven.net> wrote:
>
> 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.
>

Okay, so..... exactly the same as if you use standard double quotes,
but change the configuration option. So the options are: make
everything worse for everyone by exacerbating the problem of
non-standard identifier quoting, or get this API so SQLite can be
configured, like the OP actually asked for.

Yeah. Let's not do the wrong thing.

ChrisA