MySQL Bugs: #10189: SHOW CREATE VIEW shows the select

Bug #10189	SHOW CREATE VIEW shows the select_statement in lower case
Submitted:	26 Apr 2005 21:52	Modified:	8 Nov 2005 14:13
Reporter:	MySQL-Front Team	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	5.0.4-beta/BK source 5.0	OS:	Any (*)
Assigned to:		CPU Architecture:	Any

Description:
Like mentioned in the manual the SHOW CREATE VIEW query shows the "select" in the select_statement in lowercase:

SHOW CREATE VIEW view_name;
generates:
CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b`

Why is the "select" in lower case,

All other generated SQL keywords will be returned in higher case.

For example the returned source of a stored procedure will be return a higher case "SELECT":
http://dev.mysql.com/doc/mysql/en/create-procedure.html

How to repeat:
http://dev.mysql.com/doc/mysql/en/show-create-view.html

Suggested fix:
Return the "select" as "SELECT".

Hi,

Just the way it is right now. I'll leave it open, it might get picked up later or change.

Regards,
Geert

Not only the keyword "SELECT" will be shown in lower case - all keywords will be shown in lower case.

While fixing this problem it would be nice to have some <newline> chars in the result.

Maybe before each new clausel like FROM, WHERE, ORDER BY, GROUP BY.

This would follow the SHOW CREATE TABLE query, where each new column, key get's his own line.

PS
Whould be nice if somebody could fix this problems after 6 month waiting... ;-)

Verified on Linux.

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

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

mysql> CREATE TABLE tb (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE VIEW tbv AS SELECT id AS myid FROM tb WHERE id > 5;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE VIEW  tbv\G
*************************** 1. row ***************************
       View: tbv
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW 
`tbv` AS select `tb`.`id` AS `myid` from `tb` where (`tb`.`id` > 5)
1 row in set (0.01 sec)

mysql>

Please take a look to

SHOW CREATE PROCEDURE query.

The result of the body is uppercased.

Additional the body - like the attributes of the procedure - gets his own lines.

Please compare the results of
- SHOW CREATE VIEW
- SHOW CREATE PROCEDURE
- SHOW CREATE FUNCTION

... would be nice if the format would be equal... ;-)

Btw:
The SHOW CREATE PROCEDURE query uses 4 spaces after starting a new line.

The SHOW CREATE TABLE query uses only 2 spaces for this.

... also this could be more equal... ;-)

The developers are not interessed about our bug messages.