Bug #120362 Incorrect syntax for CREATE VIEW in docs

• Submitted: 28 Apr 6:13
• Modified: 28 Apr 9:37
• Reporter: Daniël van Eeden (OCA)
• Status: Verified
• Category: MySQL Server: Documentation
• Severity: S2 (Serious)
• Version: 9.7.0
• OS: Any
• CPU Architecture: Any
• Tags: create view, syntax, VIEW

[28 Apr 6:13] Daniël van Eeden

Description:
https://dev.mysql.com/doc/refman/9.7/en/create-view.html has this:

CREATE
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    [IF NOT EXISTS] VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

This suggests that the syntax should be:

CREATE IF NOT EXISTS VIEW v1 AS (SELECT 1);

However that is not accepted by the server:

mysql-9.7.0 [test]> CREATE IF NOT EXISTS VIEW v1 AS (SELECT 1);
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 'IF NOT EXISTS VIEW v1 AS (SELECT 1)' at line 1

This does work:

CREATE VIEW IF NOT EXISTS v1 AS (SELECT 1);

mysql-9.7.0 [test]> \W
Show warnings enabled.
mysql-9.7.0 [test]> CREATE VIEW IF NOT EXISTS v1 AS (SELECT 1);
Query OK, 0 rows affected (0.001 sec)

mysql-9.7.0 [test]> CREATE VIEW IF NOT EXISTS v1 AS (SELECT 1);
Query OK, 0 rows affected, 1 warning (0.002 sec)

Note (Code 1050): Table 'v1' already exists
mysql-9.7.0 [test]> DROP TABLE v1;
ERROR 1051 (42S02): Unknown table 'test.v1'
mysql-9.7.0 [test]> DROP VIEW v1;
Query OK, 0 rows affected (0.001 sec)

Side note: May the note should say this is a view instead of a table.

How to repeat:
Compare docs with accepted server syntax.

[28 Apr 8:32] MySQL Verification Team

Thanks for the report.
I believe "[IF NOT EXISTS] VIEW" should be "VIEW [IF NOT EXISTS]"

-- 
Shane, MySQL Senior Principal Technical Support Engineer
Oracle Corporation
http://dev.mysql.com/

[28 Apr 8:34] MySQL Verification Team

"May the note should say this is a view instead of a table"

please open a server bug for that feature request.

[28 Apr 9:37] Daniël van Eeden

>> "May the note should say this is a view instead of a table"
>>
> please open a server bug for that feature request.

Bug #120365