SQLite

View Ticket
Login
Ticket Hash: 1e39b966ae9ee7394334f06b022a5e75a94e28ce
Title: LEFT JOIN strength reduction optimization causes an error.
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-04-10 12:20:56
Version Found In: 3.23.0
User Comments:
drh added on 2018-04-10 12:05:34: (text/x-fossil-wiki)
The new LEFT JOIN strength reduction optimization is sometimes applied in
cases where it should not be, resulting in an error.  The following
query demonstrates:

<blockquote><verbatim>
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
INSERT INTO t1(a,b) VALUES(1,null);

SELECT a FROM t1 LEFT JOIN t2 ON (b=x) 
 WHERE NOT ( b IS NOT NULL AND y='xyz' );
</verbatim></blockquote>

The problem was caused by the NOT operator, which the prover did not
recognize as inverting the logic of its operand, effectively converting
the AND into an OR.

The problem was discovered by Raphael Michel and reported on the sqlite-users
mailing list.