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 based on the table's engine type. In the first query, with ENGINE=MyISAM, the query runs successfully and returns the expected result. In the second query, after recreating the table without specifying the engine, an error occurs: ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size. The discrepancy is likely caused by how the ORDER BY clause is handled differently when using the ENGINE=MyISAM, which may influence the query's memory usage and sorting behavior. This suggests an issue with how the optimizer manages sorting operations between different storage engines.
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 GROUP_CONCAT( 1 ) AS c0 , 1 AS c1 , MIN( ABS( b'101010' ) ) AS c3 FROM t0 AS tom0 GROUP BY tom0.c1 ORDER BY c0 ; |
|
|
|
|
|
|
|
|
-- query2
|
DROP TABLE t0; |
CREATE TABLE t0(c0 BOOLEAN,c1 REAL); |
INSERT INTO t0 VALUES (false, -544676116); |
|
|
SELECT GROUP_CONCAT( 1 ) AS c0 , 1 AS c1 , MIN( ABS( b'101010' ) )AS c3 FROM t0 AS tom0 GROUP BY tom0.c1 ORDER BY c0 ; |
|
|
|
-- output1
|
mysql> SELECT GROUP_CONCAT( 1 ) AS c0 , 1 AS c1 , MIN( ABS( b'101010' ) ) AS c3 FROM t0 AS tom0 GROUP BY tom0.c1 ORDER BY c0 ; |
+------+----+------+ |
| c0 | c1 | c3 |
|
+------+----+------+ |
| 1 | 1 | 42 |
|
+------+----+------+ |
1 row in set (0.00 sec) |
|
|
|
|
|
|
-- output2
|
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size |
|
|
|
|
|