MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:June 17 2010 12:01pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (gshchepa:3246)
Bug#53742
View as plain text  
#At file:///mnt/sda7/work/53742-next-mr-bugfixing/ based on revid:gshchepa@stripped

 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.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2010-05-08 22:03:35 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-06-17 12:01:06 +0000
@@ -2459,3 +2459,35 @@ COMMIT;
 COMMIT;
 DROP TABLE t1;
 DROP FUNCTION f1;
+#
+# Bug #53742: UPDATEs have no effect after applying patch for bug 36569
+#
+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;
+pk	c1_idx	c2
+4	y	NULL
+3	y	NULL
+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;
+pk	c1_idx	c2
+4	y	0
+3	y	0
+SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;
+pk	c1_idx	c2
+4	y	0
+3	y	0
+2	y	NULL
+1	y	NULL
+DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
+SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC;
+pk	c1_idx	c2
+2	y	NULL
+1	y	NULL
+DROP TABLE t1;

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-05-08 22:03:35 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-06-17 12:01:06 +0000
@@ -707,3 +707,30 @@ connection default;
 COMMIT;
 DROP TABLE t1;
 DROP FUNCTION f1;
+
+
+--echo #
+--echo # Bug #53742: UPDATEs have no effect after applying patch for bug 36569
+--echo #
+
+
+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;
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-06-16 19:42:03 +0000
+++ b/sql/sql_select.cc	2010-06-17 12:01:06 +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;


Attachment: [text/bzr-bundle] bzr/gshchepa@mysql.com-20100617120106-je8mei4ce99oyed8.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (gshchepa:3246)Bug#53742Gleb Shchepa17 Jun