Subquery elimination changes WHERE condition evaluation order

Detail name	Value
Changelog Number	10469
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.beta1, Exasol 7.0.rc1, Exasol 7.0.beta2
Fix Versions	Exasol 7.0.0, Exasol 7.0.rc2, Exasol 6.2.15
Resolution Date	2020-09-11

Description

For performance improvements, the optimizer will attempt to eliminate subqueries under certain conditions. When eliminating subqueries, the optimizer changes the WHERE condition evaluation order. This may cause WHERE conditions from the outer SELECT to be evaluated before the WHERE conditions from the inner SELECT.

Example

SELECT * FROM
(
    SELECT CAST(a AS INT) AS a_number
    FROM t
    WHERE IS_NUMBER(a)
)
WHERE a_number = 123;

The query above will be executed as:

SELECT CAST(a AS INT) AS a_number
FROM t
WHERE CAST(a AS INT) = 123 AND IS_NUMBER(a);

Then CAST(a AS INT) gets evaluated before IS_NUMBER(a) which might cause a data exception.

Workaround

For the example above we recommend adding an additional check in the SELECT list:

SELECT * FROM
(
    SELECT CASE WHEN IS_NUMBER(a) THEN CAST(a AS INT) END AS a_number -- workaround
    FROM t
    WHERE IS_NUMBER(a)
)
WHERE a_number = 123;

In other cases, you can materialize the inner subquery by adding an ORDER BY to the inner SELECT. This will force the WHERE conditions to be evaluated in the proper order:

SELECT * FROM
(
    SELECT CAST(a AS INT) AS a_number
    FROM t
    WHERE IS_NUMBER(a)
    ORDER BY 1 -- workaround
)
WHERE a_number = 123;

Fix

The WHERE conditions will be evaluated in the proper order.

Changed behavior

For queries with subqueries the WHERE condition evaluation order may change.