DISTINCT on JSON_EXTRACT returns internal server error

Detail name	Value
Changelog Number	12544
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0
Fix Versions	Exasol 7.0.12, Exasol 7.1.1
Resolution Date	2021-08-31

Background

JSON_EXTRACT is based on the Exasol UDF framework and behaves like a SCALAR EMITS UDF. Thus, the same restrictions apply, one of which is that it cannot be used in a DISTINCT clause.

Description

Queries containing DISTINCT on a JSON_EXTRACT function return an internal server error.  The expected behavior is that an appropriate error message will be thrown, like with a DISTINCT on a SCALAR EMITS UDF.

Preparation:

create table raw_data (json_doc_col varchar(10000));
insert into raw_data values ('{"id": 1, "name": "foo", "props": [{"name": "p1"}, {"name": "p1"}]}');

Example

select distinct json_extract(json_doc_col, '$.id', '$.props#.name') emits (id integer, prop_name varchar(100)) 
from raw_data;

Workaround

Encapsulate the JSON_EXTRACT in a sub-select to use it with DISTINCT.

select distinct id, prop_name from 
(
select json_extract(json_doc_col, '$.id', '$.props#.name') emits (id integer, prop_name varchar(100)) 
from raw_data
);

Fix

An appropriate error message is thrown.