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
• Status: Closed
• 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] MySQL Verification Team

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, [e-mail] +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.