MySQL Bugs: #14506: `INFORMATION_SCHEMA`.`COLUMNS`.`COLUMN

Bug #14506	`INFORMATION_SCHEMA`.`COLUMNS`.`COLUMN_COMMENT` is allways emtpy
Submitted:	31 Oct 2005 10:05	Modified:	31 Oct 2005 11:22
Reporter:	MySQL-Front Team	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I think it should contain the column comment (from the CREATE TABLE query) or <NULL> if the column is inside a VIEW.

Like I know it's not possible to give a comment to a VIEW field. Because of this <NULL> is better than <empty>

How to repeat:
SELECT `COLUMN_COMMENT` FROM `information_schema`.`COLUMNS`;

Thank you for a feature request. For me it looks like column comment propagates to view (both on 5.0.15-nt on Windows and on 5.0.16-BK on Linux):

mysql> create table tc (c1 int comment 'useful column');
Query OK, 0 rows affected (0,01 sec)

mysql> select column_name, column_comment from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tc';
+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| c1          | useful column  |
+-------------+----------------+
1 row in set (0,01 sec)

mysql> create view vc as select * from tc;
Query OK, 0 rows affected (0,00 sec)

mysql> select column_name, column_comment from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'vc';
+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| c1          | useful column  |
+-------------+----------------+
1 row in set (0,01 sec)

mysql> show create table tc;
+-------+-----------------------------------------------------------------------
-----------------------------------------+
| Table | Create Table
                                         |
+-------+-----------------------------------------------------------------------
-----------------------------------------+
| tc    | CREATE TABLE `tc` (
  `c1` int(11) default NULL COMMENT 'useful column'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-----------------------------------------+
1 row in set (0,00 sec)

mysql> show create view vc;
+------+------------------------------------------------------------------------
----------------------------------------------------+
| View | Create View
                                                    |
+------+------------------------------------------------------------------------
----------------------------------------------------+
| vc   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vc` AS select `tc`.`c1` AS `c1` from `tc` |
+------+------------------------------------------------------------------------
----------------------------------------------------+
1 row in set (0,00 sec)

mysql> select table_name, column_name, column_comment from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'vc';
+------------+-------------+----------------+
| table_name | column_name | column_comment |
+------------+-------------+----------------+
| vc         | c1          | useful column  |
+------------+-------------+----------------+
1 row in set (0,01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16    |
+-----------+
1 row in set (0,01 sec)

Explicit column comment for views are unsupported (http://dev.mysql.com/doc/refman/5.0/en/create-view.html):

mysql> create view vc2 (c2 comment 'I am in view') as select * from tc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'comment 'I am in view') as select * from tc' at line 1

You may add a separate feature request for that. 

But exisiting comments are used, even if view columns have different names:

mysql> create view vc2 (c2) as select * from tc;
Query OK, 0 rows affected (0,00 sec)

mysql> select table_name, column_name, column_comment from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'vc2';
+------------+-------------+----------------+
| table_name | column_name | column_comment |
+------------+-------------+----------------+
| vc2        | c2          | useful column  |
+------------+-------------+----------------+
1 row in set (0,02 sec)

I'm sorry, you are right: There is no bug!

Thanks a lot for your comments about view column comments!