Hi,
We've identified an issue with the ROUND(n, integer) function in Oracle Database 23ai Free (version 23.5.0.24.07).
According to the official documentation, ROUND(n, integer) rounds n to integer decimal places. If integer is negative, it rounds n to the left of the decimal point. Here’s the relevant part of the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/ROUND-number.html
However, we've obeserved that the rounding happens in the opposite direction when integer exceeds 2147483646 or is less than -2147483647 while testing this feature, which is not addressed in the documentation.
Moreover, this also violates the formula in the document, which should return a numeric overflow error when integer has abnormal values.
ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) * POWER(10, -integer)
Steps to Reproduce:
The issue can be reproduced with the following minimized query:
SELECT ROUND(123.456, 2147483647); -- 0x7fffffff
SELECT ROUND(123.456, -2147483648); -- 0x80000000
Expected Result:
123.456
0
For very large integer, rounding is negligible, so n is returned unchanged.
For very small integer, n is rounded to a very large decimal point, returning 0.
Otherwise, it should return a numeric overflow error:
SQL> SELECT FLOOR(123.456 * POWER(10, 2147483647) + 0.5) * POWER(10, -2147483647);
SELECT FLOOR(123.456 * POWER(10, 2147483647) + 0.5) * POWER(10, -2147483647)
*
ERROR at line 1:
ORA-01426: numeric overflow
Help: https://docs.oracle.com/error-help/db/ora-01426/
SQL> SELECT FLOOR(123.456 * POWER(10, -2147483648) + 0.5) * POWER(10, 2147483648);
SELECT FLOOR(123.456 * POWER(10, -2147483648) + 0.5) * POWER(10, 2147483648)
*
ERROR at line 1:
ORA-01426: numeric overflow
Help: https://docs.oracle.com/error-help/db/ora-01426/
Actual result (Oracle Database 23ai Free, version 23.5.0.24.07):
0
123.456
However, Oracle Database returns 0 for 2147483647, and 123.456 (original n) for -2147483648.
This appears to be an error due to the sign of integer being treated as its opposite, which may lead to security incidents. We suspect this is a signed integer overflow issue, with a threshold of 2147483647 (0x7fffffff) and -2147483648 (0x80000000).
We've attached some additional testcases.
We suggest updating Oracle Database to correctly evaluate ROUND() for all valid integer values or return an overflow error for out-of-range values, with the supported range specified in the documentation.
Thank you for your time and attention to this matter.
We look forward to your response.
Best regards.
Additional test cases
-- ROUND(n, integer)
-- integer > 0
-- 0x7ffffffd
SQL> SELECT ROUND(1234.5678, 2147483645);
1234.5678
-- 0x7ffffffe
SQL> SELECT ROUND(1234.5678, 2147483646);
1234.5678
-- 0x7fffffff
SQL> SELECT ROUND(1234.5678, 2147483647);
0
-- 0x80000000
SQL> SELECT ROUND(1234.5678, 2147483648);
0
-- integer < 0
-- 0x80000002
SELECT ROUND(1234.5678, -2147483646);
0
-- 0x80000001
SELECT ROUND(1234.5678, -2147483647);
0
-- 0x80000000
SELECT ROUND(1234.5678, -2147483648);
1234.5678
-- 0xffffffff`7fffffff
SELECT ROUND(1234.5678, -2147483649);
1234.5678