Bug #61983 strange explain plan results
Submitted: 26 Jul 2011 12:13 Modified: 27 Sep 2011 23:15
Reporter: luo longjiu Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.48 OS:Any
Assigned to: CPU Architecture:Any

[26 Jul 2011 12:13] luo longjiu
Description:
    in the explain plan output,i discover the type column value is 'ALL', but the key column is not 'NULL':

sql:
select t1.*
  from test_bbs t1,
       (select id
          from test_bbs 
         where g_id = 1
         order by replay_time) t2
 where t2.id = t1.id; 

How to repeat:
mysql> explain select t1.*
    ->   from test_bbs t1,
    ->        (select id
    ->           from test_bbs 
    ->          where g_id = 1
    ->          order by replay_time) t2
    ->  where t2.id = t1.id;
+----+-------------+------------+--------+----------------+----------------+---------+-------+------+----------------+
| id | select_type | table      | type   | possible_keys  | key            | key_len | ref   | rows | Extra          |
+----+-------------+------------+--------+----------------+----------------+---------+-------+------+----------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL           | NULL           | NULL    | NULL  |    3 |                |
|  1 | PRIMARY     | t1         | eq_ref | PRIMARY        | PRIMARY        | 8       | t2.id |    1 |                |
|  2 | DERIVED     | test_bbs   | ALL    | ind_test_bbs_g | ind_test_bbs_g | 5       |       |   14 | Using filesort |
+----+-------------+------------+--------+----------------+----------------+---------+-------+------+----------------+
3 rows in set (0.01 sec)

id=2:
|  2 | DERIVED     | test_bbs   | ALL    | ind_test_bbs_g | ind_test_bbs_g | 5       |       |   14 | Using filesort|

From the explain plan output,the type column is ALL,but the key column is not NULL, makes me feel confused!
[27 Jul 2011 13:21] Valeriy Kravchuk
Please, check if the same problem happens with a newer version, 5.1.58. If it does, please, provide complete repeatable test case, with CREATE TABLE and INSERTs to get minimal set of data to demonstrate the problem.
[27 Aug 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[28 Sep 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".