JSON_EXTRACT and JSON_VALUE raise conversion exceptions for certain DECIMAL types regardless of the specified error behavior

Detail name	Value
Changelog Number	12923
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0
Fix Versions	Exasol 7.0.14, Exasol 7.1.3
Resolution Date	2021-11-26

Background

Exasol supports the JSON functions JSON_EXTRACT and JSON_VALUE which also support native error handling.

Description

In the following conditions, the query returns a "data exception - numeric value out of range" error:

• Query contains a JSON_EXTRACT or JSON_VALUE function
• The error behavior of the JSON function is not ERROR ON ERROR
• The function returns or emits a certain decimal type (eg. DECIMAL(19,0))

Example

SELECT JSON_EXTRACT ('{"id":11111111111111111111}', '$.id null on error') EMITS(id decimal(19,0));

SELECT JSON_VALUE('{"id":11111111111111111111}', '$.id' RETURNING decimal(19,0) null on error);

Workaround

Use the decimal types DECIMAL(18,0) or DECIMAL(36,0) instead.

Example

SELECT JSON_EXTRACT ('{"id":11111111111111111111}', '$.id null on error') EMITS(id decimal(18,0));

SELECT JSON_VALUE('{"id":11111111111111111111}', '$.id' RETURNING decimal(18,0)); 

Fix

JSON_EXTRACT and JSON_VALUE will only raise conversion exceptions if the error behavior is ERROR ON ERROR.