LISTAGG with DISTINCT may cause an Internal Server Error

Detail name	Value
Changelog Number	20165
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 8.29.0, Exasol 7.1.29
Resolution Date	2024-07-12

Description

A query with the analytic function LISTAGG may lead to an “Internal Server Error” if the following conditions are met:

• The query contains LISTAGG.
• LISTAGG contains DISTINCT.
• There is at least one partition for which the arguments to LISTAGG consist only of NULL values.

Preparation

CREATE SCHEMA test;
CREATE OR REPLACE TABLE t(partitionCol INT, argumentCol VARCHAR(100) DEFAULT NULL);
INSERT INTO t VALUES (0, 'abcdefg');
INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (1, NULL);

Example

SELECT LISTAGG(DISTINCT argumentCol) OVER(PARTITION BY partitionCol) FROM t;

Workaround

Temporary replace NULL with a VARCHAR:

SELECT
CASE
  LISTAGG(DISTINCT NVL(argumentCol,'<NULL>')) OVER(PARTITION BY partitionCol)
WHEN '<NULL>' THEN NULL ELSE
  LISTAGG(DISTINCT NVL(argumentCol,'<NULL>')) OVER(PARTITION BY partitionCol)
END
FROM t;

Fix

Those queries run as expected.