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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
