Bug #119581 FLOOR function shows inconsistent overflow handling

• Submitted: 20 Dec 14:54
• Modified: 22 Dec 19:15
• Reporter: mu mu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[20 Dec 14:54] mu mu

Description:
FLOOR() function shows inconsistent behavior when handling integer overflow:
- With column input: correctly raises ERROR 1690
- With constant input: silently truncates with only Warning 1292

How to repeat:
1. Create test table:
CREATE DATABASE IF NOT EXISTS test_bug;
USE test_bug;
CREATE TABLE test_floor (d DOUBLE);
INSERT INTO test_floor VALUES (9223372036854775808.0);

2. Test with column input:
mysql> SELECT CAST(FLOOR(d) AS SIGNED) FROM test_floor;
ERROR 1690 (22003): BIGINT value is out of range in 'floor(`test_bug`.`test_floor`.`d`)'

3. Test with constant input:
mysql> SELECT CAST(FLOOR(9223372036854775808.0) AS SIGNED);
+----------------------------------------------+
| CAST(FLOOR(9223372036854775808.0) AS SIGNED) |
+----------------------------------------------+
|                          9223372036854775807 |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '9223372036854775808' |
+-------+------+----------------------------------------------------------+

[22 Dec 19:15] Roy Lyseng

Verified as a bug.
However, the problem is not related to constant values vs. column values,
but rather to DECIMAL vs. DOUBLE values.
FLOOR against a DECIMAL value may cause a warning,
whereas FLOOR against a DOUBLE value may cause an exception.