/ Ticket Change Details
Login
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

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

  2. Change login to "drh"
  3. Change mimetype to "text/x-fossil-wiki"
  4. Change priority to "Immediate"
  5. Change resolution to "Open"
  6. Change subsystem to "Unknown"