SQLite

CREATE TABLE t1(x,y);
INSERT INTO t1 VALUES(1,2);
INSERT INTO t1 VALUES(2,7);
INSERT INTO t1 VALUES(3,4);
CREATE TABLE t2(a, b TEXT);
CREATE INDEX t2b ON t2(b);
INSERT INTO t2 VALUES(1,2);
INSERT INTO t2 VALUES(5,6);
SELECT *, y=b FROM t1, t2 WHERE y=b;

In the final SELECT statement, the last column ("y=b") shows zero (false) in the output. Clearly this must be wrong because if "y=b" is false, the WHERE clause should have prevented the row from being returned.

The problem goes away if the index on t2(b) is removed. It appears that the query planner is using the t2b index to resolve the WHERE clause even though the t1.y and t2.b columns have incompatible type affinities.

drh added on 2009-08-19 15:21:05:
This error is present in all prior versions of SQLite back through version 3.1.3 (2005-02-19) and perhaps earlier. (Version 3.1.3 is as far back as we looked.)

The fix for the problem is check-in [e72186f2d68d28c2e0c32894f9adb28c155b5f63].

Test cases for this fix are in the test/whereB.test test script.