MySQL Bugs: #14493: DESCRIBE viewname shows wrong result

Bug #14493	DESCRIBE viewname shows wrong result
Submitted:	30 Oct 2005 20:04	Modified:	1 Nov 2005 21:10
Reporter:	MySQL-Front Team	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.15/BK source 5.0	OS:	Windows (Windows XP/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
DESCRIBE `neuerview`;

- reports no "auto_increment" flag for the `Id` field
- reports wrong "Default" value (0) for the `Id` field
- reports empty "Default" value for the `Sort` field
- reports no values in `Key` column

Why is the results of DESCRIBE `neuerview` different from the result of DESCRIBE `cms_content` since `neuerview` is defined as the result of `cms_content`?

How to repeat:
CREATE TABLE `cms_content` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `PageId` int(10) unsigned NOT NULL default '0',
  `Sort` varchar(4) NOT NULL default 'a',
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `PageId_Sort` (`PageId`,`Sort`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE VIEW `test`.`test_view` AS SELECT * FROM `cms_content`;

DESCRIBE `cms_content`;

DESCRIBE `neuerview`;

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `cms_content` (
    ->   `Id` int(10) unsigned NOT NULL auto_increment,
    ->   `PageId` int(10) unsigned NOT NULL default '0',
    ->   `Sort` varchar(4) NOT NULL default 'a',
    ->   PRIMARY KEY  (`Id`),
    ->   UNIQUE KEY `PageId_Sort` (`PageId`,`Sort`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE VIEW `test`.`test_view` AS SELECT * FROM `cms_content`;
Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE `cms_content`;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| Id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| PageId | int(10) unsigned | NO   | MUL | 0       |                |
| Sort   | varchar(4)       | NO   |     | a       |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> DESCRIBE `test_view`;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Id     | int(10) unsigned | NO   |     | 0       |       |
| PageId | int(10) unsigned | NO   |     | 0       |       |
| Sort   | varchar(4)       | NO   |     |         |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

I think we should handle this a S3 (Non-critical) severty since the database reports wrong results.

Duplicate of bug #4607