Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.2.2
-
None
-
None
Description
This example demonstrates a query optimization bug where two identical queries produce different results. In both queries, the CASE WHEN logic and time functions are used to calculate a value, but the results differ. The first query returns a row with c0 = 1, while the second query returns an empty set. This discrepancy is likely related to the fact that the first table is created with ENGINE=MyISAM, while the second table does not specify an engine (defaulting to InnoDB or another engine). The difference in storage engines affects how MySQL handles the CASE WHEN statement, the comparison of the TIME function, and the NULLIF operation. The internal handling of time values and the conditional expressions causes inconsistent behavior between the two otherwise identical queries, suggesting a bug in the optimizer's treatment of these operations based on the underlying storage engine.
DROP DATABASE IF EXISTS test1; |
CREATE DATABASE test1; |
USE test1; |
|
|
-- query1
|
|
|
CREATE OR REPLACE TABLE t0(c0 BOOLEAN , c1 REAL ) engine=MyISAM; |
INSERT INTO t0 VALUES (false, -544676116); |
|
|
SELECT 1 AS c0 |
FROM t0 AS tom0 |
WHERE IFNULL( CASE WHEN NULLIF( TIME( '2025-12-31 14:30:00' ) , 'A' ) >> tom0.c0 LIKE '%' THEN '2025-12-31' ELSE NULLIF( '2025-12-31' , '2025-12-31' ) END , 1) ^ DATE_SUB( '2025-12-31' , INTERVAL 1 DAY ) GROUP BY tom0.c1 ; |
|
|
|
|
-- query2
|
|
|
CREATE OR REPLACE TABLE t0(c0 BOOLEAN,c1 REAL); |
INSERT INTO t0 VALUES (false, -544676116); |
|
|
|
|
SELECT 1 AS c0 |
FROM t0 AS tom0 |
WHERE IFNULL( CASE WHEN NULLIF( TIME( '2025-12-31 14:30:00' ) , 'A' ) >> tom0.c0 LIKE '%' THEN '2025-12-31' ELSE NULLIF( '2025-12-31' , '2025-12-31' ) END , 1) ^ DATE_SUB( '2025-12-31' , INTERVAL 1 DAY ) GROUP BY tom0.c1 ; |
|
|
-- output1
|
+----+ |
| c0 |
|
+----+ |
| 1 |
|
+----+ |
1 row in set, 2 warnings (0.00 sec) |
|
|
|
|
|
|
|
|
-- output2
|
|
|
Empty set, 3 warnings (0.01 sec) |
|
|
|