Wrong results when converting single digit string into DECIMAL(x,x)

Detail name	Value
Changelog Number	32309
Type	Bug
Status	Resolved
Affected Versions	Exasol 8.6.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0
Fix Versions	Exasol 2026.1.2
Resolution Date	2026-09-14

Description

In rare cases, converting a single-digit string other than zero (for example, '1') to a DECIMAL whose precision equals its scale (for example, DECIMAL(1,1)) could produce an incorrect result.

Variant 1:

• The CPU does not support the BMI2 instruction-set. BMI2 instruction-set extension was introduced with Intel’s Haswell CPUs in 2013.
• The query contains a single-digit number string other than zero ('1', ‘2', ‘3’, ‘4’, ‘5’, ‘6’, ‘7’, ‘8’, ‘9’, '-1', ‘-2', ‘-3’, ‘-4’, ‘-5’, ‘-6’, ‘-7’, ‘-8’, ‘-9’).
• The query or optimizer casts the string to a DECIMAL whose precision equals its scale (for example, DECIMAL(1,1), DECIMAL(9,9)).

Variant 2:

• The query contains a negative single-digit number string other than zero ('-1', ‘-2', ‘-3’, ‘-4’, ‘-5’, ‘-6’, ‘-7’, ‘-8’, ‘-9’).
• The query or optimizer casts the string to a DECIMAL whose precision equals its scale (for example, DECIMAL(1,1), DECIMAL(9,9)).

Example

-- Variant 1 (CPU does not support the BMI2 instruction-set), example 1
-- Observed: true
-- Expected: false
select json_value ('{"x": "1"}', '$.x' returning decimal(1,1) default 0.5 on error) = 1;

-- Variant 1 (CPU does not support the BMI2 instruction-set), example 2
-- Observed: exception "data exception - numeric value out of range: value 1 (internal number representation) is not in [ -0.9 .. 0.9 ]"
-- Expected: query finishes with error written into ERROR_COLUMN
select JSON_EXTRACT('{"x": "1"}', '$.x', '$.error()')
       EMITS (d1_1 decimal(1,1), error_column varchar(2000000))
from dual;

-- Variant 2:
-- Observed: false
-- Expected: true
select '-1' < CAST(0 AS DECIMAL(9,9));

Workaround

• When using virtual machines make sure that BMI2 is activated.
• On SQL level workaround is to use decimal with larger precision, for example:

select json_value ('{"x": "1"}', '$.x' returning decimal(2,1) default 0.5 on error) = 1;

select JSON_EXTRACT('{"x": "1"}', '$.x', '$.error()')
       EMITS (d2_1 decimal(2,1), error_column varchar(2000000))
from dual;

select '-1' < CAST(0 AS DECIMAL(10,9));

Fix

Query runs as expected.