|12:20||• Fixed ticket [1e39b966]: LEFT JOIN strength reduction optimization causes an error. plus 5 other changes (artifact: f7116625 user: drh)|
|12:10||Fix a problem causing the LEFT JOIN strength reduction optimization to be incorrectly applied in some cases where the WHERE clause of the query contains a filter expression of the form NOT(x AND y). Ticket [1e39b966]. (check-in: 38d319c1 user: dan tags: trunk)|
|12:05||• New ticket [1e39b966] LEFT JOIN strength reduction optimization causes an error.. (artifact: 99d5851c user: drh)|
|Title:||LEFT JOIN strength reduction optimization causes an error.|
|Last Modified:||2018-04-10 12:20:56|
|Version Found In:||3.23.0|
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.