Different answer with and without index on IN operator with type mismatch
|User & Date:||drh 2016-09-03 15:24:12|
- Change icomment to:
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".
- Change login to "drh"
- Change mimetype to "text/x-fossil-wiki"
- Change priority to "Immediate"
- Change resolution to "Open"
- Change subsystem to "Unknown"