Wrong results for filters on DECIMAL columns with only positive values

Detail name	Value
Changelog Number	15281
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.6
Fix Versions	Exasol 8.0.7, Exasol 7.1.13
Resolution Date	2022-08-01

Description

Filters on a column produce wrong results if the following conditions are met:

• Column is of type DECIMAL..
• Column is not of type DECIMAL(9,?), DECIMAL(18,?), DECIMAL(36,?).
• The column contains only positive values.
• The conditional filter compares the column with a negative value.

This problem only occurs if the column contains enough values. The number of values required to trigger the problem depends on the DBRAM setting and the number of nodes.

Preparation

-- Single node, DBRAM=50 GIB
create or replace table test.t(x decimal(10,2), y decimal(10,2));
insert into test.t select -70, range_value from values between 0 and 4194303;
insert into test.t select -80, range_value from values between 0 and 4194303;
insert into test.t select -60, range_value from values between 0 and 4194303;

Example

-- Expected result: 0
select count(x) from test.t where y < -1;
-- Actual result: 12582912

Workaround

Use a scalar subselect.

select count(x) from test.t where y < (SELECT -1);

Fix

Those queries return the correct result.