D 2019-08-01T10:32:11.346
L The\sinfamous\sDQS\smisfeature
U sandro
W 3160
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0-doc">back</a><hr><br>
<h1>Introduction</h1>
<b>DQS</b> simply is the achronym for <b>Double-Quoted Strings</b>.<br>
All previous versions of <b>SQLite3</b> were affected by an obnoxius <i><b>DQS misfeature</b></i> that has been finally resolved starting from version <b>3.29.0</b> (released on <b>2019-07-10</b>).
<h3>What the ISO SQL standards dictates</h3>
The ISO SQL standard has two different kinds of text strings:
<ol>
<li><b>String Literals</b> (aka <i><b>Text Constants</b></i>)<br>
They are always interpreted as <b>values</b>, and <b>must</b>
necessarily be enclosed within a pair of <b>Single Quotes<b>.<br>
<b>Example</b>: <i><b>SELECT * FROM x WHERE name = 'something';</b></i></li>
<li><b>SQL names</b> (aka <i><b>SQL identifiers</b></i>)<br>
They are intended to identify <b>Tables</b>, <b>Columns</b>, <b>Triggers</b> and alike, and <b>must</b> optionally enclosed within a pair or <b>Double Quotes</b>.<br>
More precisely:<ul>
<li>SQL names containing any <b><i>forbidden character</i></b> must be always masqued by enclosing the intended name within Double Quotes.</li>
<li>any other SQL name (not containing forbidden characters) does not strictly requires Double-quoting, but can be optionally Double-quoted.</li></ul>
<b>Example</b>: <i><b>SELECT "a", "b", "c" FROM "x";</b></i></li>
</li>
</ol>
<h3>What was effectively supported by SQLite</h3>
In an attempt to mimic the non-standard behavior of <b>MySQL 3.x</b> SQLite was always very tolerant about Single- and Double-Quoting.<br>
Even in the case of incorrect quoting SQLite was often able to guess the intended scope of any string by analyzing its context.<br>
It clearly was a potentially error prone approach, but it helped many sloppy or messy users to write their SQL queries with few complications.<br>
Very simply said, it was a <b>misfeature</b> badly encouraging many developers into the bad habit of misusing double- and single-quoting.<br>
<h3>The most recent approach of SQLite to ISO SQL quoting</h3>
In recent times the developers of SQLite finally recognized that departing from the ISO SQL standard wasn't good at all.<br>
And consequently they decided that starting from version <b>3.29.0</b> SQLite should finally be able to correctly enforce strict ISO SQL quoting.<br>
Unhappily such a radical change can have a very heavy impact on already existing applications, so a moderate approach was adopted:
<ul>
<li>a new build-time option has been introduce, <b>-DSQLITE_DQS=<i>n</i></b></li>
<li>The default assumption is <b>-DSQLITE_DQS=3</b><br>
this practically corresponds to the old traditional behavior (i.e. supporting a very permissive and tolerant quoting interpretation).</li>
<li>But the developers of SQLite stongly reccommed to always explicitly set <b>-DSQLITE_DQS=3</b><br>
this will enforce strict ISO SQL quoting, and will definitely eradicate the extavagant <b>DQS misfeaure</b>.</li>
</ul>
<br><br>
<hr><br>
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0-doc">back</a>
Z 5baf21c42c9f9e31144fa6c94b533d08