DELETE with IN predicate containing expressions in WHERE clause may fail on empty tables

Detail name	Value
Changelog Number	13930
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0
Fix Versions	Exasol 8.0.0, Exasol 7.1.7
Resolution Date	2022-03-16

Description

A DELETE statement containing expressions in the WHERE clause in combination with an IN predicate may fail if the referenced table in the IN clause is empty.

Preparation

create table t1 (a int, b int);
create table t2 (a int, b int);

Examples for successfull executions

-- Successful execution
DELETE FROM t1 WHERE (a,a) IN (SELECT a+1,a FROM t2);

-- Successful execution
DELETE FROM t1 WHERE (a,a) IN (SELECT a,a FROM t2);

Examples for executions returning errors

-- Error: [Code: 0, SQL State: 40005]  Successfully reconnected after internal server error, transaction was rolled back.
DELETE FROM t1 WHERE (a+1,a) IN (SELECT b-1,b FROM t2);

-- Error: [Code: 0, SQL State: 40005]  Successfully reconnected after internal server error, transaction was rolled back.
DELETE FROM t1 WHERE (a+1,a) IN (SELECT a+1,a FROM t2);

-- Error: [Code: 0, SQL State: 40005]  Successfully reconnected after internal server error, transaction was rolled back.
DELETE FROM t1 WHERE (a+1,a) IN (SELECT a,a FROM t2);

Workaround

Rewrite the delete statement to avoid the expression in the IN predicate.

Example

DELETE FROM t1 WHERE (a+1,a) IN (SELECT b,b FROM t2);
-- rewrite to
DELETE FROM t1 WHERE (a,a) IN (SELECT b-1,b FROM t2);

Fix

In such cases DELETE statements finish successfully.