MySQL Bugs: #69684: The update and select behave differently under the same conditions.

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

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.

Please upload the mysqldump of the table only ...

Thanks in advance ...

I repeated the behavior with the same version of 5.5 that you are using. Now, testing with latest 5.5 and 5.6.

Bug exists also in latest versions of 5.5 and 5.6. Hence, verified !!!!

Thank you for your contribution !!!

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.

$ 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.