Update of "The infamous DQS misfeature"
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID: d437e32b54d06a0b73c126c37c618ee1eb1d8d47
Page Name:The infamous DQS misfeature
Date: 2019-08-01 12:01:02
Original User: sandro
Parent: 0008e59eecedf3c723191d1a499dd58108153edf
Content

back



Introduction

DQS simply is the acronym for Double-Quoted Strings.
All previous versions of SQLite3 were affected by an obnoxious DQS misfeature that has been finally resolved starting from version 3.29.0 (released on 2019-07-10).

What the ISO SQL standards dictates

The ISO SQL standard has two different kinds of text strings:
  1. String Literals (aka Text Constants)
    They are always interpreted as values, and must necessarily be enclosed within a pair of Single Quotes.
    Example: SELECT * FROM x WHERE name = 'something';
  2. SQL names (aka SQL identifiers)
    They are intended to identify Tables, Columns, Triggers and alike, and must optionally enclosed within a pair or Double Quotes.
    More precisely:
    • SQL names containing any forbidden character must be always masked by enclosing the intended name within Double Quotes.
    • any other SQL name (not containing forbidden characters) does not strictly requires Double-quoting, but can be optionally Double-quoted.
    Example: SELECT "a", "b", "c" FROM "x";

What was effectively supported by SQLite

In an attempt to mimic the non-standard behavior of MySQL 3.x SQLite was always very tolerant about Single- and Double-Quoting.
Even in the case of incorrect quoting SQLite was often able to guess the intended scope of any string by analyzing its context.
It clearly was a potentially error prone approach, but it helped many sloppy or messy users to write their SQL queries with few complications.
Very simply said, it was a misfeature badly encouraging many developers into the bad habit of misusing double- and single-quoting.

The most recent approach of SQLite to ISO SQL quoting

In recent times the developers of SQLite finally recognized that departing from the ISO SQL standard wasn't good at all.
And consequently they decided that starting from version 3.29.0 SQLite should finally be able to correctly enforce strict ISO SQL quoting.
Unhappily such a radical change can have a very heavy impact on already existing applications, so a moderate approach was adopted:

A practical comparative test

Tested on SQLite 3.25.3Tested on SQLite 3.29.0 - compiled with -DSQLITE_DQS=0
CREATE TABLE "test" (
    "id" INTEGER PRIMARY KEY,
    "name" TEXT NOT NULL);
-----------------------------
ok

INSERT INTO "test" ("id", "name") VALUES (123, "one");
-----------------------------
ok

SELECT "id" FROM "test" WHERE "name" = "one";
-----------------------------
123

INSERT INTO test (id, name) VALUES (123456, 'two');
-----------------------------
ok

SELECT id FROM test WHERE name = 'two';
-----------------------------
123456
CREATE TABLE "test" (
    "id" INTEGER PRIMARY KEY,
    "name" TEXT NOT NULL);
-----------------------------
ok

INSERT INTO "test" ("id", "name") VALUES (123, "one");
-----------------------------
no such column: one

SELECT "id" FROM "test" WHERE "name" = "one";
-----------------------------
no such column: one

INSERT INTO test (id, name) VALUES (123456, 'two');
-----------------------------
ok

SELECT id FROM test WHERE name = 'two';
-----------------------------
123456

Anecdotal evidence

The code of libspatialite itself had very little problems in nicely adapting to he most recent -DSQLITE_DQS=0 requirements of SQLite 3.29.0.
There were just a couple of badly quoted SQL Statements requiring to be fixed.

But properly restoring the test coverage was a nasty surprise.
There were plenty of misquoted SQL Statements (about some thousands), and patiently nursing all them was a lengthy and boring task.

Be warned: switching to the more stringent requirements of the most recent versions of SQLite
can easily become a real nightmare, most notably if you usually adopted a sloppy SQL quoting style.


Important notice for Windows users

All future binary packages for Windows distributed by SpatiaLite itself will be always built by specifying the -DSQLITE_DQS=0 option.

Be prepared.





back