MySQL Bugs: #35694: Wrong string function

Bug #35694	Wrong string function
Submitted:	31 Mar 2008 7:20	Modified:	9 Sep 2008 6:06
Reporter:	BIGGIO LAI	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.51a	OS:	Linux (CentOS 5.1)
Assigned to:		CPU Architecture:	Any

Description:
Wrong string function output when using in stored procedure :

I have no this kind of problem when using Mysql 5.0.26

When using mysql 5.0.51a rpm version or source version, I get the same error value.

How to repeat:
1/ Create one procedure:

CREATE DEFINER = 'sysadmin'@'%' PROCEDURE `test`( IN txtValues TEXT )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
	DECLARE strKeyDelim VARCHAR(1) DEFAULT ',';
	DECLARE tmpValues TEXT;
	DECLARE tmpValue VARCHAR(255);
	
	SET tmpValues = txtValues;
	REPEAT
		SET tmpValue = SUBSTRING_INDEX( tmpValues, strKeyDelim, 1 );
		IF tmpValue = tmpValues THEN
			SET tmpValues = '';
		ELSE
			SET tmpValues = SUBSTRING( tmpValues, LENGTH( tmpValue ) + 1 + LENGTH( strKeyDelim ) );
		END IF;
		select tmpValue;

	UNTIL tmpValues = ''
	END REPEAT;
END;

2/ Call this procedure in client side, 

Call `test`( 'a,bcdef' );

Got wrong rule:
+----------+
| tmpValue |
+----------+
| a        |
+----------+
1 row in set (0.00 sec)

+----------+
| tmpValue |
+----------+
|   def    |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Normally, we should get the rule:
+----------+
| tmpValue |
+----------+
| a        |
+----------+
1 row in set (0.00 sec)

+----------+
| tmpValue |
+----------+
| bcdef    |
+----------+
1 row in set (0.00 sec)

Thank you for a problem report. Please, send the results of:

show variables like 'character_set\_%';

I was not able to repeat the problem described:

mysql> show variables like 'character_set\_%'//
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | utf8   |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

mysql> Call `test`( 'a,bcdef' )//
+----------+
| tmpValue |
+----------+
| a        |
+----------+
1 row in set (0.00 sec)

+----------+
| tmpValue |
+----------+
| bcdef    |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Below is the character set result:
[db_order]> show variables like 'character_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | utf8   |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

When using latin1_ci as the db character set... the problem is gone.

However, we need use UTF8 as the db character set.

Please, try to repeat with a newer version, 5.0.67, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".