Hi,
We found an overflow in the TRUNC(n1, n2) function in Oracle Database 23ai Free (version 23.5.0.24.07).
According to the official documentation, TRUNC(n1, n2) returns n1 truncated to n2 decimal places. It takes any numeric data type as an argument.
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TRUNC-number.html
However, we've obeserved that the truncating happens in the opposite direction when n2 exceeds 2147483646 or is less than -2147483647, which is not addressed in the documentation.
Steps to Reproduce:
The issue can be reproduced with the following minimized query:
SELECT TRUNC(123.456, 2147483647); -- 0x7fffffff
SELECT TRUNC(123.456, -2147483648); -- 0x80000000
Expected Result:
123.456
0
For very large n2, truncating is negligible, so n1 is returned unchanged.
For very small n2, n1 is truncated to a very large decimal point, returning 0.
Actual result (Oracle Database 23ai Free, version 23.5.0.24.07):
SQL> SELECT TRUNC(123.456, 2147483646);
TRUNC(123.456,2147483646)
------------------------
123.456
SQL> SELECT TRUNC(123.456, 2147483647);
TRUNC(123.456,2147483647)
-------------------------
0
SQL> SELECT TRUNC(123.456, -2147483647);
TRUNC(123.456,-2147483647)
--------------------------
0
SQL> SELECT TRUNC(123.456, -2147483648);
TRUNC(123.456,-2147483648)
--------------------------
123.456
However, Oracle Database returns 0 for 2147483647, and 123.456 (original n1) for -2147483648.
This appears to be an error due to the sign of n2 being treated as its opposite.
We suggest updating Oracle Database to correctly evaluate TRUNC() for all valid n2 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.