MySQL Bugs: #53742: UPDATEs have no effect after applying patch for bug 36569

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	CPU Architecture:	Any

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;

Data for bug 53742

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

Actually UPDATE *has* effect, but in a wrong order (it ignores DESC).

See a similar bug #51431.

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;

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;

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.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

Noted in 5.5.6 changelog.

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