SQLite

View Ticket
Login
Ticket Hash: ced41c7c7d6b4d362bea7b4283515d77962fdb2a
Title: Query with DISTINCT does not fetch all distinct rows
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-07-30 01:17:23
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-07-29 22:42:21: (text/x-fossil-wiki)
Consider the test case below:

<pre>
CREATE TABLE t1 (c1 , c2, c3, c4 , PRIMARY KEY (c4, c3));
INSERT INTO t1(c3) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (1), (0);
UPDATE t1 SET c2 = 0;
INSERT INTO t1(c1) VALUES (0), (0), (NULL), (0), (0);
ANALYZE t1;
UPDATE t1 SET c3 = 1;
SELECT DISTINCT * FROM t1 WHERE t1.c3 = 1; -- expected: |0|1|, 0||1|, ||1|, actual: |0|1|
</pre>

I would expect the query to fetch the three distinct rows, however, only one is fetched. If the ANALYZE is omitted, then all three distinct rows are fetched.