Bug #69684 The update and select behave differently under the same conditions.
Submitted: 7 Jul 2013 10:44 Modified: 20 May 2014 0:10
Reporter: yu hongyu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5, 5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: UPDATE

[7 Jul 2013 10:44] yu hongyu
Description:
Hi,

I expect UPDATE can affect all the records,that found by SELECT with the same condition.

mysql> select brand,hex(brand),brand_id,count(*) from marketphoto where brand='5cm' group by hex(brand),brand_id;
+-------+------------+----------+----------+
| brand | hex(brand) | brand_id | count(*) |
+-------+------------+----------+----------+
| 5CM   | 35434D     |    72755 |       78 |
| 5CM   | 35434D20   |    72755 |        5 |
+-------+------------+----------+----------+
2 rows in set (0.00 sec)

mysql> update marketphoto set brand_id=1414 where brand='5cm';
Query OK, 79 rows affected (0.00 sec)
Rows matched: 79  Changed: 79  Warnings: 0

mysql> select brand,hex(brand),brand_id,count(*) from marketphoto where brand='5cm' group by hex(brand),brand_id;
+-------+------------+----------+----------+
| brand | hex(brand) | brand_id | count(*) |
+-------+------------+----------+----------+
| 5CM   | 35434D     |     1414 |       78 |
| 5CM   | 35434D20   |     1414 |        1 |
| 5CM   | 35434D20   |    72755 |        4 |
+-------+------------+----------+----------+
3 rows in set (0.01 sec)

mysql> update marketphoto set brand_id=1414 where brand='5cm';
Query OK, 1 row affected (0.01 sec)
Rows matched: 80  Changed: 1  Warnings: 0

mysql> select brand,hex(brand),brand_id,count(*) from marketphoto where brand='5cm' group by hex(brand),brand_id;
+-------+------------+----------+----------+
| brand | hex(brand) | brand_id | count(*) |
+-------+------------+----------+----------+
| 5CM   | 35434D     |     1414 |       78 |
| 5CM   | 35434D20   |     1414 |        2 |
| 5CM   | 35434D20   |    72755 |        3 |
+-------+------------+----------+----------+
3 rows in set (0.01 sec)

mysql> update marketphoto set brand_id=72744 where brand like '5cm%';
Query OK, 83 rows affected (0.01 sec)
Rows matched: 83  Changed: 83  Warnings: 0

Is it a bugļ¼Ÿ

Thank you.

How to repeat:
It doesn't always appear.
You can get it by using the same table structure and data.
It has the same behaviour with MyISAM and InnoDB.And REPAIR table has no effect.
But if I drop the index on brand and then create it again,it disappears.
I'll upload the files.
[11 Jul 2013 17:35] Sinisa Milivojevic
Please upload the mysqldump of the table only ...

Thanks in advance ...
[12 Jul 2013 14:26] Sinisa Milivojevic
I repeated the behavior with the same version of 5.5 that you are using. Now, testing with latest 5.5 and 5.6.
[12 Jul 2013 14:37] Sinisa Milivojevic
Bug exists also in latest versions of 5.5 and 5.6. Hence, verified !!!!

Thank you for your contribution !!!
[20 May 2014 0:10] Paul Dubois
Noted in 5.6.19, 5.7.5 changelogs.

Updates could fail to update all applicable rows in cases where
multiple key values were identical except for trailing spaces.
[2 Jun 2014 14:33] Laurynas Biveinis
$ bzr log -r 5910
------------------------------------------------------------
revno: 5910
committer: mithun <mithun.c.y@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2014-04-30 16:20:10 +0530
message:
  Bug #17156940 : THE UPDATE AND SELECT BEHAVE DIFFERENTLY
                  UNDER THE SAME CONDITIONS.
  ISSUE   :
  In myisam, Suppose in btree index of varchar type we have
  keys with and without trailing spaces example keys 'abc',
  'abc ', 'abc '. During index search based on same key,
  length of the lastkey get changed if lastkey read is the
  one with trailing spaces. If x is the length of key 'abc'.
  After reading 'abc ' length will be x + 1. And,
  last_rkey_length should be recalculated as accordingly
  whenever lastkey changes.
  But in function mi_rnext_same during BTREE search when we
  tried to copy lastkey to lastkey2 we have used non updated
  last_rkey_length even though lastkey and its length might
  have been changed as explained above. And, last_rkey_length
  is computed only once during mi_rkey. Because of this
  invalid length usage compare_key failed and scan got
  terminated. And, hence Update command ended before updating
  further tuples which will satisfy the condition.
  
  SOLUTION :
  In function mi_rnext_same the input key lastkey2 can remain
  constant if we use a separate buffer other than lastkey2.
  And, we can fill this key just for one time for the complete
  scan in mi_rnext_same. Since key and length is computed only
  once for the entire scan, issues related to invalid length
  during compare keys will not arise.