Currently, SQLite assumes that if the result-set of a SELECT DISTINCT query is collectively subject to a UNIQUE constraint, the DISTINCT qualifier is redundant.
However, this is only true if there are no NULL values in the result set, as NULL values are considered distinct for the purposes of UNIQUE constraints, but identical for the purposes of DISTINCT processing. For example:
<verbatim>
SQLite version 3.7.12 2012-04-20 15:24:53
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1(x, y);
sqlite> INSERT INTO t1 VALUES(NULL, 'row');
sqlite> INSERT INTO t1 VALUES(NULL, 'row');
sqlite> SELECT DISTINCT x, y FROM t1;
|row
sqlite> CREATE UNIQUE INDEX i1 ON t1(x, y);
sqlite> SELECT DISTINCT x, y FROM t1;
|row
|row
sqlite>
</verbatim>
<hr><i>dan added on 2012-04-20 17:27:06:</i><br>
Introduced here: [45e581bf]
<hr><i>dan added on 2012-04-20 17:44:18:</i><br>
Fixed here: [7b8548b187]
|