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
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S4 (Feature request)
• Version: 5.0.0-alpha-debug
• OS: Linux (SuSE 8.2)
• 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.''