Join on JSON_EXTRACT output returns internal server error

Detail name	Value
Changelog Number	12220
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

Description

Joins on output of JSON_EXTRACT columns lead to an internal server error if both sides of the join contain the output of JSON_EXTRACT.

Preparation:

create or replace table raw_data (json_col varchar(10000));
insert into raw_data values ('{"id": 1, "name": "the_thing", "widgets": [{"name": "widget_1"}, {"name": "widget_2"}]}');
create or replace view v1 as select json_extract(json_col, '$.id', '$.name') emits (id integer, name varchar(100)) from raw_data;

Example

select v1.id, v1.name, v2.name 
from v1 
join v1 as v2 on v1.id = v2.id;

Workaround

As a workaround, you can materialize the views using (CREATE TABLE AS).

Fix

The query will not return an internal server error, as expected.