MySQL Bugs: #15666: Docs: cando PREPARE with SELECT, cant do with SELECT..INTO

Bug #15666	Docs: cando PREPARE with SELECT, cant do with SELECT..INTO
Submitted:	11 Dec 2005 13:20	Modified:	11 Dec 2005 14:23
Reporter:	Roland Bouman	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	NA
Assigned to:		CPU Architecture:	Any

Description:
The documentation regarding prepared statements clearly states it's limitations in terms of what types of statements can and what can't be prepared:

"The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not yet supported"

The documentation regarding this feature was found at:
http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

Among the ones that can be prepared is the SELECT statement. However, the SELECT..INTO statement *CANNOT* be prepared. This is not clear from the documentation.

How to repeat:
mysql> desc t;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.08 sec)

mysql> prepare stmt from 'select id,name into ?,? from t;';
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 '?,? f
mysql> prepare stmt from 'select id,name from t;';
Query OK, 0 rows affected (0.02 sec)
Statement prepared

Suggested fix:
Please change the text so that it reads:

"The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT (but not: SELECT..INTO), SET, UPDATE, and most SHOW statements. Other statements are not yet supported"

Alternatively, allow the SELECT..INTO syntax for prepared statements

Thank you for a problem report. Sorry, but it is neither a bug, nor a documentation request. The problem you encountered is clearly documented on that same page (http://dev.mysql.com/doc/refman/5.0/en/sqlps.html):

"Within the statement, ‘?’ characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The ‘?’ characters should not be enclosed within quotes, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth."

Please, look:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18

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

mysql> desc t;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| i     | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> prepare stmt from 'select i into ? from t';
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 '? from t' at line 1
mysql> prepare stmt from 'select i into @var from t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

So, the problem is not that SELECT ... INTO can not be prepared in general, but with your particular statement.

Valeriy, 

Ok, no I understand. I never gathered 'data values' was meant that literally, I thought the term 'value expression' was intendend. My bad. However, I feel that there is a fundamental difference between identifiers of variables and identifiers of objects. e.g. I understand that:

prepare 'create table ? (? int)' 

is not something you'd wanna to support, whereas I feel that 

prepare 'select col into ? from t' 

really should be possible. 

Anyway, thanks for (as ever ;-) quick response.

Roland

And, a quick test:

mysql> prepare 'select id,name into @id,@name from t';
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 ''sele
mysql>

shows me that you really can *NOT* use SELECT INTO as a prepared statement... 
So, I think it should still be documented.

Whoops! I was a bit too quick, (my cheeks blushing red in shame). Of course my last comment is bogus, it works fine :)

mysql> prepare stmt from 'select id,name into @id,@name from t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Sorry!