CAST of negative DOUBLE values to string might fail or return wrong result

Detail name	Value
Changelog Number	32498
Type	Bug
Status	Open
Affected Versions	Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0, Exasol 2026.1.0

Description

CAST of certain double values to string types of a specific length can fail or return the wrong result. This can happen only if the following conditions are met:

• The cast is from type DOUBLE to VARCHAR or from DOUBLE to CHAR
• The value is negative
• The VARCHAR or CHAR type is sized such that the representation of the double value does not fully fit into the (VAR)CHAR type.

Examples

-- Expected: "-124"
-- Observed: "-123.9" or "Internal Server Error"
SELECT CAST(CAST(-123.9 AS DOUBLE) AS VARCHAR(4) UTF8); 

-- Expected: "-77777778"
-- Observed: "Internal Server Error"
SELECT CAST(CAST(-77777777.7777777 AS DOUBLE) AS VARCHAR(9) ASCII);

Workaround

Avoid casting negative value doubles into VARCHAR or CHAR types that are not large enough to contain a string representation of the DOUBLE value preceding the decimal. Increasing the size of the (VAR)CHAR container will resolve the issue.

In the first example above, changing the cast to a VARCHAR(5) resolves the issue:

SELECT CAST(CAST(-123.9 AS DOUBLE) AS VARCHAR(6) UTF8); -- Returns correct result: "-123.9" (6 characters)

SELECT CAST(CAST(-77777777.7777777 AS DOUBLE) AS VARCHAR(11) ASCII); -- Returns correct result "-77777777.8"

Additional workarounds are:

• Casting first to DECIMAL,
• using ROUND(),
• or using TO_CHAR().

Fix

Casts, including those meeting the above criteria, are now represented correctly in the returned string type.