Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.2.2
-
None
-
None
Description
This example highlights a query optimization bug where two identical queries produce different results based on the table's engine type. In the first query, with ENGINE=MyISAM, the query returns c6 = 0, while in the second query, after recreating the table without specifying the engine, the result is c6 = NULL. This discrepancy is likely related to how MySQL handles JSON functions (JSON_ARRAYAGG and JSON_OBJECTAGG) and the HAVING clause with NULLIF. The difference in behavior between ENGINE=MyISAM and other engines may be due to how NULL values and JSON data are processed during query execution, especially when combined with conditional aggregation in the HAVING clause.
DROP DATABASE IF EXISTS test1; |
CREATE DATABASE test1; |
USE test1; |
|
|
|
|
-- query1
|
CREATE TABLE t0(c0 BOOLEAN , c1 REAL ) engine=MyISAM; |
INSERT INTO t0 VALUES (false, -544676116); |
|
|
|
|
|
|
SELECT JSON_ARRAYAGG(1 ) ^ JSON_OBJECTAGG( FROM_UNIXTIME( tom6.c0 , '%Y-%m-%d %H:%i:%s' ) >>1, 'A' ) AS c6 |
FROM t0 AS tom6 |
GROUP BY tom6.c1 |
HAVING NULLIF( 1 ,c6 ) ; |
|
|
|
|
-- query2
|
|
|
drop table t0; |
CREATE TABLE t0(c0 BOOLEAN,c1 REAL); |
INSERT INTO t0 VALUES (false, -544676116); |
|
|
|
|
|
|
|
|
SELECT JSON_ARRAYAGG( 1 ) ^ JSON_OBJECTAGG( FROM_UNIXTIME( tom6.c0 , '%Y-%m-%d %H:%i:%s' ) >>1 , 'A' ) AS c6 |
FROM t0 AS tom6 |
GROUP BY tom6.c1 |
HAVING NULLIF( 1 , c6 ) ; |
|
|
-- output1
|
+------+ |
| c6 |
|
+------+ |
| 0 |
|
+------+ |
1 row in set, 4 warnings (0.00 sec) |
|
|
|
|
|
|
-- output2
|
+------+ |
| c6 |
|
+------+ |
| NULL | |
+------+ |
1 row in set, 4 warnings (0.01 sec) |
|
|
|
|
|