Yaoxuan Wu created FLINK-41594:
----------------------------------
Summary: CAST / TRY_CAST of a STRING to DECIMAL with precision <=
18 ignores one invalid character after the digits ('12a' -> 12, '1.5%' -> 1.5,
'a' -> 0)
Key: FLINK-41594
URL: https://issues.apache.org/jira/browse/FLINK-41594
Project: Flink
Issue Type: Bug
Components: Table SQL / Runtime
Reporter: Yaoxuan Wu
Casting a STRING to a DECIMAL with precision <= 18 accepts some invalid strings
and returns a number for them. It ignores the first invalid character after the
digits, and it also accepts strings with no digits at all. {{CAST}} returns a
value where it should fail, and {{TRY_CAST}} returns a value where it should
return NULL. A DECIMAL with precision > 18, INT and DOUBLE reject the same
strings.
{code:sql}
SELECT s, TRY_CAST(s AS DECIMAL(10, 2)), TRY_CAST(s AS DECIMAL(20, 2)),
TRY_CAST(s AS DOUBLE)
FROM (VALUES ('12'), ('12a'), ('1.5%'), ('1e2x'), ('a'), ('.'), ('a1')) AS t(s);
-- s DECIMAL(10, 2) DECIMAL(20, 2) DOUBLE
-- '12' 12.00 12.00 12.0
-- '12a' 12.00 NULL NULL <- expected NULL
-- '1.5%' 1.50 NULL NULL <- expected NULL
-- '1e2x' 100.00 NULL NULL <- expected NULL
-- 'a' 0.00 NULL NULL <- expected NULL
-- '.' 0.00 NULL NULL <- expected NULL
-- 'a1' NULL NULL NULL
SELECT CAST('a' AS DECIMAL(5, 0)); -- 0 (CAST('a' AS INT) throws
NumberFormatException)
SELECT CAST('1x' AS DECIMAL(5, 2)); -- 1.00
{code}
* Batch and streaming, literals and column values.
* Same on 2.0.1, 2.2.1, 2.3.0 and master (2.4-SNAPSHOT).
Cause, in {{BinaryStringDataUtil.toDecimalFromBytes}}, the path for precision
<= 18 (a larger precision goes through {{toBigPrecisionDecimal}}, which uses
BigDecimal):
* The significand and exponent loops read {{b = bytes[offset + i]; i++;}} and
{{break}} on a non-digit. At that point {{i}} is already past the invalid
character, so the following "invalid character at the end" loop starts after
it, and one invalid character is never checked. A second one is caught, which
is why 'a1' and '1x2' give NULL.
* Nothing checks that at least one digit was read, so 'a', '.' and '-.' parse
as 0.
A possible fix: after the loops, step back one position when they stopped on a
non-digit (or check {{b}} before incrementing {{i}}). Also return null when
{{significandLen == 0}}.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)