Bug #53742 UPDATEs have no effect after applying patch for bug 36569
Submitted: 18 May 2010 10:11 Modified: 29 Jul 2010 19:12
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:next-mr OS:Any
Assigned to: Gleb Shchepa
Triage: Triaged: D1 (Critical)

[18 May 2010 10:11] Philip Stoev
Description:
After applying the patch for bug 36569 from

http://lists.mysql.com/commits/108458

to

revision-id: alik@sun.com-20100507091823-nzao4h3qosau4tin
date: 2010-05-07 13:18:23 +0400
build-date: 2010-05-18 12:20:11 +0300
revno: 3143
branch-nick: mysql-next-mr-bug36569

certain UPDATE statements no longer cause changes to the database, even though affected_rows is greater than zero.

How to repeat:
Using the dataset that will be attached shortly, run:

SET AUTOCOMMIT=OFF;
START TRANSACTION;

mysql> select * from A WHERE `col_varchar_10_key`  IN ('y') ORDER BY `pk` DESC LIMIT 10;
+------------+----------------+--------------------+------+-------------+
| col_int    | col_varchar_10 | col_varchar_10_key | pk   | col_int_key |
+------------+----------------+--------------------+------+-------------+
|          7 | XNAWN          | y                  | 9960 |        NULL |
| 1315831808 | RSAWZ          | y                  | 9732 |         146 |
| 1306984448 | ijgtalpkhx     | y                  | 9696 |           3 |
|        206 | r              | y                  | 9554 |           9 |
|          6 | ofeamvymhb     | y                  | 9493 |        NULL |
|          7 | JCPAK          | y                  | 9406 | -1193213952 |
|       NULL | one            | y                  | 9367 | -1871118336 |
|          2 | fwvabsowtu     | y                  | 9089 | -1651638272 |
|  936640512 | ctrglgvcqh     | y                  | 8858 |        NULL |
|        218 | or             | y                  | 8733 |  -856096768 |
+------------+----------------+--------------------+------+-------------+
10 rows in set (0.00 sec)

mysql> UPDATE `A` SET `col_int_key` = 0 WHERE `col_varchar_10_key`  IN ('y') ORDER BY `pk` DESC LIMIT 10;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 10  Changed: 9  Warnings: 0

mysql> select * from A WHERE `col_varchar_10_key`  IN ('y') ORDER BY `pk` DESC LIMIT 10;
+------------+----------------+--------------------+------+-------------+
| col_int    | col_varchar_10 | col_varchar_10_key | pk   | col_int_key |
+------------+----------------+--------------------+------+-------------+
|          7 | XNAWN          | y                  | 9960 |        NULL |
| 1315831808 | RSAWZ          | y                  | 9732 |         146 |
| 1306984448 | ijgtalpkhx     | y                  | 9696 |           3 |
|        206 | r              | y                  | 9554 |           9 |
|          6 | ofeamvymhb     | y                  | 9493 |        NULL |
|          7 | JCPAK          | y                  | 9406 | -1193213952 |
|       NULL | one            | y                  | 9367 | -1871118336 |
|          2 | fwvabsowtu     | y                  | 9089 | -1651638272 |
|  936640512 | ctrglgvcqh     | y                  | 8858 |        NULL |
|        218 | or             | y                  | 8733 |  -856096768 |
+------------+----------------+--------------------+------+-------------+
10 rows in set (0.00 sec)

The UPDATE reports "Rows matched: 10  Changed: 9  Warnings: 0" whereas in fact the database appears unchanged;
[18 May 2010 10:12] Philip Stoev
Data for bug 53742

Attachment: bug53742.zip (application/x-zip-compressed, text), 135.89 KiB.

[10 Jun 2010 18:27] Gleb Shchepa
Actually UPDATE *has* effect, but in a wrong order (it ignores DESC).

See a similar bug #51431.
[12 Jun 2010 8:17] Gleb Shchepa
DELETE is affected as well.

Simplified test case:

-- source include/have_innodb.inc
CREATE TABLE t1 (
  pk INT NOT NULL AUTO_INCREMENT,
  c1_idx CHAR(1) DEFAULT 'y',
  c2 INT,
  PRIMARY KEY (pk),
  INDEX c1_idx (c1_idx)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (), (), (), ();

SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;

DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;

DROP TABLE t1;
[12 Jun 2010 8:37] Gleb Shchepa
Preliminary fix:

=== modified file 'sql/sql_select.cc'
--- old/sql/sql_select.cc	2010-06-12 08:33:57 +0000
+++ new/sql/sql_select.cc	2010-06-12 08:36:16 +0000
@@ -17535,7 +17535,9 @@ uint get_index_for_order(ORDER *order, T
       return MAX_KEY;
     }
 
-    switch (test_if_order_by_key(order, table, select->quick->index)) {
+    uint used_key_parts;
+    switch (test_if_order_by_key(order, table, select->quick->index,
+                                 &used_key_parts)) {
     case 1: // desired order
       *need_sort= FALSE;
       return select->quick->index;
@@ -17547,7 +17549,7 @@ uint get_index_for_order(ORDER *order, T
       {
         QUICK_SELECT_I *reverse_quick;
         if ((reverse_quick=
-               select->quick->make_reverse(select->quick->used_key_parts)))
+               select->quick->make_reverse(used_key_parts)))
         {
           select->set_quick(reverse_quick);
           *need_sort= FALSE;
[17 Jun 2010 12:18] 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/commits/111433

3246 Gleb Shchepa	2010-06-17
      Bug #53742: UPDATEs have no effect after applying patch for bug 36569
      
      This patch is a part of the fix for bug #36569.
      
      UPDATE/DELETE on InnoDB tables with descending ORDER BY the primary
      key and a quick select ordered by some secondary key were
      updated/deleted in the incorrect (ascending) order.
      
      The optimizer tries to eliminate the ORDER BY clause when applicable.
      If an intermediate output of quick select is already ordered, but in
      the reverse direction, the optimizer tries to wraps that quick select
      with a QUICK_SELECT_DESC object.
      
      Coincidence of the following facts led to a trouble:
        1) secondary key fields were constants through the WHERE expression
           (quick select with an equal range), and
        2) that quick select ordered the output by some secondary key, and
        3) that secondary key had a PK suffix (InnoDB), and
        4) ORDER BY PK DESC was used, so it may be replaced with a
           that secondary key in reverse order.
      In this case the number of used key parts is larger than the
      total number of own secondary key parts (excluding PK suffix parts),
      however that was missed by the mistake, and QUICK_SELECT_DESC
      wrapper was used with the original second key part counter.
      That has been fixed.
     @ mysql-test/r/innodb_mysql.result
        Added test case for bug #53742.
     @ mysql-test/t/innodb_mysql.test
        Added test case for bug #53742.
     @ sql/sql_select.cc
        Bug #53742: UPDATEs have no effect after applying patch for bug 36569
        
        The get_index_for_order function has been modified to take
        into account an adjusted key part counter for the secondary key
        that the optimizer uses to skip unnecessary ORDER BY PK DESC.
[23 Jul 2010 12:28] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[29 Jul 2010 19:12] Paul Dubois
Noted in 5.5.6 changelog.

The patch for Bug#36569 caused performance regressions and incorrect
execution of some UPDATE statments.