DISTINCT with JSON_VALUE, IS JSON, or IS NOT JSON returns an error

Detail name	Value
Changelog Number	11139
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.3
Fix Versions	Exasol 7.0.4
Resolution Date	2020-11-26

Description

Beginning with 7.0.3, queries using DISTINCT in combination with JSON_VALUE, IS JSON, or IS NOT JSON raise the following exception:

[42000] not a valid GROUP BY expression

Preparation

create schema spot11139;
create table t(a varchar(20));
insert into t values '[1,2,3]', '[4,5,6]', '[7,8,3]';

Example

select distinct JSON_VALUE(a, '$[2]') from t; -- [42000] not a valid GROUP BY expression
select distinct a is json from t; -- [42000] not a valid GROUP BY expression
select distinct a is not json from t; -- [42000] not a valid GROUP BY expression

Workaround

You can rewrite the query using a subselect and move the DISTINCT to the outer query:

select distinct * from (select JSON_VALUE(a, '$[2]') from t);
select distinct * from (select a is json from t);
select distinct * from (select a is not json from t);

Fix

Queries using DISTINCT in combination with JSON_VALUE, IS JSON, or IS NOT JSON returns the expected results without error.