/ View Ticket
Login
Ticket UUID: 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:

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:

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' );

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.