/ Ticket Change Details
Login
Overview

Artifact ID: 74e2e6591fb889b87b85f0ed38c78491cd3c03a3
Ticket: fccbde530a6583bf2748400919f1603d5425995c
DISTINCT thinks a zeroblob() and blob of all zeros are different
User & Date: drh 2014-02-08 18:47:06
Changes

  1. Change foundin to "3.8.3"
  2. Change icomment to:

    The logic that computes DISTINCT sometimes thinks that a zeroblob() and a blob of all zeros are different when they should be the same. The following query illustrates the problem:

    CREATE TABLE t1(a INTEGER);
    INSERT INTO t1 VALUES(3);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(3);
    CREATE TABLE t2(x);
    INSERT INTO t2
      SELECT DISTINCT
        CASE a WHEN 1 THEN x'0000000000'
               WHEN 2 THEN zeroblob(5)
               ELSE 'xyzzy' END
        FROM t1;
    SELECT quote(x) FROM t2;
    

    The final output above should only generate two rows: x'0000000000' and 'xyzzy' since x'0000000000' and zeroblob(5) are logically equivalent. But it ends up generating three rows. There are two x'0000000000' values in the output.

    Bisecting shows that this problem was introduced by check-in [45e581bff7a75db] on 2011-07-02 for SQLite version 3.7.8.

    This problem was discovered by the developers during code analysis and has never been reported in the wild.

  3. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Important"
  6. Change status to "Open"
  7. Change title to:

    DISTINCT thinks a zeroblob() and blob of all zeros are different

  8. Change type to "Code_Defect"