MySQL Bugs: #13109: Views are not stored with original formatting

Bug #13109	Views are not stored with original formatting
Submitted:	11 Sep 2005 4:44	Modified:	3 Oct 2008 21:10
Reporter:	Tom Khoury	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.12	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When views are saved in the database, they are re-parsed into a format that is very hard to read.  While saving views and stored procedures, it is important that the database preserves all of the the line breaks and indentations that are used by developers to make their objects more readable when referenced at a later time.  The functionality is there, but this could become a serious usability problem.

How to repeat:
Create a view on any table using multiple lines and indenting certain lines and save the view.  When editing the view later on, it all appears as a single line making the view very difficult for a developer to read and determine what it contains.

Suggested fix:
When saving views and stored procedures, preserve all line breaks, indentations (spaces at the beginning of lines) and comments so that they can be clearly read by another developer or dba.

Yes, you are right, your "specific formatting" is not preserved:

mysql> create view v1
    -> as
    ->   select *
    ->     from table_1;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table v1;
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------+
| View | Create View
                                                           |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`table_1`.`Number` AS `Number`,`test`.`table_1`.`hint` AS `hint`,`test`.`table_1`.`id` AS `id`,`test`.`table_1`.`day` AS `day` from `test`.`table_1` |
+------+------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------+
1 row in set (0.02 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

But, first of all, current behaviour is not a (serious) bug. 

In case of view database engine can not just store 'select *', for example, bacause it needs to know exact number, types and names of columns without additional queries to the base tables. So, in any database engine (not only MySQL) DDL statements are not always stored exactly as you enter them.

You should save your statements in you own scripts, with any comments and indentation you like. Database stores them as it seemed efficient to its developers.

As for storing comments for tables, views, columns - it seems a reasonable feture request for me. At least, Oracle allows to do so. That it why I changed severity of your bug report.

Thank you, this is a duplicate of Bug#5159

Bug #48535 was marked as a duplicate of this one.