[MDEV-326] EXPLAIN+ORDER BY: Different plans produced by SHOW EXPLAIN and EXPLAIN Created: 2012-06-08  Updated: 2016-04-18  Resolved: 2016-04-18

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.10
Fix Version/s: 10.2.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 al1.a FROM t1 AS al1, t1 AS al2 
WHERE al1.a = al2.a HAVING al1.a != 3 ORDER BY al1.a;

the plan produced by SHOW EXPLAIN contains 'Using where; Using index' in the Extra field, while normal EXPLAIN only says 'Using index'.

bzr version-info

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

Reproducible with Aria and InnoDB, with the default optimizer_switch.

SHOW EXPLAIN:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	al1	index	a	a	4	NULL	98	Using where; Using index
 
1	SIMPLE	al2	ref	a	a	4	test.al1.a	11	Using index

EXPLAIN:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	al1	index	a	a	4	NULL	8	Using index
 
1	SIMPLE	al2	ref	a	a	4	test.al1.a	11	Using index

Test case:

CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=Aria;
 
INSERT INTO t1 VALUES (4),(8),(0),(0),(0),(7),(7),(5),
 
(3),(188),(4),(9),(6),(1),(5),(6),(2),(4),(231),(4),
 
(3),(3),(7),(6),(7),(9),(4),(4),(2),(1),(2),(194),(2),
 
(3),(8),(4),(9),(4),(5),(5),(9),(3),(8),(0),(98),(3),
 
(1),(0),(189),(8),(3),(3),(9),(6),(8),(3),(9),(5),(9),
 
(2),(2),(5),(8),(6),(9),(0),(3),(6),(5),(8),(2),(120),
 
(25),(1),(3),(1),(3),(153),(5),(9),(1),(8),(7),(6),(2),
 
(4),(7),(3),(8),(4),(6),(1),(7),(1),(7),(2),(1),(5);
 
 
 
--connect (con1,localhost,root,,)
 
let $con_id = `SELECT CONNECTION_ID()`;
 
 
 
EXPLAIN
 
SELECT al1.a FROM t1 AS al1, t1 AS al2
 
WHERE al1.a = al2.a HAVING al1.a != 3 ORDER BY al1.a;
 
 
 
--send
 
SELECT al1.a FROM t1 AS al1, t1 AS al2
 
WHERE al1.a = al2.a HAVING al1.a != 3 ORDER BY al1.a;
 
 
 
--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 = 5;
 
while ($pass)
 
{
 
  --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
 
  --eval SHOW EXPLAIN FOR $con_id
 
  --dec $pass
 
  if ($mysql_errno)
 
  {
 
    let $pass = 0;
 
  }
 
}
 
 
 
--connection con1
 
--disable_result_log
 
--reap
 
 



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

With latest 5.5-show-explain, I was able to replicate with these sync commands:

set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';

EXPLAIN:

 
+------+-------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref        | rows | Extra       |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+-------------+
 
|    1 | SIMPLE      | al1   | index | a             | a    | 4       | NULL       |    8 | Using index |
 
|    1 | SIMPLE      | al2   | ref   | a             | a    | 4       | j326.al1.a |   11 | Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+-------------+

SHOW EXPLAIN: 

+------+-------------+-------+-------+---------------+------+---------+------------+------+-----------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref        | rows | Extra                       |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+-----------------------------+
 
|    1 | SIMPLE      | al1   | index | a             | a    | 4       | NULL       |   98 | Using index; Using filesort |
 
|    1 | SIMPLE      | al2   | ref   | a             | a    | 4       | j326.al1.a |   11 | Using index                 |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+-----------------------------+

Comment by Sergei Petrunia [ 2012-06-19 ]

As one can see, I don't get the "Using where" vs "Using where; Using index" difference.

However, I get these differences:

  • SHOW EXPLAIN shows "using filesort", EXPLAIN doesn't
  • SHOW EXPLAIN has #rows=98 for table al1, while EXPLAIN has 8.
Comment by Sergei Petrunia [ 2012-06-19 ]

The original testcase also has rows=8 vs rows=98 difference.

Table al1 is accessed with type='index', there is no LIMIT clause. This means, we will scan the whole index. There are 98 rows in the table, so SHOW EXPLAIN shows the correct value, while EXPLAIN shows the wrong one.

Comment by Sergei Petrunia [ 2012-06-19 ]

The query hits these lines in test_if_skip_sort_order():

if (tab->join->select_options & SELECT_DESCRIBE)

{ tab->ref.key= -1; tab->ref.key_parts= 0; if (select_limit < table->file->stats.records) tab->limit= select_limit; }

At this point, select_limit==8. The value of 8 was obtained in
test_if_cheaper_ordering() by dividing number of records in the first table
(98) by the fanout of the second table (11).

This could make some sense if we did a GROUP BY operation, but the query has
ORDER BY clause, not GROUP BY.

The query has HAVING (which doesn't seem to make much sense). If I remove the
HAVING clause, EXPLAIN is the same with rows=8, so HAVING is not the cause of
the problem.

Comment by Sergei Petrunia [ 2012-07-25 ]

Regarding the "Using where" part:

  • it refers to the condition "al1.a !=3", which comes from the HAVING clause.
  • This is surprising, because generally, the optimizer doesn't move parts of HAVING into the WHERE.
  • However, in the case where sorting is provided by use of index, and there is no grouping, JOIN::exec() has a piece of code starting with

Item* sort_table_cond= make_cond_for_table(...

which will take the HAVING and attach it to the first JOIN_TAB, effectively putting it into the WHERE.

For this particular example, it is correct that SHOW EXPLAIN shows "Using where", if you catch it during the query execution.
It is incorrect that SHOW EXPLAIN does NOT show "Using where" if you catch it at the point where it is entering JOIN::exec(). This is another instance of late-changes-in-query-plan which are hard to catch.

Comment by Elena Stepanova [ 2014-04-13 ]

On the current 10.0 tree (10.0.10+) I'm not getting 8 vs 98 difference; but on the other hand, there is no 'Using where' in SHOW EXPLAIN, so, if what the previous comment suggests it's true and it should be there, there's still a bug:

EXPLAIN:

+------+-------------+-------+-------+---------------+------+---------+------------+------+----------+-------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref        | rows | filtered | Extra       |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+----------+-------------+
 
|    1 | SIMPLE      | al1   | index | a             | a    | 4       | NULL       |   98 |   100.00 | Using index |
 
|    1 | SIMPLE      | al2   | ref   | a             | a    | 4       | test.al1.a |   11 |   100.00 | Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+----------+-------------+

SHOW EXPLAIN:

+------+-------------+-------+-------+---------------+------+---------+------------+------+-----------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref        | rows | Extra                       |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+-----------------------------+
 
|    1 | SIMPLE      | al1   | index | a             | a    | 4       | NULL       |   98 | Using index; Using filesort |
 
|    1 | SIMPLE      | al2   | ref   | a             | a    | 4       | test.al1.a |   11 | Using index                 |
 
+------+-------------+-------+-------+---------------+------+---------+------------+------+-----------------------------+

(If it's not a bug, please close).

Comment by Sergei Petrunia [ 2016-04-18 ]

The issue is not repeatable after MDEV-8646 plus its fixes.
This is expected, the code structure was changed so this no longer happens.

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