🪲 COUNT(*) and DISTINCT

[joocer]

These two queries should return the same result, as id is unique values

SELECT COUNT(*) FROM (SELECT DISTINCT id FROM $planets) AS S

SELECT COUNT(*) FROM (SELECT id FROM $planets) AS S

but the inclusion of DISTINCT in the first query causes the result to be incorrect.

[joocer]

The problem appears to be the projection pushdown and count(*) optimizations.

Because count(*) doesn't reference any columns when we remove the subquery we don't reference any of the inner columns.

What we need to do is ensure the distinct explicitly references the columns so that the projection pushdown doesn't eliminate them.

Currently the reference to columns by distinct is implied, it references all of them.