Queries with scalar subqueries inside ON or WHERE conditions might crash

Detail name	Value
Changelog Number	849
Type	Bug
Status	Resolved
Affected Versions
Fix Versions	Exasol 6.1.0
Resolution Date	2018-05-31

Problem

Some queries might lead to an internal server error if a scalar subquery is inside an ON or a WHERE condition. The problem might occur in the following cases:

• The subquery is used directly as a boolean expression. For instance, the following query crashes.

CREATE SCHEMA S;
CREATE OR REPLACE TABLE T0(C0 INT);
CREATE OR REPLACE TABLE T1(C1 BOOL);

SELECT * FROM T0 JOIN T1 
ON C1 = 1 AND (SELECT MAX(C1) FROM T1);

• The scalar subquery is contained in the WHERE clause and it is the argument of a function. For instance, the following query crashes.

CREATE SCHEMA S;
CREATE OR REPLACE TABLE T2 (A INT, B INT, D1 DATE);
CREATE OR REPLACE TABLE T3 (X INT, Y INT, D2 DATE);

SELECT * FROM T2 JOIN T3 ON A=X 
WHERE TO_DATE( (SELECT MAX(D1) FROM T2) ) = TO_DATE('2000-01-01');

• The result of the scalar subquery is part of an IN condition. For instance, the following query crashes.

CREATE SCHEMA S;
CREATE OR REPLACE TABLE T2 (A INT, B INT, D1 DATE);
CREATE OR REPLACE TABLE T3 (X INT, Y INT, D2 DATE);

SELECT * FROM T2 JOIN T3 ON A=X 
WHERE TO_DATE( (SELECT MAX(D1) FROM T2) ) IN ('2000-01-01');