List:Commits« Previous MessageNext Message »
From:Chaithra Gopalareddy Date:April 18 2012 6:12am
Subject:bzr push into mysql-5.1 branch (chaithra.gopalareddy:3720 to 3721)
Bug#12713907
View as plain text  
 3721 Chaithra Gopalareddy	2012-04-18
      Bug#12713907:STRANGE OPTIMIZE & WRONG RESULT UNDER
                         ORDER BY COUNT(*) LIMIT.
      
      PROBLEM:
      With respect to problem in the bug description, we
      exhibit different behaviors for the two tables
      presented, because innodb statistics (rec_per_key
      in this case) are updated for the first table
      and not so for the second one. As a result the
      query plan gets changed in test_if_skip_sort_order
      to use 'index' scan. Hence the difference in the
      explain output. (NOTE: We can reproduce the problem
      with first table by reducing the number of tuples
      and changing the table structure)
      
      The varied output w.r.t the query on the second table
      is because of the result in the query plan change.
      When a query plan is changed to use 'index' scan,
      after the call to test_if_skip_sort_order, we set
      keyread to TRUE immedietly. If for some reason
      we drop this index scan for a filesort later on,
      we fetch only the keys not the entire tuple.
      As a result we would see junk values in the result set.
      
      Following is the code flow:
      
      Call test_if_skip_sort_order
      -Choose an index to give sorted output
      -If this is a covering index, set_keyread to TRUE
      -Set the scan to INDEX scan
      
      Call test_if_skip_sort_order second time
      -Index is not chosen (note that we do not pass the
      actual limit value second time. Hence we do not choose
      index scan second time which in itself is a bug fixed
      in 5.6 with WL#5558)
      -goto filesort
      
      Call filesort
      -Create quick range on a different index
      -Since keyread is set to TRUE, we fetch only the columns of
      the index
      -results in the required columns are not fetched
      
      FIX:
      Remove the call to set_keyread(TRUE) from
      test_if_skip_sort_order. The access function which is
      'join_read_first' or 'join_read_last' calls set_keyread anyways.
     @ mysql-test/r/func_group_innodb.result
        Added test result for Bug#12713907
     @ mysql-test/t/func_group_innodb.test
        Added test case for Bug#12713907
     @ sql/sql_select.cc
        Remove the call to set_keyread as we do it from access
        functions 'join_read_first' and 'join_read_last'

    modified:
      mysql-test/r/func_group_innodb.result
      mysql-test/t/func_group_innodb.test
      sql/sql_select.cc
 3720 Georgi Kodinov	2012-04-17
      Raise version number after cloning 5.1.63

    modified:
      configure.in
=== modified file 'mysql-test/r/func_group_innodb.result'
--- a/mysql-test/r/func_group_innodb.result	2007-05-29 12:58:18 +0000
+++ b/mysql-test/r/func_group_innodb.result	2012-04-18 05:55:01 +0000
@@ -145,3 +145,43 @@ select count(*), min(7), max(7) from t2m
 count(*)	min(7)	max(7)
 0	NULL	NULL
 drop table t1m, t1i, t2m, t2i;
+#
+# Bug#12713907: STRANGE OPTIMIZE & WRONG RESULT UNDER ORDER BY 
+# COUNT(*) LIMIT.
+#
+CREATE TABLE t1 (
+id BIGINT(20) ,
+member_id_to INT(11) ,
+r_date DATE ,
+PRIMARY KEY (id,r_date),
+KEY r_date_idx (r_date),
+KEY t1_idx01 (member_id_to)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(107924526,518491,'2011-05-01'),
+(107924527,518491,'2011-05-01'),
+(107924528,518491,'2011-05-01'),
+(107924529,518491,'2011-05-01'),
+(107924530,518491,'2011-05-01'),
+(107924531,518491,'2011-05-01'),
+(107924532,518491,'2011-05-01'),
+(107924534,518491,'2011-06-21'),
+(107924535,518491,'2011-06-21'),
+(107924536,518491,'2011-06-21'),
+(107924537,518491,'2011-06-21'),
+(107924538,518491,'2011-06-21'),
+(107924542,1601319,'2011-06-21'),
+(107924543,1601319,'2011-06-21'),
+(107924544,1601319,'2011-06-21'),
+(107924545,1601319,'2011-06-21'),
+(107924546,1601319,'2011-06-21'),
+(107924547,1601319,'2011-06-21'),
+(107924548,1601319,'2011-06-21'),
+(107924549,1601319,'2011-06-21'),
+(107924550,1601319,'2011-06-21');
+SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date =
+'2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1;
+member_id_to	COUNT(*)
+518491	5
+DROP TABLE t1;
+# End of test  BUG#12713907

=== modified file 'mysql-test/t/func_group_innodb.test'
--- a/mysql-test/t/func_group_innodb.test	2006-05-22 11:27:58 +0000
+++ b/mysql-test/t/func_group_innodb.test	2012-04-18 05:55:01 +0000
@@ -83,3 +83,47 @@ explain select count(*), min(7), max(7)
 select count(*), min(7), max(7) from t2m, t1i;
 
 drop table t1m, t1i, t2m, t2i;
+
+--echo #
+--echo # Bug#12713907: STRANGE OPTIMIZE & WRONG RESULT UNDER ORDER BY 
+--echo # COUNT(*) LIMIT.
+--echo #
+
+CREATE TABLE t1 (
+id BIGINT(20) ,
+member_id_to INT(11) ,
+r_date DATE ,
+PRIMARY KEY (id,r_date),
+KEY r_date_idx (r_date),
+KEY t1_idx01 (member_id_to)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(107924526,518491,'2011-05-01'),
+(107924527,518491,'2011-05-01'),
+(107924528,518491,'2011-05-01'),
+(107924529,518491,'2011-05-01'),
+(107924530,518491,'2011-05-01'),
+(107924531,518491,'2011-05-01'),
+(107924532,518491,'2011-05-01'),
+(107924534,518491,'2011-06-21'),
+(107924535,518491,'2011-06-21'),
+(107924536,518491,'2011-06-21'),
+(107924537,518491,'2011-06-21'),
+(107924538,518491,'2011-06-21'),
+(107924542,1601319,'2011-06-21'),
+(107924543,1601319,'2011-06-21'),
+(107924544,1601319,'2011-06-21'),
+(107924545,1601319,'2011-06-21'),
+(107924546,1601319,'2011-06-21'),
+(107924547,1601319,'2011-06-21'),
+(107924548,1601319,'2011-06-21'),
+(107924549,1601319,'2011-06-21'),
+(107924550,1601319,'2011-06-21');
+
+SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date =
+  '2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1;
+
+DROP TABLE t1;
+
+--echo # End of test  BUG#12713907

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-02-17 09:52:41 +0000
+++ b/sql/sql_select.cc	2012-04-18 05:55:01 +0000
@@ -13820,8 +13820,6 @@ check_reverse_order:
                                 join_read_first:join_read_last;
         tab->type=JT_NEXT;           // Read with index_first(), index_next()
 
-        if (table->covering_keys.is_set(best_key))
-          table->set_keyread(TRUE);
         table->file->ha_index_or_rnd_end();
         if (tab->join->select_options & SELECT_DESCRIBE)
         {

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.1 branch (chaithra.gopalareddy:3720 to 3721)Bug#12713907Chaithra Gopalareddy20 Apr