Improved capabilities for scientific format casts

Detail name	Value
Changelog Number	15357
Type	Improvement
Status	Resolved
Fix Versions	Exasol 8.6.0
Resolution Date	2022-10-12

Background

Numbers can be represented in scientific format like 1.345e17, where "17" is called the exponent in this example.

Description

Previously, when casting such numbers from string to decimal or double, a "Numeric Value out of Range" exception was generated for any exponent that was:

• equal or larger than 32768 (unsigned); even when the actual result should be zero or when the exponent is followed by invalid characters
• larger than the target precision; even when the actual number was zero

Improvement

Casting such numbers from string to decimal or double is now supported. Specifically:

• Exponents can be arbitrarily large
• Too large negative exponents result in a zero value
• Too large positive exponents result in an exception, only when the numerical value is not zero.

Examples

SELECT CAST(... AS DECIMAL(18,0))

Cast to DECIMAL(18,0):

String	Result Old	Result New
'0e18'	0	0
'0e19'	Error "Numeric Value out of Range"	0
'0e99999'	Error "Numeric Value out of Range"	0
'0e99999 hello'	Error "Numeric Value out of Range"	Error "invalid character"
'1e99999'	Error "Numeric Value out of Range"	Error "Numeric Value out of Range"
'1e99999 hello'	Error "Numeric Value out of Range"	Error "invalid character"
'1e-99999'	Error "Numeric Value out of Range"	0
'-1e99999'	Error "Numeric Value out of Range"	Error "Numeric Value out of Range"

Changed behavior