Multiplication With Certain Analytic Functions leads to Invalid Data Types

Detail name	Value
Changelog Number	7698
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 multiplication that involves certain analytic functions (e.g., ROW_NUMBER, COUNT), the type deduction in Exasol determines the wrong result type. 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 multiplication result.
Example:

CREATE SCHEMA test;
OPEN SCHEMA test;

CREATE TABLE t(col1 int);
INSERT INTO t VALUES 1,2,3,4,5,6,7,8,9;

SELECT 2 * ROW_NUMBER() OVER(ORDER BY col1) FROM t;

Workaround

Cast the constant expression to a larger data type.
Example:

SELECT CAST(2 AS DECIMAL(18,0)) * ROW_NUMBER() OVER(order by col1) from t;