Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.10
-
None
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
|
|
|
Attachments
Issue Links
- relates to
-
MDEV-165 MWL#182: Explain running statements: finish coding, submit for review
-
- Closed
-
Activity
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.
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.
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.
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.
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).
The issue is not repeatable after MDEV-8646 plus its fixes.
This is expected, the code structure was changed so this no longer happens.
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 |+------+-------------+-------+-------+---------------+------+---------+------------+------+-----------------------------+