MySQL and MariaDB – casting a string representing decimal value with many leading zeros into numeric value gives an unexpected result
When casting a string representing decimal value with many leading zeros into numeric value MySQL and MariaDB gives an unexpected result – with enabled strict mode Out of range value error happens, and with disabled strict mode Out of range value warning is shown and the result is clamped to maximal decimal value with given precision. For example:
SELECT CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01 AS DECIMAL(15,2)) as val; gives 9999999999999.99
But actually it is not value that is out of range, it is just value with many leading zeros! Why it work like this? In MS SQL and PostgreSQL this doesn’t happen
When we came into it we investigated this behaviour, the bug happens with disabled strict mode when input string is longer than 83 symbols. 83 limit is related to the DECIMAL_MAX_STR_LENGTH constant in MySQL C source code, when input string is longer than that constant, the resulted value is set to maximal decimal value with given precision. The bug is present in MySQL 8.0 and in MariaDB 10.4.13 and maybe in theirs older versions also.
This could be dangerous in some cases, for example in online financial services that use MySQL. A malicious persons could use this bug for getting big amount of money on its account by entering sum with many leading zeros.
We reported this bug to MySQL developers, they answered that it is
a known behaviour. Simply, digital data type has its limit. This particular limit is not too well described, in particular the part of whet happens if you make an overflow.
They decided not to fix this, but added its description into their documentation.
Also we reported to MariaDB, they keep silence
