Bug #28206 SUBSTRING function behavior differences
Submitted: 2 May 2007 18:15 Modified: 2 Jun 2007 19:21
Reporter: Darren Ford Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.37-Community RPM OS:Linux (Red Hat EL 4 AS EMT64)
Assigned to: CPU Architecture:Any

[2 May 2007 18:15] Darren Ford
I have a stored procedure that needed a loop to break down a comma delimited list of numbers (ids for record inserts) and was using the following syntax:

SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);

remainder value was '1581,1589,1590' before the call
cur_position was 5
delimiter_length was 1

Under both the 5.0.37 version in windows and the 5.0.37 version running on Fedora Core 6 this was returning '1589,1590' for the remainder variable but on Red Hat EL 4 AS EMT64 version 5.0.37 (obtained from Mysql website community version) this same statement was returning '9,1590' which appeared to be getting the last 6 characters from the string rather than starting at position 6 and returning the rest of the string.

The following statement on this SAME system provided the correct result:

SELECT SUBSTRING(remainder, cur_position + delimiter_length) into tmpVar;
SET remainder = tmpVar;

I am not sure why using the select method would be any different.

How to repeat:
Tried to reproduce on same version under windows but couldn't. Same thing for Fedora Core 6 using RPM obtained from web using same version number.
[2 May 2007 19:21] MySQL Verification Team
Thank you for the bug report. Could you please provide the complete
test case script, the result you got and what you expect. Thanks in
[2 Jun 2007 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".