Usage of MUL in analytic functions with ORDER BY or DISTINCT produces incorrect results for DECIMAL datatypes with scale.

Detail name	Value
Changelog Number	29440
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0, Exasol 2025.1.0, Exasol 2025.2.0
Fix Versions	Exasol 2026.1.0, Exasol 2025.1.11
Resolution Date	2026-05-15

Description

Usage of MUL in a analytic functions with ORDER BY or DISTINCT produced incorrect results for DECIMAL data types with scale.

Example:

CREATE OR REPLACE TABLE testmul (category VARCHAR(10), nums DECIMAL(10,2));
INSERT INTO testmul VALUES 
  ('A', 1.10), 
  ('A', 2.10), 
  ('A', 3.21), 
  ('B', 1.10), 
  ('B', 2.10);

/*
Observed: 5 rows (Wrong results in mul_per_category)

CATEGORY|NUMS|MUL_PER_CATEGORY|
--------+----+----------------+
A       |1.10|           110.0|
A       |3.21|       7415100.0|
A       |2.10|         23100.0|
B       |1.10|           110.0|
B       |2.10|         23100.0|

Expected: 5 rows (Correct results in mul_per_category)

CATEGORY|NUMS|MUL_PER_CATEGORY|
--------+----+----------------+
A       |1.10|           1.100|
A       |3.21|           7.415|
A       |2.10|           2.310|
B       |1.10|           1.100|
B       |2.10|           2.310|
*/
SELECT
    category,
    nums,
    MUL(nums) OVER (PARTITION BY category ORDER BY nums) AS mul_per_category
FROM testmul;

/*
Observed: 5 rows (Wrong results in mul_distinct_all)

CATEGORY|MUL_DISTINCT_ALL|
--------+----------------+
A       |       7415100.0|
A       |       7415100.0|
A       |       7415100.0|
B       |       7415100.0|
B       |       7415100.0|

Expected: 5 rows (Correct results in mul_distinct_all)

CATEGORY|MUL_DISTINCT_ALL|
--------+----------------+
A       |           7.415|
A       |           7.415|
A       |           7.415|
B       |           7.415|
B       |           7.415|
*/
SELECT
  category,
  MUL(DISTINCT nums) OVER () AS mul_distinct_all
FROM testmul;

Workaround

Cast the decimal datatype to double

SELECT
    category,
    nums,
    MUL(CAST (nums as double)) OVER (PARTITION BY category ORDER BY nums) AS mul_per_category
FROM testmul;

Fix

MUL now returns correct results for DECIMAL data types with scale when used in analytic functions with ORDER BY or DISTINCT.