bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3405) Bug#51431

[Sergey Glukhov]

#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:sergey.glukhov@stripped

 3405 Sergey Glukhov	2010-06-07
      Bug#51431 Wrong sort order after import of dump file
      The problem is that QUICK_SELECT_DESC behaviour depends
      on used_key_parts value which can be bigger than selected
      best_key_parts value if an engine supports clustered key.
      But used_key_parts is overwritten with best_key_parts
      value that prevents from correct selection of index
      access method. The fix is to preserve used_key_parts
      value for further use in QUICK_SELECT_DESC.
     @ mysql-test/r/innodb_mysql.result
        test case
     @ mysql-test/t/innodb_mysql.test
        test case
     @ sql/sql_select.cc
        preserve used_key_parts value for further use in QUICK_SELECT_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-25 14:43:45 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-06-07 09:48:58 +0000
@@ -2417,4 +2417,36 @@ ENGINE=InnoDB;
 INSERT INTO t1 VALUES (0, 77, 1, 3);
 UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25;
 DROP TABLE t1;
+#
+# Bug#51431 Wrong sort order after import of dump file
+#
+CREATE TABLE t1 (
+f1 INT(11) NOT NULL,
+f2 int(11) NOT NULL,
+f3 int(11) NOT NULL,
+f4 tinyint(1) NOT NULL,
+PRIMARY KEY (f1),
+UNIQUE KEY (f2, f3),
+KEY (f4)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
+(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
+(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
+(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
+(26,1,9921,1), (27,1,9922,1);
+FLUSH TABLES;
+SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+f1	f2	f3	f4
[phone]
[phone]
[phone]
[phone]
[phone]
+EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	f2,f4	f4	1	NULL	11	Using where
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-05-25 14:43:45 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-06-07 09:48:58 +0000
@@ -663,4 +663,34 @@ UPDATE t1 SET d = 0 WHERE b = 77 AND c =
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#51431 Wrong sort order after import of dump file
+--echo #
+
+CREATE TABLE t1 (
+  f1 INT(11) NOT NULL,
+  f2 int(11) NOT NULL,
+  f3 int(11) NOT NULL,
+  f4 tinyint(1) NOT NULL,
+  PRIMARY KEY (f1),
+  UNIQUE KEY (f2, f3),
+  KEY (f4)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
+(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
+(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
+(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
+(26,1,9921,1), (27,1,9922,1);
+
+FLUSH TABLES;
+
+SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+
+DROP TABLE t1;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-05-27 15:13:53 +0000
+++ b/sql/sql_select.cc	2010-06-07 09:48:58 +0000
@@ -13656,7 +13656,16 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           */
         }
       }
-      used_key_parts= best_key_parts;
+      /*
+        'used_key_parts' affects QUICK_SELECT_DESC behaviour.
+        If an engine supports clustered keys this value
+        might be greater than  best_key_parts and we need to
+        preserve this value for further use in QUICK_SELECT_DESC.
+      */
+      if (!select || !select->quick)
+        used_key_parts= best_key_parts;
+      else
+        used_key_parts= max(used_key_parts, best_key_parts);
       order_direction= best_key_direction;
     }
     else


Attachment: [text/bzr-bundle] bzr/[e-mail]