[MDEV-327] SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY' - JIRA

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 * 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

Gliffy Diagrams

Attachments

Issue Links

relates to

MDEV-165 MWL#182: Explain running statements: finish coding, submit for review

Closed

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Petrunia added a comment - 2012-06-11 00:26

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)

Show

Sergei Petrunia added a comment - 2012-06-11 00:26 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)

Hide

Permalink

Sergei Petrunia added a comment - 2012-06-19 12:44

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

Show

Sergei Petrunia added a comment - 2012-06-19 12:44 The difference is a bug in subquery handling. Filed it as https://bugs.launchpad.net/maria/+bug/1013343 .

Hide

Permalink

Sergei Petrunia added a comment - 2012-06-19 12:45

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

Show

Sergei Petrunia added a comment - 2012-06-19 12:45 With fix for that bug, SHOW EXPLAIN and EXPLAIN outputs are no longer different, both show 'SUBQUERY'.

SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Time Tracking

Agile