Wrong result in query with various BETWEEN and BETWEEN SYMMETRIC predicates

lukaseder
SQL-Fighter

Here's a fun bug:

I'm using Exasol 7.0.9:

 

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

select t.i
from t
where (
  t.i between 2 and 3
  and t.i between symmetric 2 and 3
  and t.i between symmetric 3 and 2
)
order by t.i;

 

This produces no results. The correct result is produced if any of the following changes are made:

  • The primary key is removed
  • Any of the 3 predicate parts is removed
  • Any of the predicates where 2 and 3 are in order is changed to 2 + 0 and 3 (this change doesn't work if the reverse order between predicate is changed)
1 ACCEPTED SOLUTION

exa-SimonR
Team Exasol
Team Exasol

As always, thanks for the bug report!

I can reproduce the problem and created a fix. The fix still needs to go through QA, but I'm optimistic to get it into 7.0.10. The fix will be included in the upcoming versions 7.0.10 and 6.2.15. The corresponding ticket is EXASOL-2895.

The reason for this problem is that we treat BETWEEN SYMMETRIC like a normal BETWEEN during an index scan. PRIMARY KEY creates an index on the column. Non primary key columns that have an index are also affected:

 

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

-- correctly returns two rows
select t.i from t where t.i between symmetric 3 and 2;

enforce local index on t(i);

-- no row is returned
select t.i from t where t.i between symmetric 3 and 2;

 

 

 

 

View solution in original post

3 REPLIES 3

exa-SimonR
Team Exasol
Team Exasol

As always, thanks for the bug report!

I can reproduce the problem and created a fix. The fix still needs to go through QA, but I'm optimistic to get it into 7.0.10. The fix will be included in the upcoming versions 7.0.10 and 6.2.15. The corresponding ticket is EXASOL-2895.

The reason for this problem is that we treat BETWEEN SYMMETRIC like a normal BETWEEN during an index scan. PRIMARY KEY creates an index on the column. Non primary key columns that have an index are also affected:

 

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

-- correctly returns two rows
select t.i from t where t.i between symmetric 3 and 2;

enforce local index on t(i);

-- no row is returned
select t.i from t where t.i between symmetric 3 and 2;

 

 

 

 

View solution in original post

mwellbro
Xpert

A bit off-topic and very unfortunately only in German: https://www.youtube.com/watch?v=Dtzr7Y_fdhw
"Between 3 and 5" and "Between 5 and 3" - he even mentions the SYMMETRIC syntax at the end ( which I honestly had never heard of before @lukaseder´s post here 🙂 )

lukaseder
SQL-Fighter

Yes, it's not very widely used - especially not in the form I've mentioned. A typical usage is when an interval is stored in columns A and B, and then a third value is compared with those columns, without knowing which one of A or B is the lower / upper bound. Though, even then, most people will probably order the bounds prior to insertion.