Bug #119313 BETWEEN clause returns incorrect results when comparing numeric literal with DATETIME columns

• Submitted: 6 Nov 3:03
• Reporter: Alice Alice
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any
• Tags: between, comparison, datetime, type conversion

[6 Nov 3:03] Alice Alice

Description:
When using a numeric literal in a BETWEEN clause to compare with DATETIME columns, MySQL returns incorrect results due to improper type conversion/comparison.

mysql> CREATE TABLE tb3_test1 (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     c28 DATETIME(6),
    ->     c29 DATETIME(6)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO tb3_test1 (c28, c29) VALUES ('2025-01-01 00:00:01.000000', '2025-01-01 00:00:00.001000');
Query OK, 1 row affected (0.00 sec)
mysql> select c28,c29 from tb3_test1 as tb3  where 20250101000000.000000 between tb3.c29 and tb3.c28 order by 1;
+----------------------------+----------------------------+
| c28                        | c29                        |
+----------------------------+----------------------------+
| 2025-01-01 00:00:01.000000 | 2025-01-01 00:00:00.001000 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE tb3_test1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    c28 DATETIME(6),
    c29 DATETIME(6)
);

INSERT INTO tb3_test1 (c28, c29) 
VALUES ('2025-01-01 00:00:01.000000', '2025-01-01 00:00:00.001000');

SELECT c28, c29 
FROM tb3_test1 AS tb3  
WHERE 20250101000000.000000 BETWEEN tb3.c29 AND tb3.c28 
ORDER BY 1;

Expected result:
Empty result set (0 rows), because the numeric value 20250101000000.000000 represents '2025-01-01 00:00:00.000000', which is NOT between c29 ('2025-01-01 00:00:00.001000') and c28 ('2025-01-01 00:00:01.000000').