Invalid range window in analytic functions

Detail name	Value
Changelog Number	19944
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 8.29.0, Exasol 7.1.29
Resolution Date	2024-07-12

Description

When an analytic function has the range window with boundaries of larger type than type of ORDER BY expression, than boundaries are cast to that type and it may produce invalid window.

Preparation

create schema s;

create table t1(x decimal(1,0));
insert into t1 values (1);
insert into t1 values (2);

Example

-- Observed/Expected: | 1 |
--                    | 2 |
select listagg(x,', ') over(order by x range between 0.5 preceding and current row) from t1;

-- Expected:   | 1    |
--             | 2    |
--
-- Observed:   | 1   |
--             | 1, 2|
select listagg(x,', ') over(order by x range between 0.51 preceding and current row) from t1;

Workaround

Cast ORDER BY expression to a type larger than the range boundaries types.

-- Observed/Expected: | 1 |
--                    | 2 |
select listagg(x,', ') over(order by cast(x as decimal(3,2)) range between 0.51 preceding and current row) from t1;

Fix

Query runs as expected.

Changed behavior