DML using system table within a subquery may cause exception

Detail name	Value
Changelog Number	14604
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.7
Fix Versions	Exasol 8.0.0, Exasol 7.1.12
Resolution Date	2022-04-20

Description

A DML statement may cause an exception "successfully reconnected after internal server error" if the following conditions are met

• a DML statement is used
• a subquery is used
• the subquery contains a system table
• autocommit is turned off (default is on)
• snapshot mode is used in mode system tables (default is system tables)

Example Query:

UPDATE T SET status = null 
WHERE EXISTS (SELECT * FROM EXA_METADATA WHERE EXA_METADATA.PARAM_NAME = 'databaseName');

Workaround

Temporarily deactivate the snapshot mode for such queries.

For example:

ALTER SESSION SET SNAPSHOT_MODE = 'OFF';

UPDATE T SET status = null 
WHERE EXISTS (SELECT * FROM EXA_METADATA WHERE EXA_METADATA.PARAM_NAME = 'databaseName');

ALTER SESSION SET SNAPSHOT_MODE = 'SYSTEM TABLES';

Fix

No error is raised in such scenarios.