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:
None 
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 18:40] Alexander Nozdrin
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)
[26 Jun 2006 22:50] Miguel Solorzano
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.