List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:June 30 2010 1:06pm
Subject:bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3454)
Bug#51431
View as plain text  
#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:staale.smedseng@stripped

 3454 Sergey Glukhov	2010-06-30
      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-06-21 11:09:58 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-06-30 13:06:25 +0000
@@ -2455,4 +2455,36 @@ AND f5 = 'abcdefghijklmnopwrst' AND f2 =
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	PRIMARY,idx1,idx2	idx2,idx1,PRIMARY	7,60,4	NULL	1	Using intersect(idx2,idx1,PRIMARY); Using where
 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
+27	1	9922	1
+26	1	9921	1
+25	1	9920	1
+24	1	9919	1
+23	1	9918	1
+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-06-21 11:09:58 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-06-30 13:06:25 +0000
@@ -694,4 +694,34 @@ AND f5 = 'abcdefghijklmnopwrst' AND f2 =
 
 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-06-25 13:20:22 +0000
+++ b/sql/sql_select.cc	2010-06-30 13:06:25 +0000
@@ -13419,6 +13419,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
     uint nr;
     key_map keys;
     uint best_key_parts= 0;
+    uint saved_best_key_parts= 0;
     int best_key_direction= 0;
     ha_rows best_records= 0;
     double read_time;
@@ -13579,6 +13580,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
             {
               best_key= nr;
               best_key_parts= keyinfo->key_parts;
+              saved_best_key_parts= used_key_parts;
               best_records= quick_records;
               is_best_covering= is_covering;
               best_key_direction= direction; 
@@ -13665,8 +13667,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           */
         }
       }
-      used_key_parts= best_key_parts;
       order_direction= best_key_direction;
+      /*
+        saved_best_key_parts is actual number of used keyparts found by the
+        test_if_order_by_key function. It could differ from keyinfo->key_parts,
+        thus we have to restore it in case of desc order as it affects
+        QUICK_SELECT_DESC behaviour.
+      */
+      used_key_parts= (order_direction == -1) ?
+        saved_best_key_parts :  best_key_parts;
     }
     else
       DBUG_RETURN(0); 


Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100630130625-9p7mu3m3x8a04idz.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3454)Bug#51431Sergey Glukhov30 Jun