Bug #27900 'create view' with user variable makes server unstable

• Submitted: 17 Apr 2007 20:03
• Modified: 17 Apr 2007 20:59
• Reporter: Luciano Fantuzzi
• Status: Closed
• Category: MySQL Server: Views
• Severity: S2 (Serious)
• Version: 5.0.37-community-nt
• OS: Windows
• CPU Architecture: Any
• Tags: 1351, contains, create view, error, hy000, Parameter, SELECT, variable

[17 Apr 2007 20:03] Luciano Fantuzzi

Description:
A CREATE VIEW command, combinated with a user variable which increments, makes query result in error and leaves "unstable" the server for another query // El comando CREATE VIEW combinado con una variable de usuario que se autoincrementa, hace que la consulta finalice en error y deja relativamente "inestable" el servidor para otra consulta semejante.

How to repeat:
mysql> SET @a:=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE a SELECT (@a:=@a+1);
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW a AS SELECT (@a:=@a+1);
ERROR 1351 (HY000): View's SELECT contains a variable or parameter

mysql> CREATE TEMPORARY TABLE b SELECT (@a:=@a+1);
ERROR 1351 (HY000): View's SELECT contains a variable or parameter

[17 Apr 2007 20:59] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

I was able to repeat with version 5.0.37 but not anymore with server
built from source tree:

c:\build\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.40 Source distribution

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

mysql> SET @a:=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE a SELECT (@a:=@a+1);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW a AS SELECT (@a:=@a+1);
ERROR 1351 (HY000): View's SELECT contains a variable or parameter
mysql> CREATE TEMPORARY TABLE b SELECT (@a:=@a+1);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0