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