Bug #10262 Stored procedures: character set clause ignored for variables

• Submitted: 29 Apr 2005 14:08
• Modified: 4 Oct 2005 6:51
• Reporter: Peter Gulutzan
• Status: Can't repeat
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.6-beta-debug
• OS: Linux (Linux, Windows)
• CPU Architecture: Any

[29 Apr 2005 14:08] Peter Gulutzan

Description:
If a stored-procedure CHAR variable is defined with a multibyte character set,
then hex() should show multiple bytes and octet_length should show multiple
bytes. That's what would happen for values outside a stored procedure.
Inside a stored procedure, after fetch with a cursor, it doesn't happen.
See also bug#6513.
 

How to repeat:
mysql> delimiter //
mysql> create table t92 (s1 char character set sjis)//
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t92 values (0x8150)//
Query OK, 1 row affected (0.00 sec)

mysql> create procedure p92 () begin declare v char character set sjis; declare c cursor for select s1 from t92; open c; fetch c into v; select hex(v), octet_length(v); end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p92()//
+--------+-----------------+
| hex(v) | octet_length(v) |
+--------+-----------------+
| 3F     |               1 |
+--------+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[15 Sep 2005 8:11] Valeriy Kravchuk

I was able to repeat on 5.0.12-beta-nt:

mysql> use test
Database changed
mysql> create table t92 (s1 char character set sjis);
Query OK, 0 rows affected (0.66 sec)

mysql> insert into t92 values (0x8150);
Query OK, 1 row affected (0.14 sec)

mysql> delimiter //
mysql> create procedure p92 () begin declare v char character set sjis; declare
    -> c cursor for select s1 from t92; open c; fetch c into v; select hex(v),
    -> octet_length(v); end//
Query OK, 0 rows affected (0.31 sec)

mysql> call p92()//
+--------+-----------------+
| hex(v) | octet_length(v) |
+--------+-----------------+
| 3F     |               1 |
+--------+-----------------+
1 row in set (0.18 sec)

Query OK, 0 rows affected (0.22 sec)

mysql> select s1, hex(s1), octet_length(s1) from t92//
+------+---------+------------------+
| s1   | hex(s1) | octet_length(s1) |
+------+---------+------------------+
| ?    | 8150    |                2 |
+------+---------+------------------+
1 row in set (0.00 sec)

mysql> select version()//
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

So, it is a really different behaviour in SP.

[3 Oct 2005 21:17] Alexander Nozdrin

It seems, the bug was fixed by BUG#6513.

[4 Oct 2005 6:51] Valeriy Kravchuk

I was unable to repeat it any more on 5.0.13-rc-nt:

mysql> use test;
Database changed
mysql> drop table t92;
Query OK, 0 rows affected (0.53 sec)

mysql> create table t92 (s1 char character set sjis);
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t92 values (0x8150);
Query OK, 1 row affected (0.11 sec)

mysql> drop procedure p92;
Query OK, 0 rows affected (0.22 sec)

mysql> delimiter //
mysql> create procedure p92 () begin declare v char character set sjis; declare
    -> c cursor for select s1 from t92; open c; fetch c into v; select hex(v),
    -> octet_length(v); end//
Query OK, 0 rows affected (0.02 sec)

mysql> call p92()//
+--------+-----------------+
| hex(v) | octet_length(v) |
+--------+-----------------+
| 8150   |               2 |
+--------+-----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> select s1, hex(s1), octet_length(s1) from t92//
+------+---------+------------------+
| s1   | hex(s1) | octet_length(s1) |
+------+---------+------------------+
| ?    | 8150    |                2 |
+------+---------+------------------+
1 row in set (0.00 sec)

mysql> select version()//
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)

Looks like bug #6513 fixed in this version solved this problem too.

[10 Oct 2005 21:03] Roland Bouman

I'm having a similar bug with varchar stored procedure parameters in mysql 5.0.13-rc-nt. see: bug 13909. (Sorry if this is a duplicate)