Improvement of Optimizer for Between Joins

Detail name	Value
Changelog Number	5364
Type	Improvement
Status	Resolved
Fix Versions	Exasol 6.1.0
Resolution Date	2019-06-27

Problem Description

For some queries that contain BETWEEN joins the optimizer might not find a good execution plan.

Example

Given the following setup:

CREATE SCHEMA S;
CREATE TABLE T1(A1 INT, B1 INT);
CREATE TABLE T2(A2 INT, B2 INT);
CREATE TABLE T3(A3 INT, B3 INT);
INSERT INTO T1 VALUES (1,2),(2,3),(3,4),(4,5),(5,6);
INSERT INTO T2 VALUES (1,2),(2,4),(3,4),(4,4),(5,3);
INSERT INTO T3 VALUES (1,2),(5,6),(6,7),(7,8),(8,9);

The following join produces only a single row of data:

SELECT *
FROM T2
JOIN T3
  ON T2.B2 BETWEEN T3.A3 AND T3.B3;

When embedding this join into a larger query, the optimizer will currently always prefer joins on equality conditions, delaying this filtering between join:

SELECT *
FROM T1 
JOIN T2
  ON A1=B2
JOIN T3
  ON B2 BETWEEN A3 AND B3;

Workaround

As a workaround, it might be useful to enforce the materialization of a subselect that contains the filtering BETWEEN join(s). See SOL-581 for further information on how to enforce the materialization of a subselect.

The example from above could be rewritten as

SELECT *
FROM T1
JOIN (
    SELECT *
    FROM T2
    JOIN T3
      ON B2 BETWEEN A3 AND B3
    ORDER BY FALSE
  )
  ON A1=B2;