| Bug #5500 | EXPLAIN for a view differs from EXPLAIN for a base table | ||
|---|---|---|---|
| Submitted: | 10 Sep 2004 4:00 | Modified: | 25 Sep 2006 2:20 |
| Reporter: | Peter Gulutzan | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.2-alpha | OS: | Linux (SuSE 8.2) |
| Assigned to: | Bugs System | Target Version: | |
[19 Aug 2005 12:13]
Victoria Reznichenko
Verified with 5.0.12-beta-debug-log
[6 Sep 2006 17: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 10:22]
Georgi Kodinov
Pushed into 5.0.26/5.1.12-beta
[25 Sep 2006 2:20]
Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs.
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)