Wrong result for DOUBLE filtering for BETWEEN filter

Detail name	Value
Changelog Number	17330
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.17.0
Fix Versions	Exasol 8.20.0
Resolution Date	2023-07-12

Description

If the following conditions are met, queries return incorrect results:

• DOUBLE column type
• using BETWEEN filter on that column using FILTERSCAN (enabled by default for supported scenarios, can be found in profiling)
• upper bound of the filtering has to be smaller than the actual maximum value of the column

• comparing to a value of type DOUBLE (in case of constant it has to be casted)
• the filter condition cannot contain OR statement and cannot be applied to a JOIN

Preparation

create schema SCH;
CREATE OR REPLACE TABLE SCH.TEST(col DOUBLE);

insert into SCH.TEST (col) select -4.2 from values between 1 and 4194304;
insert into SCH.TEST (col) select 0 from values between 1 and 4194304;
insert into SCH.TEST (col) select 3.3 from values between 1 and 4194304;

Example

In the below example, the expected value is 4194304.

select count(col) from SCH.TEST where col >= cast(-10 as DOUBLE) and col <= cast(-2 as DOUBLE); -- returns wrong result 8388608

or

select count(col) from SCH.TEST where col between cast(-10 as DOUBLE) and cast(-2 as DOUBLE) ; -- returns wrong result 8388608

Workaround

Set the database parameter -useRawCompare=0. Unfortunately, this will lower the performance of some queries.

Fix

In these scenarios, queries will return the correct results, as expected.