| Bug #20701 | BINARY keyword should be forbidden in stored routines | ||
|---|---|---|---|
| Submitted: | 26 Jun 2006 18:40 | Modified: | 21 Sep 2006 2:30 |
| Reporter: | Alexander Nozdrin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.23/5.1BK | OS: | Linux (Suse Linux 10) |
| Assigned to: | Andrey Hristov | CPU Architecture: | Any |
[26 Jun 2006 22:50]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[4 Aug 2006 13:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/10051 ChangeSet@1.2245, 2006-08-04 15:17:52+02:00, andrey@lmy004. +5 -0 Fix for bug#20701 BINARY keyword should be forbidden in stored routines create function func() returns char(10) binary ... is no more possible. This will be reenabled when bug 2676 "DECLARE can't have COLLATE clause in stored procedure" is fixed.
[7 Aug 2006 17:19]
Marc ALFF
Approved 5.1 patch by email.
[8 Aug 2006 13:59]
Andrey Hristov
Approved by email by Marc Allf
[9 Aug 2006 15:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/10210 ChangeSet@1.2245, 2006-08-09 17:07:59+02:00, andrey@lmy004. +5 -0 Fix for bug#20701 BINARY keyword should be forbidden in stored routines create function func() returns char(10) binary ... is no more possible. This will be reenabled when bug 2676 "DECLARE can't have COLLATE clause in stored procedure" is fixed. Fix after 2nd review
[10 Aug 2006 10:31]
Andrey Hristov
Queued into 5.1-runtime
[11 Aug 2006 8:52]
Tomash Brechko
Pushed to 5.1.12.
[21 Sep 2006 2:30]
Paul DuBois
Noted in 5.1.12 changelog and stored routines chapter. The BINARY keyword now is forbidden as a data type attribute in stored routines (for example, DECLARE v1 VARCHAR(25) BINARY), because DECLARE does not support collations, and in this context BINARY specifies the binary collation of the variable's character set.
Description: BINARY keyword should be forbidden in type definitions in stored routines, as it is just ignored. BINARY keyword is used as alternative to COLLATION-clause in order to specify collation for columns in CREATE TABLE statement. I.e. the following two statements define the same table: CREATE TABLE t1( c1 VARCHAR(25) BINARY, c2 VARCHAR(25) BINARY) CHARACTER SET koi8r; CREATE TABLE t1( c1 VARCHAR(25) COLLATION koi8r_bin, c2 VARCHAR(25) COLLATION koi8r_bin) CHARACTER SET koi8r; Currently we don't support collations for stored-routine variables (see BUG#2676: DECLARE can't have COLLATE clause in stored procedure). So, we should prohibit BINARY until BUG#2676 is completed also, otherwise it looks misleading. How to repeat: mysql> CREATE FUNCTION f1() RETURNS VARCHAR(25) BINARY RETURN "test"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COLLATION(f1()); +-------------------+ | COLLATION(f1()) | +-------------------+ | latin1_swedish_ci | +-------------------+ 1 row in set (0.00 sec) mysql> CREATE PROCEDURE p1() -> BEGIN -> DECLARE v1 VARCHAR(25) BINARY; -> SELECT COLLATION(v1); -> END| Query OK, 0 rows affected (0.00 sec) mysql> CALL p1()| +-------------------+ | COLLATION(v1) | +-------------------+ | latin1_swedish_ci | +-------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)