Bug Report: "Is Null" where term propagated to a virtual table causes wrong query results

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Bug Report: "Is Null" where term propagated to a virtual table causes wrong query results

Josef Kučera
Hello,
I have found an obscure bug in the virtual table processing.

The schema looks like this:
CREATE TABLE t1 (id int, value text);
INSERT INTO t1 VALUES(1,'try');
CREATE TABLE t2 (ctx int, id int, value text);
INSERT INTO t2 VALUES(1,1,'good');
INSERT INTO t2 VALUES(2,2,'evil');

The following query works ok if t1 and t2 are real:
sqlite> select * from t2 left join t1 on t1.id=t2.ctx where t1.value is
null;
2|2|evil||

If they are virtual (and the module implements ISNULL processing) the
result is wrong:
sqlite> select * from t2 left join t1 on t1.id=t2.ctx where t1.value is
null;
1|1|good||
2|2|evil||

Looking at WhereTrace I can see the ISNULL term applied to t1 without
the L flag. The virtual module correctly returns 0 records for t1
search, but t2 records are returned anyway.

As a workaround I can use a unary operator, effective blocking the
ISNULL term processing in virtual module:
sqlite> select * from t2 left join t1 on t1.id=t2.ctx where +t1.value is
null;
2|2|evil||

Best regards,
Joe
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users