Bug #99721 The syntax of creating procedure is not compatible with the old version (8.0.16)

• Submitted: 28 May 2020 11:10
• Modified: 28 May 2020 12:55
• Reporter: lyp tennyson
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 8.0.18
• OS: Any
• CPU Architecture: Any

[28 May 2020 11:10] lyp tennyson

Description:
The syntax of creating procedure is not compatible with the old version (8.0.16)

How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> delimiter ##
mysql>
mysql> CREATE PROCEDURE proc(OUT result varchar(50))
    -> BEGIN
    ->     SELECT COUNT(a.id)
    ->     INTO result
    ->     FROM member a LEFT JOIN organizations org ON a.xx = org.yy
    ->             WHERE a.count >= 0;
    -> END ##
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 'member a LEFT JOIN organizations org ON a.xx = org.yy
            WHERE a.count ' at line 5
mysql>

This DDL is executed successfully on mysql-server of version 8016:

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

mysql>
mysql> delimiter ##
mysql>
mysql> CREATE PROCEDURE proc(OUT result varchar(50))
    -> BEGIN
    ->     SELECT COUNT(a.id)
    ->     INTO result
    ->     FROM member a LEFT JOIN organizations org ON a.xx = org.yy
    ->             WHERE a.count >= 0;
    -> END ##
Query OK, 0 rows affected (0.00 sec)

mysql>

Suggested fix:
Backward compatibility should be satisfied.

[28 May 2020 12:27] MySQL Verification Team

Hi Mr. tennyson,

Thank you for your bug report.

However, this is not a bug. I had no problem whatsoever in using your code to create a procedure, with mysql-8.0.20. You probably had tabs or other characters in the new line, or this is already problem that is fixed.

Here is the output:

mysql> delimiter ##
mysql> CREATE PROCEDURE proc(OUT result varchar(50))
    -> BEGIN
    ->       SELECT COUNT(a.id) INTO result FROM member a LEFT JOIN organizations org ON a.xx = org.yy WHERE a.count >= 0;
    -> END ##
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> delimiter ;

Not a bug.

[28 May 2020 12:55] lyp tennyson

I have tried the DDL again on the latest version.
Yes, it works OK on 8.0.20.  
THX～

[28 May 2020 13:22] MySQL Verification Team

You are welcome.