Bug #10837 view with check option, server accepts syntactical wrong SQL

• Submitted: 24 May 2005 18:46
• Modified: 1 Jun 2005 15:27
• Reporter: Matthias Leich
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Assigned to: Mikael Ronström
• CPU Architecture: Any

[24 May 2005 18:46] Matthias Leich

Description:
CREATE TABLE t1 (f1 BIGINT);

The server accepts the following wrong SQL statement:
CREATE VIEW v1 AS SELECT * FROM t1 CASCADED WITH CHECK OPTION

This syntax is not allowed, neither by the MySQL manual nor by the
SQL standard.
The correct statement would be:
CREATE VIEW v1 AS SELECT * FROM t1 WITH CASCADED CHECK OPTION

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1903, 2005-05-24

How to repeat:
Please execute the statement above.

[1 Jun 2005 15:27] Mikael Ronström

CREATE VIEW v1 AS SELECT * FROM t1 CASCADED WITH CHECK OPTION
is a perfectly ok syntax that means
CREATE VIEW v1
AS
SELECT * from t1 CASCADED
WITH CHECK OPTION

Thus CASCADED here is an alias for the table name t1, completely useless in this particular
query but still an allowed syntax.

If one wants to avoid this problem then CASCADED cannot be used at all as an identifer.