IN filter may be slow with implicit integer type conversion

Detail name	Value
Changelog Number	14793
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.11, Exasol 8.0.3
Resolution Date	2022-06-09

Description

In the following scenario, filters using an IN clause may be slow:

• Query contains a column of type varchar
• This column contains many large values
• This column is compared using an IN clause
• The values in the IN clause are constant integer values

Preparation

create or replace table test.t(varchar_column varchar(100));
create or replace table test.ten as values 1,2,3,4,5,6,7,8,9,0 as v(t);
insert into test.t (select power(2, 64) from test.ten,test.ten,test.ten,test.ten,test.ten,test.ten);

Example

select * from test.t where varchar_column IN (1,2,3);

Workaround

Avoid implicit type conversion by adjusting the constant values to the correct data type (in this case, varchar).

select * from test.t where varchar_column IN ('1', '2', '3'); 

Fix

In such cases, the performance of implicit type conversion is improved.

However, comparisons between the same data types are recommended for best performance.