[MDEV-300] SHOW EXPLAIN: select_type returned by SHOW EXPLAIN is different from the normal EXPLAIN (SIMPLE vs PRIMARY) Created: 2012-05-30  Updated: 2012-06-04  Resolved: 2012-06-04

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

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 0
Labels: None

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

 Description    

EXPLAIN SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 
Warnings:
 
Note    1249    Select 2 was reduced during optimization
 
SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );
 

SHOW EXPLAIN FOR 2;
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    2       Using where
 
Warnings:
 
Note    1003    SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) )

bzr version-info

revision-id: psergey@askmonty.org-20120524182239-gq6jtnnovydnthtl
 
date: 2012-05-24 22:22:39 +0400
 
revno: 3405

Reproducible with the default optimizer_switch as well as with all OFF values (except for in_to_exists which is required to execute the query).
Reproducible with MyISAM, Aria, InnoDB.

Test case:

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
--connect (con1,localhost,root,,)
 
let $con_id = `SELECT CONNECTION_ID()`;
 
 
 
EXPLAIN SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );
 
send SELECT a FROM t1 WHERE ( SELECT SLEEP(0.5) );
 
 
 
--connection default
 
let $run = 100;
 
while ($run)
 
{
 
  --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
 
  --eval SHOW EXPLAIN FOR $con_id
 
  --dec $run
 
  if (!$mysql_errno)
 
  {
 
    let $run = 0;
 
  }
 
}
 
 
 
--connection con1
 
--reap



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

This is a known property, as documented here: http://kb.askmonty.org/en/show-explain/

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