COUNT(DISTINCT) with OVER clause may return NULL instead of 0

Detail name	Value
Changelog Number	11568
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 6.2.14, Exasol 7.0.8
Resolution Date	2021-02-17

Description

COUNT (DISTINCT) with OVER clause returns NULL instead of 0 if the following conditions are met:

• The query contains a COUNT(DISTINCT) with an OVER clause
• The OVER clause contains a PARTITION BY clause
• Exactly one group contains exactly one NULL value

The query returns correct results, except that NULL is shown instead of 0 for the one group containing exactly one NULL value

Preparation:

create schema test;
create or replace table t (c int);
insert into t values (null);
insert into t values (1);

Example:

select c, count( distinct c) over (partition by c ) from t; 

Workaround

You can use the NVL function to convert NULL values to 0 in the results:

select c, nvl(count(distinct c) over (partition by c ),0) from t ; 

Fix

The query will return the expected results