Small exact numeric literals as input may cause wrong results when using AVG, STDDEV_*,VAR_*

Details

Detail name Value
Changelog Number 10681
Type Bug
Status Resolved
Affected Versions Exasol 6.2.0, Exasol 7.0.0
Fix Versions Exasol 7.0.4, Exasol 6.2.12
Resolution Date 2020-10-16

Description

Some analytic functions (AVG, STDDEV, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, VARIANCE) cause wrong results under the following conditions:

  • The input argument is an exact numeric literal.
  • The data type of the exact numeric literal is smaller than DECIMAL(19,0).
  • The OVER clause contains an ORDER BY.

Example: 

-- Preparation
CREATE OR REPLACE TABLE t(o int);
INSERT INTO t VALUES 1,2,3,4,5;

--Expected results: 1.0
SELECT AVG(1) OVER (ORDER BY o) FROM t;

Workaround

Cast the literal to DECIMAL(19,0) or a larger datatype.

Example: 

SELECT AVG(CAST(1 AS DECIMAL(19,0))) OVER (ORDER BY o) FROM t;

Fix

The above functions will produce the expected results