Bug #8942 SUBSTRING_INDEX in UPDATE causes internal loop
Submitted: 4 Mar 2005 9:13 Modified: 17 Mar 2005 16:24
Reporter: Jigal van Hemert Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.23 / 4.1.8 / 4.1.10 OS:Any (*)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[4 Mar 2005 9:13] Jigal van Hemert 
Description:
UPDATE query with SUBSTRING_INDEX causes internal loop repeating the update  until the value reaches the next power of 10. The affected rows count reflects the number of times the update of the single row was repeated.
value   affected rows   new value
      1                9              10
    10               90             100
   100             900           1000
      4                 6               10
    587            413            1000

When query is repeated without other actions in between, it sometimes works correctly.

Bug works with bot MyISAM and InnoDB tables

How to repeat:
Table structure:
==========

CREATE TABLE `param_str` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(32) NOT NULL default '',
  `value` varchar(255) default NULL,
  `match` varchar(255) default NULL,
  `match2` varchar(255) default NULL,
  `weight` tinyint(4) default NULL,
  KEY `id-name-value-match-match2`
(`id`,`name`,`value`(20),`match`(20),`match2`(20))
) TYPE=InnoDB;

INSERT INTO `param_str` VALUES (0, 'prf_hits', '1', NULL, NULL, NULL);

Query:
====

UPDATE `param_str`
SET `value` =
   SUBSTRING_INDEX(
    `value`,
    ';',
    1
   ) + 1
 WHERE
 `id`=0  AND
 `name`='prf_hits'
[17 Mar 2005 6:25] 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/internals/23127
[17 Mar 2005 6:37] Ramil Kalimullin 
fixed in 4.0.25
[17 Mar 2005 16:24] Paul DuBois 
Noted in 4.0.25 changelog.