Details
Major
Description
Take the attached dataset and run this query:
explain select distinct count(*) from t2 where a between 10 and 20 group by a; +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+ | 1 | SIMPLE | t2 | range | a | a | 5 | NULL | 10 | Using where; Using index; Distinct; Using temporary; Using filesort | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+
and see an apparently non-sensical output (how can a table use "Distinct" if it
is the only table in the join?)
This is repeatable on 10.0.17, didn't check 5.5
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
On 5.3 and 5.5, it looks like this:
elenst@wheezy-64:~/git/5.5$ ml test -e "explain select distinct count(*) from t2 where a between 10 and 20 group by a;" +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+ | 1 | SIMPLE | t2 | range | a | a | 5 | NULL | 10 | Using where; Using index; Using temporary; Using filesort; Distinct | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+
Debugging on 10.0, looking at this line of code in JOIN::save_explain_data_intern:
if (distinct & test_all_bits(used_tables, join->select_list_used_tables))select_list_used_tables==0 when we come here... which is weird, JOIN::optimize_inner does call JOIN::eval_select_list_used_tables which should have put a correct value there.
in eval_select_list_used_tables we have:
One can debate what (count
)->used_tables() should return, but this is not the point.
The point is, if we run
then it is obvious that Distinct optimization is not applicable.
Short-circuiting logic applies when running
but not when one runs
(unless maybe some cases with Loose Scan).