diff options
Diffstat (limited to 'www/quirks.html')
| -rw-r--r-- | www/quirks.html | 93 |
1 files changed, 91 insertions, 2 deletions
diff --git a/www/quirks.html b/www/quirks.html index 4e053e9..4ddc439 100644 --- a/www/quirks.html +++ b/www/quirks.html @@ -142,6 +142,7 @@ That Are Not In The GROUP BY Clause</a></div> <div class="fancy-toc1"><a href="#autoincrement_does_not_work_the_same_as_mysql">11. AUTOINCREMENT Does Not Work The Same As MySQL</a></div> <div class="fancy-toc1"><a href="#nul_characters_are_allowed_in_text_strings">12. NUL Characters Are Allowed In Text Strings</a></div> <div class="fancy-toc1"><a href="#sqlite_distinguishes_between_integer_and_text_literals">13. SQLite Distinguishes Between Integer And Text Literals</a></div> +<div class="fancy-toc1"><a href="#sqlite_gets_the_precedence_of_comma_joins_wrong">14. SQLite Gets The Precedence Of Comma-Joins Wrong</a></div> </div> </div> <script> @@ -464,7 +465,7 @@ that can be done using the same C-code as shown above except with the third parameter changed from 0 to 1. </p><p> As of SQLite 3.41.0 (2023-02-21) SQLITE_DBCONFIG_DQS_DDL and -SQLTIE_DBCONFIG_DQS_DML are disabled by default in the <a href="cli.html">CLI</a>. Use +SQLITE_DBCONFIG_DQS_DML are disabled by default in the <a href="cli.html">CLI</a>. Use the ".dbconfig" dot-command to reenable the legacy behavior if desired. @@ -545,5 +546,93 @@ See the "<a href="nulinstr.html">NUL characters in strings</a>" document for fur <p>It does this because an integer is not a string. Every other major SQL database engine says this is true, for reasons that the creator of SQLite does not understand. -</p> + +</p><h1 id="sqlite_gets_the_precedence_of_comma_joins_wrong"><span>14. </span>SQLite Gets The Precedence Of Comma-Joins Wrong</h1> + +<p>SQLite gives all join operators equal precedence and processes them +from left to right. But this is not quite correct. It should be that +comma-joins have lower precedence than all others join operators. +In other words, a FROM clause like this: + +</p><blockquote><p> +... FROM a, b RIGHT JOIN c, d ... +</p></blockquote> + +<p>This FROM clause should be parsed as follows: + +</p><div class="imgcontainer"> +<div style="max-width:153px;"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 153.328 245.544"> +<path d="M67,32L120,32L120,2L67,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="93" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text> +<polygon points="53,72 58,61 65,67" style="fill:rgb(0,0,0)"/> +<path d="M93,32L57,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="134,72 122,67 128,61" style="fill:rgb(0,0,0)"/> +<path d="M93,32L129,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M27,102L80,102L80,72L27,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="53" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text> +<path d="M121,102L146,102L146,72L121,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="134" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">D</text> +<polygon points="13,142 18,131 24,137" style="fill:rgb(0,0,0)"/> +<path d="M53,102L17,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="93,142 82,137 88,131" style="fill:rgb(0,0,0)"/> +<path d="M53,102L89,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M36,173L151,173L151,142L36,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="93" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">RIGHTÂ JOIN</text> +<path d="M2,173L25,173L25,142L2,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="13" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">A</text> +<polygon points="53,213 58,201 65,208" style="fill:rgb(0,0,0)"/> +<path d="M93,173L57,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="134,213 122,208 128,201" style="fill:rgb(0,0,0)"/> +<path d="M93,173L129,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M42,243L65,243L65,213L42,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="53" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">B</text> +<path d="M121,243L146,243L146,213L121,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="134" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">C</text> +</svg> +</div> +</div> + +<p>But SQLite instead parses the FROM clause like this: + +</p><div class="imgcontainer"> +<div style="max-width:188px;"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 188.691 245.544"> +<path d="M107,32L160,32L160,2L107,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="134" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text> +<polygon points="93,72 99,61 105,67" style="fill:rgb(0,0,0)"/> +<path d="M134,32L98,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="174,72 162,67 169,61" style="fill:rgb(0,0,0)"/> +<path d="M134,32L170,68" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M36,102L151,102L151,72L36,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="93" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">RIGHTÂ JOIN</text> +<path d="M161,102L186,102L186,72L161,72Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="174" y="87" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">D</text> +<polygon points="53,142 58,131 65,137" style="fill:rgb(0,0,0)"/> +<path d="M93,102L57,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="134,142 122,137 128,131" style="fill:rgb(0,0,0)"/> +<path d="M93,102L129,138" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M27,173L80,173L80,142L27,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="53" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">JOIN</text> +<path d="M121,173L146,173L146,142L121,142Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="134" y="157" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">C</text> +<polygon points="13,213 18,201 24,208" style="fill:rgb(0,0,0)"/> +<path d="M53,173L17,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="93,213 82,208 88,201" style="fill:rgb(0,0,0)"/> +<path d="M53,173L89,209" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M2,243L25,243L25,213L2,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="13" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">A</text> +<path d="M82,243L105,243L105,213L82,213Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="93" y="228" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">B</text> +</svg> +</div> +</div> + +<p>The problem can only makes a difference in the result when using +RIGHT OUTER JOIN or FULL OUTER JOIN in the same FROM clause with +comma-joins, which rarely happens in practice. And +the problem can be easily overcome using parentheses in the FROM clause: + +</p><blockquote><p> +... FROM a, (b RIGHT JOIN c), d ... +</p></blockquote> +<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/quirks.in?m=e04aef4f57">2024-05-22 18:42:01</a> UTC </small></i></p> |