[MDEV-327] SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY' Created: 2012-06-08  Updated: 2012-06-19  Resolved: 2012-06-19

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 * FROM t1, ( SELECT * FROM t2 ) AS alias
 
WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b )

with the default optimizer_switch the usual EXPLAIN returns

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
 
3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)

while SHOW EXPLAIN produces

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
 
3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)

bzr version-info

revision-id: psergey@askmonty.org-20120607171922-w5d79pe7733jrh2r
 
date: 2012-06-07 21:19:22 +0400
 
revno: 3412

Reproducible with MyISAM, Aria, InnoDB. The test case contains Aria because with MyISAM it is less reliable.

Test case:

CREATE TABLE t1 (a INT) ENGINE=Aria;
 
INSERT INTO t1 VALUES
 
(4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
 
(1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
 
 
 
CREATE TABLE t2 (b INT) ENGINE=Aria;
 
INSERT INTO t2 VALUES
 
(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
 
(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
 
 
 
--connect (con1,localhost,root,,)
 
let $con_id = `SELECT CONNECTION_ID()`;
 
 
 
EXPLAIN 
SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
 
WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
 
 
 
--send
 
SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
 
WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = 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-11 ]

There is an interesting difference: 

MariaDB [j2]> explain extended SELECT * FROM t1,  t2 AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where                                     |
 
|    1 | PRIMARY     | alias | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using join buffer (flat, BNL join)              |
 
|    2 | SUBQUERY    | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 |                                                 |
 
|    2 | SUBQUERY    | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
4 rows in set, 1 warning (0.00 sec)
 

MariaDB [j2]> explain extended SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where                                     |
 
|    1 | PRIMARY            | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using join buffer (flat, BNL join)              |
 
|    3 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 |                                                 |
 
|    3 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
 
4 rows in set, 1 warning (0.00 sec)

Comment by Sergei Petrunia [ 2012-06-19 ]

The difference is a bug in subquery handling. Filed it as https://bugs.launchpad.net/maria/+bug/1013343.

Comment by Sergei Petrunia [ 2012-06-19 ]

With fix for that bug, SHOW EXPLAIN and EXPLAIN outputs are no longer different, both show 'SUBQUERY'.

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