Internal server error for cached queries with scalar subqueries in table operator

Detail name	Value
Changelog Number	10647
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.rc2
Fix Versions	Exasol 7.0.0, Exasol 6.2.11
Resolution Date	2020-09-11

Description

An internal server error might occur for a query that contains a table operator (UNION, MINUS, EXCEPT, INTERSECTS) with a scalar subquery in one of the operands.
The error only occurs when the result of the query is not computed, but taken from the query cache.

Example

drop schema if exists repme cascade;
create schema repme;

create or replace table minus_base(i int);
create or replace table scalar_subsel(ssel1 int);
create or replace table from_table(x int);

-- This query puts the resultset into the query cache
SELECT * FROM minus_base MINUS 
SELECT
    min(CASE WHEN (SELECT ssel1 FROM scalar_subsel) THEN 1 END) AS COL
FROM from_table;


-- This query returns an internal server error because the result is read from the cache
SELECT * FROM minus_base MINUS 
SELECT
    min(CASE WHEN (SELECT ssel1 FROM scalar_subsel) THEN 1 END) AS COL
FROM from_table;

 

Workaround

There are two workarounds which will disable the caching of the query.

1. Without modifying the query, you can disable the query cache using the ALTER SESSION statement, as shown below:

ALTER SESSION SET QUERY_CACHE='OFF';
SELECT * FROM minus_base MINUS 
SELECT
    min(CASE WHEN (SELECT ssel1 FROM scalar_subsel) THEN 1 END) AS COL
FROM from_table;
ALTER SESSION SET QUERY_CACHE='ON';

2. The query text can be modified in a way that it can no longer be cached. To do this, we just add an expression that doesn't change the result to any WHERE clause. In this case we use the CURRENT_TIMESTAMP-literal. This prevents the caching of the query.

SELECT * FROM minus_base WHERE CURRENT_TIMESTAMP=CURRENT_TIMESTAMP MINUS 
SELECT
    min(CASE WHEN (SELECT ssel1 FROM scalar_subsel) THEN 1 END) AS COL
FROM from_table;

Fix

The query will be executed successfully without returning an internal server error when the result is being accessed from the query cache