Bug #2676 DECLARE can't have COLLATE clause in stored procedure
Submitted: 8 Feb 2004 11:47 Modified: 1 Mar 2006 12:49
Reporter: Peter Gulutzan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.0.0-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: CPU Architecture:Any

[8 Feb 2004 11:47] Peter Gulutzan
Description:
I can use a CHARACTER SET clause in a DECLARE statement in a stored procedure. But I 
can't use a COLLATE clause. 
 
 
 

How to repeat:
mysql> create procedure p () begin declare v char(5) collate latin1_german2_ci; 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 'collate 
latin1_german2_ci; end' at line 1 
 
mysql> create procedure p () begin declare v char(5) character set latin1; end;// 
Query OK, 0 rows affected (0.00 sec)
[17 Feb 2004 11:35] Dean Ellis
Verified against 5.0.1-alpha-log.
[6 Apr 2004 2:43] Per-Erik Martin
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Page 504 in SQL-99 Complete, Really:
"TRAP: Don't get confused by the similarity to a <Column definition>. A variable definition can contain only a <data type> and (optionally) a DEFAULT clause. It cannot contain a <Domain name>, a <Constraint> or a COLLATE clause."
[10 Jan 2006 10:35] Per-Erik Martin
It turns out that the syntax for character string types in stored routines was changed in SQL:2003, as noted by Peter G.:

''Looking at the SQL:1999 standard, I see
"
Section 6.1 <data type>
...
<data type> ::=
 <predefined type>
...
<predefined type> ::=
 <character string type> [ CHARACTER SET <character set specification> ]
"

But looking at the SQL:2003 standard, I see
"
6.1 <data type>
...
<data type> ::=
 <predefined type>
...
<predefined type> ::=
 <character string type> [ CHARACTER SET <character set specification> ]
 [ <collate clause> ]
"

There's been a change.''