BETWEEN SYMMETRIC is treated as normal BETWEEN on columns with an index

Detail name	Value
Changelog Number	12227
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 6.2.15, Exasol 7.0.10
Resolution Date	2021-05-25

Background

BETWEEN syntax evaluates the conditions "A" and "B" in the order they are written.

select * from tab where tab.x between A and B

If A > B the query resturns no results.

BETWEEN SYMMETRIC instead does not enforce an order of the expressions.

Description

If the following conditions are met, BETWEEN SYMMETRIC is treated as BETWEEN

• query contains BETWEEN SYMMETRIC
• an index is used to evaluate the expressions

Preparation

create schema s;
create table t (i int);
insert into t values (1),(2),(3),(4);

Example showing the correct behaviour (correctly returns two rows)

select t.i from t where t.i between symmetric 3 and 2;

Example showing the wrong behaviour (no row is returned)

enforce local index on t(i);

select t.i from t where t.i between symmetric 3 and 2;

Fix

BETWEEN SYMMETRIC will work correctly regardless of the presence of an index.