106 replies

Pagination 'count' is not accurate if the SQL query result contains duplicate id's

The pagination count SQL query is using

DISTINCT

DISTINCT

DISTINCT

DISTINCT like this:

SELECT coalesce(count(DISTINCT t0."id"::bigint), $1::bigint)::bigint FROM (...) ...

SELECT coalesce(count(DISTINCT t0."id"::bigint), $1::bigint)::bigint FROM (...) ...

SELECT coalesce(count(DISTINCT t0."id"::bigint), $1::bigint)::bigint FROM (...) ...

SELECT coalesce(count(DISTINCT t0."id"::bigint), $1::bigint)::bigint FROM (...) ...

If the result set returned by the sub query has duplicate

id

id

id

ids then the count doesn't reflect the actual number of rows returned by the sub query because

DISTINCT

DISTINCT

DISTINCT

DISTINCT is removing them from the count.

Is there any way to remove

DISTINCT

DISTINCT

DISTINCT

DISTINCT from the pagination count query?