Bug #19621 Character set introducer before parameter substitution causes syntax error
Submitted: 8 May 2006 17:35 Modified: 18 May 2006 1:20
Reporter: Gwynne Raskind Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.21/5.0BK/5.1BK/4.1BK OS:Any (*)
Assigned to: CPU Architecture:Any

[8 May 2006 17:35] Gwynne Raskind
Description:
When using a character set identifier before a prepared statement parameter subsitution marker (?), a syntax error is given, though the manual says the syntax is valid: http://dev.mysql.com/doc/refman/5.0/en/charset-literal.html

How to repeat:
Expected:
> PREPARE s FROM 'SELECT _latin1 ?';
Query OK, 0 rows affected (0.00 sec)
> SET @a:='a';
Query OK, 0 rows affected (0.00 sec)
> EXECUTE s USING @a;
+---+
| ?  |
+---+
| a  |
+---+
1 row in set (0.00 sec)

Actual:
> PREPARE s FROM 'SELECT _latin1 ?';
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 '?' at line 1

(This happens with both the SQL syntax and various prepared statement APIs)

Suggested fix:
Make it work?
[8 May 2006 18:14] MySQL Verification Team
Thank you for the bug report. Indeed I understand the same reading the
Manual.
[8 May 2006 18:16] Gwynne Raskind
As far as I can tell, this bug also eists in all versions of 4.1.x as well as 5.x.
[8 May 2006 18:25] MySQL Verification Team
Yes you are right also for 4.1
[17 May 2006 16:25] Konstantin Osipov
This is a bug in the manual and a valid feature request. The syntax is not valid and has never been valid.
[17 May 2006 16:36] Paul DuBois
I'll remove the syntax from the manual, because it's not
supported.

Changing Severity to Feature Request.
Unassigning Stefan from Lead.
[18 May 2006 1:20] Gwynne Raskind
Since this is an invalid syntax, is it possible to specify the character set used by a particular parameter substitution? If not, which character set (server, connection, client, or database) takes effect with regards to string parameters? This is a critical question for our current development.
[18 May 2006 9:20] Konstantin Osipov
If you specify MYSQL_TYPE_BLOB as the input type of the data, then no character set assumptions are made - the data is inserted into a column without conversions.

Otherwise the data is assumed to be in CHARACTER_SET_CLIENT. Therefore, if the character set of a column doens't match CHARACTER_SET_CLIENT, a conversion is performed and the parameter value is converted to the column character set.