Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.17, 5.3.13, 5.5(EOL), 10.1(EOL)
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
Attachments
Activity
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:
(gdb) p item$11 = (Item *) 0x7fffa4006618(gdb) p dbug_print_item( item)$12 = 0x1842700 "count(0)"(gdb) p item->used_tables()$13 = 0One can debate what (count
)->used_tables() should return, but this is not the point.
The point is, if we run
SELECT DISTINCT COUNT(*)then it is obvious that Distinct optimization is not applicable.
Short-circuiting logic applies when running
SELECT DISTINCT func(table_column)but not when one runs
SELECT DISTINCT aggregate_func.(unless maybe some cases with Loose Scan).