Using certain analytic functions as denominator leads to inaccurate data types

Detail name	Value
Changelog Number	7714
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.dev5
Fix Versions	Exasol 6.2.0, Exasol 6.1.3
Resolution Date	2019-04-16

Background

If a query contains a division that uses certain analytic functions (e.g., ROW_NUMBER, COUNT, DENSE_RANK, Rank) as denominator, the type deduction in Exasol determines the result type decimal instead of double. This can cause an out of range error ('data exception - numeric value out of range') if the deducted data type is too small for the division result.
Additionally, Exasol rounds the results and thus behaves differently compared to other analytic functions like MAX.
Example:

CREATE SCHEMA TEST;
CREATE OR REPLACE TABLE small (col1 INT);
INSERT INTO small VALUES 1,2;
-- Result: 1 | 1
SELECT 1 / ROW_NUMBER() OVER (ORDER BY col1) FROM small;

-- Result: 1 | 0.5
SELECT 1 / MAX(col1) OVER (ORDER BY col1) FROM small;

Workaround

Cast the denominator to the desired data type.
Example:

SELECT 1 / cast(ROW_NUMBER() OVER (ORDER BY col1) as double) FROM small;

Changed behavior