Bug #5500 EXPLAIN for a view differs from EXPLAIN for a base table
Submitted: 10 Sep 2004 2:00 Modified: 25 Sep 2006 0:20
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.2-alpha OS:Linux (SuSE 8.2)
Assigned to: Igor Babaev CPU Architecture:Any

[10 Sep 2004 2:00] Peter Gulutzan
Description:
An EXPLAIN of a select from a view is not the same 
as an EXPLAIN of a select from the original base table. 
The select_type changes from SIMPLE to PRIMARY. 
That's slightly confusing. 
 

How to repeat:
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.29 sec) 
 
mysql> create view v as select * from t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> explain select * from t; 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ 
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               | 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ 
|  1 | SIMPLE      | t     | system | NULL          | NULL | NULL    | NULL |    0 | const row not 
found | 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ 
1 row in set (0.00 sec) 
 
mysql> explain select * from v; 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ 
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               | 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ 
|  1 | PRIMARY     | t     | system | NULL          | NULL | NULL    | NULL |    0 | const row not 
found | 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ 
1 row in set (0.00 sec)
[19 Aug 2005 10:13] Victoria Reznichenko
Verified with 5.0.12-beta-debug-log
[6 Sep 2006 15:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11478

ChangeSet@1.2254, 2006-09-06 08:21:43-07:00, igor@rurik.mysql.com +8 -0
  Fixed bug #5500: EXPLAIN returned a wrong select_type for queries using views.
  Select_type in the EXPLAIN output for the query SELECT * FROM t1 was
  'SIMPLE', while for the query SELECT * FROM v1, where the view v1
  was defined as SELECT * FROM t1, the EXPLAIN output contained 'PRIMARY'
  for the select_type column.
[19 Sep 2006 8:22] Georgi Kodinov
Pushed into 5.0.26/5.1.12-beta
[25 Sep 2006 0:20] Paul Dubois
Noted in 5.0.26, 5.1.12 changelogs.