[MDEV-325] SHOW EXPLAIN: Plan produced by SHOW EXPLAIN is different from standard EXPLAIN: type ALL vs 'index_merge', keys is different, 'Using sort_union(a,b);' is missing Created: 2012-06-07  Updated: 2012-11-04  Resolved: 2012-07-27

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.0

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-165 MWL#182: Explain running statements: ... Closed

 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



 Comments   
Comment by Sergei Petrunia [ 2012-06-08 ]

All of these issues and BUG#992942... Seems like detailed study of ORDER BY processing is un-avoidable.

Comment by Sergei Petrunia [ 2012-06-22 ]

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.

Comment by Sergei Petrunia [ 2012-07-27 ]

Has been pushed some time ago into 5.5-show-explain-testing1, now considering it closed.

Generated at Thu Feb 08 06:27:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.