SQLite

In the following SQL, the SELECT statement occurs twice, once before and once after the index is created. The two SELECTs give different answers. This should never be. An index will hopefully make the answer appear more quickly, but it should never change the answer.

CREATE TABLE b1(a TEXT); INSERT INTO b1 VALUES(1);
CREATE TABLE b2(x BLOB); INSERT INTO b2 VALUES(1);
SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2);
CREATE UNIQUE INDEX b1a ON b1(a);
SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2);

This problem has been in the code since before version 3.3.7 (2006-08-12), and and hence gone unnoticed for over ten years. For that reason, it is given a low severity.

This problem was discovered during internal code reviews of the row-value feature planned for the 3.15 release. There have been no reports of this problem in the wild.

This is related to [93fb9f89d6a69dc3d8], though that prior ticket dealt with joins instead of IN operators. Consider:

CREATE TABLE b1(a TEXT); INSERT INTO b1 VALUES(1);
CREATE TABLE b2(x BLOB); INSERT INTO b2 VALUES(1);
SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2);
SELECT count(*) FROM b1, b2 WHERE a=x;
CREATE UNIQUE INDEX b1a ON b1(a);
SELECT count(*) FROM b1 WHERE a IN (SELECT x FROM b2);
SELECT count(*) FROM b1, b2 WHERE a=x;

The code above used to return "0 0 1 1" prior to check-in [e72186f2d6] which was the fix for ticket [93fb9f89d6a69dc3d8]. After that check-in (circa 2009-08-13) the answer returned was "0 0 1 0". The correct answer should be "0 0 0 0".