SQLite

View Ticket
Login
Ticket Hash: 199df4168c7bb752a5a752cb46d5d364278f0d53
Title: Different answer with and without index on IN operator with type mismatch
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-11-23 23:49:14
Version Found In: 3.14.1
User Comments:
drh added on 2016-09-03 14:19:59: (text/x-fossil-wiki)
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.

<blockquote><verbatim>
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);
</verbatim></blockquote>

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.

drh added on 2016-09-03 15:24:12: (text/x-fossil-wiki)
This is related to [93fb9f89d6a69dc3d8], though that prior ticket dealt
with joins instead of IN operators.  Consider:

<blockquote><verbatim>
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;
</verbatim></blockquote>

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".