Overview
Artifact ID: | 4776331e7fafe22dc1aaffd1b94308fc35d2c229cff5bf7c4834062f2e90b969 |
---|---|
Ticket: | ce68383bf6aba2f8e2b0344d067364e22daf59dc
Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index |
User & Date: | drh 2017-07-18 16:57:15 |
Changes
- foundin changed to: "3.19.3"
- icomment:
The same LEFT JOIN query gives different results depending on whether or not automatic indexes are enabled. The code below illustrates: <blockquote><verbatim> CREATE TABLE t1(w int); CREATE TABLE t2(y int); INSERT INTO t1 VALUES(NULL); INSERT INTO t2 VALUES(1); CREATE INDEX t2y ON t2(y); SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w; PRAGMA automatic_index=off; SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w; </verbatim></blockquote> Both queries should return no rows. But when automatic indexes are turned on (the default) a single row of two NULLs is returned. Bisecting shows this problem was introduced by check-in [3428043cd0029445] This problem was discovered during internal testing and analysis and has never been reported in the wild.
- login: "drh"
- mimetype: "text/x-fossil-wiki"
- severity changed to: "Important"
- status changed to: "Open"
- title changed to:
Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index
- type changed to: "Code_Defect"