|19:12||• Fixed ticket [fccbde53]: DISTINCT thinks a zeroblob() and blob of all zeros are different plus 5 other changes (artifact: 609faf4c user: drh)|
|19:12||Change the OP_Found opcode so that it expands zero-blobs prior to comparing them. Fix for ticket [fccbde530a6583b] (check-in: e2303d1b user: drh tags: trunk)|
|18:47||• New ticket [fccbde53] DISTINCT thinks a zeroblob() and blob of all zeros are different. (artifact: 74e2e659 user: drh)|
|Title:||DISTINCT thinks a zeroblob() and blob of all zeros are different|
|Last Modified:||2014-02-08 19:12:24|
|Version Found In:||3.8.3|
drh added on 2014-02-08 18:47:06:
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.