Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.2.2
-
None
-
None
Description
This example demonstrates a query optimization bug where two structurally identical queries produce different results due to internal handling of time functions and the bitwise NOT (~) operator. In both queries, the MAX() and MIN() functions are applied along with the ~ operator and DATE_ADD(). However, the first query outputs a different value for the bitwise NOT result (c2) than the second query. The discrepancy likely stems from the fact that the first table is created with ENGINE=MyISAM, while the second table uses the default engine (typically InnoDB). The difference in storage engines impacts how MySQL processes time values and internal conversions, especially when performing bitwise operations on time-based data. Despite the queries being identical, the internal handling of DATE_ADD() and the bitwise NOT operation by different storage engines results in inconsistent outcomes.
DROP DATABASE IF EXISTS test1; |
CREATE DATABASE test1; |
USE test1; |
|
|
-- query1
|
CREATE TABLE t0(c0 double)engine=MyISAM ; |
SELECT MAX( X'68656C6C6F' ) AS c0 , MIN( '14:30:00' ) AS c1 , ~( DATE_ADD( '2025-12-31' , INTERVAL 1 SECOND ) ) AS c2 FROM t0 AS tom0 ; |
|
|
|
|
|
|
-- query2
|
drop table t0; |
CREATE TABLE t0(c0 double); |
SELECT MAX( X'68656C6C6F' ) AS c0 , MIN( '14:30:00' ) AS c1 , ~( DATE_ADD( '2025-12-31' , INTERVAL 1 SECOND ) ) AS c2 FROM t0 AS tom0 ; |
|
|
|
|
|
-- output1
|
|
|
+------------+------+----------------------+ |
| c0 | c1 | c2 |
|
+------------+------+----------------------+ |
| NULL | NULL | 18446723822478551614 | |
+------------+------+----------------------+ |
1 row in set (0.00 sec) |
|
|
|
|
-- output2
|
|
|
mysql> SELECT MAX( X'68656C6C6F' ) AS c0 , MIN( '14:30:00' ) AS c1 , ~( DATE_ADD( '2025-12-31' , INTERVAL 1 SECOND ) ) AS c2 FROM t0 AS tom0 ; |
+------------+------+---------------------+ |
| c0 | c1 | c2 |
|
+------------+------+---------------------+ |
| NULL | NULL | 9223372036854775807 | |
+------------+------+---------------------+ |
1 row in set (0.00 sec) |
|
|
|