Wrong results with analytic count(distinct) on varchar-column with larger partitions

Detail name	Value
Changelog Number	11261
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 6.2.13, Exasol 7.0.6
Resolution Date	2021-01-12

Description

The following conditions may cause a wrong result (count is too high).

• A query uses count(distinct <expression>) as an analytic function
• The over-clause of the analytic function is empty or has a PARTITION BY-clause such that at least one partition has a size of at least 2000 rows.
• The expression from count(distinct <expression>) is of type CHAR or VARCHAR

Preparation

create schema test;
create table t(vc_col varchar(100));

insert into t(vc_col)
(with ten(i) as (values 1,2,3,4,5,6,7,8,9,0)
select mod(rn,11) from (select row_number() over (order by 1) rn from (select 1 from ten, ten, ten, ten, ten, ten limit 10000)));

Example which has a wrong count

select distinct 
  count(distinct vc_col) over ()
from t;

In this case the query returns a count higher than the correct value 11.

Workaround

Since normal COUNT(DISTINCT) is not affected by the problem, it is possible to exchange the analytic function by a scalar subquery in this case.

select distinct 
  (select count(distinct vc_col) from t)
from t;

Fix

The query will return the correct count.