MySQL Bugs: #75777: SELECT FROM DUAL PROCEDURE ANALYSE() returns syntax error

Bug #75777	SELECT FROM DUAL PROCEDURE ANALYSE() returns syntax error
Submitted:	5 Feb 2015 0:47	Modified:	6 Feb 2015 14:24
Reporter:	Federico Razzoli	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.6, 5.6.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This query:

SELECT PI() FROM DUAL PROCEDURE ANALYSE();

returns a 1064 error. Same happens if I drop "from dual". But it works if I replace "dual" with a table name.

How to repeat:
SELECT PI() FROM DUAL PROCEDURE ANALYSE();

Hello Federico Razzoli,

Thank you for the report.

Thanks,
Umesh

// 5.6.22

mysql> create table p2 select "OK" from dual;
mysql> SELECT PI() FROM DUAL PROCEDURE ANALYSE();
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 'PROCEDURE ANALYSE()' at line 1
mysql>
mysql> SELECT PI() FROM p2 PROCEDURE ANALYSE();
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+----------+---------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std      | Optimal_fieldtype         |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+----------+---------------------------+
| PI()       | 3.141593  | 3.141593  |          8 |          8 |                0 |     0 | 3.141593                | 0.000000 | ENUM('3.141593') NOT NULL |
+------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+----------+---------------------------+
1 row in set (0.00 sec)

Is this really only in 5.6? I've been able to repeat it on MariaDB 5.5 - but I won't change the "Version" field because I don't have any old MySQL version installed.

MySQL 5.5 is also affected:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3312 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table p2 select "OK" from dual;
Query OK, 1 row affected (2.63 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT PI() FROM DUAL PROCEDURE ANALYSE();
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 'PROCE
DURE ANALYSE()' at line 1
mysql> SELECT PI() FROM p2 PROCEDURE ANALYSE();
+------------+-----------+-----------+------------+------------+----------------
--+-------+-------------------------+----------+---------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zero
s | Nulls | Avg_value_or_avg_length | Std      | Optimal_fieldtype         |
+------------+-----------+-----------+------------+------------+----------------
--+-------+-------------------------+----------+---------------------------+
| PI()       | 3.141593  | 3.141593  |          8 |          8 |
0 |     0 | 3.141593                | 0.000000 | ENUM('3.141593') NOT NULL |
+------------+-----------+-----------+------------+------------+----------------
--+-------+-------------------------+----------+---------------------------+
1 row in set (0.09 sec)