Extra values outputted, with Partial Index and DISTINCT constraints applied

(1) By Yu Liang (LY1598773890) on 2021-04-06 22:56:57 [source]

For query:

CREATE TABLE person ( pid INT) ;
CREATE UNIQUE INDEX idx ON person ( pid ) WHERE pid == 1;
INSERT INTO person VALUES (1), (10), (10);
SELECT DISTINCT pid FROM person;
SELECT DISTINCT pid FROM person where pid = 10;

The expected answer should be: 1 10 (from the first SELECT) 10 (from the second SELECT)

However, the actual output is: 1 10 (from the first SELECT) 10 10 (from the second SELECT)

An extra 10 is outputted from the second SELECT stmt, with the constraint DISTINCT being applied.

Removing the CREATE UNIQUE INDEX statement seems to fix the problem.

(2) By Richard Hipp (drh) on 2021-04-06 23:33:01 in reply to 1 [link] [source]

Thanks for the report.

This is a real bug. SQLite had an optimization that omitted the DISTINCT clause as unnecessary if all terms of the result set were individually not-NULL (which the WHERE clause proved in the example above) and if all terms where elements of a UNIQUE index. But that optimization was incorrect. We also needed to add the constraint that the index should not be a partial index.

Fixed by check-in c2f940b02883e165.

(3) By Yu Liang (LY1598773890) on 2021-04-07 02:10:15 in reply to 2 [link] [source]

Thank you for the details and the patched information.