Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.0
-
Component/s: None
-
Labels:None
Description
For the following query
SELECT a+SLEEP(0.01) FROM t1 WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 ORDER BY b;
standard EXPLAIN returns
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort
SHOW EXPLAIN sometimes produces the same in the beginning, but then switches to
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a,b NULL NULL NULL 30 Using where; Using filesort
bzr version-info
revision-id: psergey@askmonty.org-20120607081906-rkc1r1wyc9e0n1hj date: 2012-06-07 12:19:06 +0400 revno: 3409
Reproducible with MyISAM and Aria, with the default optimizer_switch.
Test case:
CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b)) ENGINE=MyISAM;
INSERT INTO t1 VALUES
(8,0),(128,5050),(5372,8),(234,7596),(2,0),(2907,8930),(1,0),
(0,5224),(8,7638),(960,5),(9872,1534),(0,2295),(3408,9809),
(7,0),(1168,0),(2089,5570),(0,205),(88,1018),(0,26528),
(0,0),(4,5567),(1444,145),(6,0),(1,7535),(7793,534),(70,9),
(178,1),(44,5),(189,0),(3,0);
SELECT a+SLEEP(0.01) FROM t1
WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
ORDER BY b;
--connect (con1,localhost,root,,)
let $con_id = `SELECT CONNECTION_ID()`;
EXPLAIN
SELECT a+SLEEP(0.01) FROM t1
WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
ORDER BY b;
--send
SELECT a+SLEEP(0.01) FROM t1
WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
ORDER BY b;
--connection default
let $fail = 100;
while ($fail)
{
--error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
--eval SHOW EXPLAIN FOR $con_id
--dec $fail
if (!$mysql_errno)
{
let $fail = 0;
}
}
let $pass = 1;
while ($pass)
{
--error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
--eval SHOW EXPLAIN FOR $con_id
if ($mysql_errno)
{
let $pass = 0;
}
}
--connection con1
--disable_result_log
--reap
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-165 MWL#182: Explain running statements: finish coding, submit for review
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The problem is caused by these lines in create_sort_index():
if (select)
{ /* We need to preserve tablesort's output resultset here, because QUICK_INDEX_MERGE_SELECT::~QUICK_INDEX_MERGE_SELECT (called by SQL_SELECT::cleanup()) may free it assuming it's the result of the quick select operation that we no longer need. Note that all the other parts of this data structure are cleaned up when QUICK_INDEX_MERGE_SELECT::get_next encounters end of data, so the next SQL_SELECT::cleanup() call changes sort.io_cache alone. */ IO_CACHE *tablesort_result_cache; tablesort_result_cache= table->sort.io_cache; table->sort.io_cache= NULL; select->cleanup(); // filesort did select table->quick_keys.clear_all(); // as far as we cleanup select->quick table->intersect_keys.clear_all(); table->sort.io_cache= tablesort_result_cache; }Here, it destroys the quick select.
This is the same place where quick select is destroyed for BUG#992942.
Has been pushed some time ago into 5.5-show-explain-testing1, now considering it closed.
All of these issues and BUG#992942... Seems like detailed study of ORDER BY processing is un-avoidable.