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

[31 Mar 2008 7:20] BIGGIO LAI
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)
[31 Mar 2008 8:18] Valeriy Kravchuk
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)
[31 Mar 2008 8:29] BIGGIO LAI
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)
[31 Mar 2008 8:36] BIGGIO LAI
When using latin1_ci as the db character set... the problem is gone.

However, we need use UTF8 as the db character set.
[9 Aug 2008 6:06] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.67, and inform about the results.
[9 Sep 2008 23:00] Bugs System
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".