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)

Reply via email to