Scalar Subquery with Joins may cause Internal Server Error

Details

Detail name Value
Changelog Number 8650
Type Bug
Status Resolved
Affected Versions Exasol 6.2.0, Exasol 6.1.3
Fix Versions Exasol 6.1.7, Exasol 6.2.2
Resolution Date 2019-09-24

Problem

This problem only occurs if several conditions are met:

  1. The query needs a scalar subquery that contains a join.
  2. The result of this scalar subquery (1) is used as filter on a different join.
  3. It is possible to push down this filter to all tables in the join (2).

Example: 

CREATE SCHEMA test2;
CREATE OR REPLACE TABLE t1(col1 INT);
SELECT *
FROM
    (SELECT a.col1 from t1 a inner join t1 b on a.col1 = b.col1) c
LEFT JOIN
    (SELECT a.col1 from t1 a inner join t1 b on a.col1 = b.col1) d
ON
    c.col1 = (SELECT MAX(a.col1) FROM t1 a inner join t1 b on a.col1 = b.col1)
and d.col1 = (SELECT MAX(a.col1) FROM t1 a inner join t1 b on a.col1 = b.col1);

Workaround

This depends on the query. It is necessary to remove one of the three preconditions.