[Commits] Rev 4227: MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB in file:///home/psergey/dev2/5.5/

Sergey Petrunya psergey at askmonty.org
Tue Jul 22 14:52:52 EEST 2014


At file:///home/psergey/dev2/5.5/

------------------------------------------------------------
revno: 4227
revision-id: psergey at askmonty.org-20140722115249-e6z0cfrs3uktup21
parent: sanja at askmonty.org-20140715093734-42nrug10i0xw8hvb
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.5
timestamp: Tue 2014-07-22 15:52:49 +0400
message:
  MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB
  - Filesort has an optmization where it reads only columns that are 
    needed before the sorting is done.
  - When ref(_or_null) is picked by the join optimizer, it may remove parts
    of WHERE clause that are guaranteed to be true.
  - However, if we use quick select, we must put all of the range columns into the 
    read set. Not doing so will may cause us to fail to detect the end of the range.
=== added file 'mysql-test/r/order_by_innodb.result'
--- a/mysql-test/r/order_by_innodb.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/order_by_innodb.result	2014-07-22 11:52:49 +0000
@@ -0,0 +1,13 @@
+drop table if exists t0,t1,t2,t3;
+#
+# MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB
+#
+CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB;
+INSERT INTO t1 (a,c) VALUES
+(8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21),
+(20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28);
+SELECT  * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c;
+a	b	c	d
+8	NULL	9	NULL
+8	NULL	10	NULL
+DROP TABLE t1;

=== added file 'mysql-test/t/order_by_innodb.test'
--- a/mysql-test/t/order_by_innodb.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/order_by_innodb.test	2014-07-22 11:52:49 +0000
@@ -0,0 +1,23 @@
+#
+# ORDER BY handling (e.g. filesort) tests that require innodb
+#
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t0,t1,t2,t3;
+--enable_warnings
+
+--echo #
+--echo # MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB;
+
+INSERT INTO t1 (a,c) VALUES
+(8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21),
+(20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28);
+
+SELECT  * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c;
+
+DROP TABLE t1;
+

=== modified file 'sql/filesort.cc'
--- a/sql/filesort.cc	2014-03-27 21:26:58 +0000
+++ b/sql/filesort.cc	2014-07-22 11:52:49 +0000
@@ -642,6 +642,9 @@ static ha_rows find_all_keys(SORTPARAM *
   /* Temporary set for register_used_fields and register_field_in_read_map */
   sort_form->read_set= &sort_form->tmp_set;
   register_used_fields(param);
+  if (quick_select)
+    select->quick->add_used_key_part_to_set(sort_form->read_set);
+
   Item *sort_cond= !select ?  
                      0 : !select->pre_idx_push_select_cond ? 
                            select->cond : select->pre_idx_push_select_cond;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2014-06-05 15:18:35 +0000
+++ b/sql/opt_range.cc	2014-07-22 11:52:49 +0000
@@ -10538,6 +10538,7 @@ QUICK_RANGE_SELECT *get_quick_select_for
   if (quick->init())
     goto err;
   quick->records= records;
+  quick->max_used_key_length= ref->key_parts;
 
   if ((cp_buffer_from_ref(thd, table, ref) && thd->is_fatal_error) ||
       !(range= new(alloc) QUICK_RANGE()))
@@ -10547,7 +10548,7 @@ QUICK_RANGE_SELECT *get_quick_select_for
   range->min_length= range->max_length= ref->key_length;
   range->min_keypart_map= range->max_keypart_map=
     make_prev_keypart_map(ref->key_parts);
-  range->flag= (ref->key_length == key_info->key_length ? EQ_RANGE : 0);
+  range->flag= EQ_RANGE;
 
   if (!(quick->key_parts=key_part=(KEY_PART *)
 	alloc_root(&quick->alloc,sizeof(KEY_PART)*ref->key_parts)))
@@ -11755,6 +11756,60 @@ void QUICK_ROR_UNION_SELECT::add_keys_an
   }
 }
 
+
+void QUICK_RANGE_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set)
+{
+  for (uint i=0; i < max_used_key_length; i++)
+  {
+    bitmap_set_bit(col_set, key_parts[i].field->field_index);
+  }
+}
+
+
+void QUICK_GROUP_MIN_MAX_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set)
+{
+  for (uint i=0; i < max_used_key_length; i++)
+  {
+    bitmap_set_bit(col_set, index_info->key_part[i].field->field_index);
+  }
+}
+
+
+void QUICK_ROR_INTERSECT_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set)
+{
+  List_iterator_fast<QUICK_SELECT_WITH_RECORD> it(quick_selects);
+  QUICK_SELECT_WITH_RECORD *quick;
+  while ((quick= it++))
+  {
+    quick->quick->add_used_key_part_to_set(col_set);
+  }
+}
+
+
+void QUICK_INDEX_SORT_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set)
+{
+  QUICK_RANGE_SELECT *quick;
+  List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects);
+  while ((quick= it++))
+  {
+    quick->add_used_key_part_to_set(col_set);
+  }
+  if (pk_quick_select)
+    pk_quick_select->add_used_key_part_to_set(col_set);
+}
+
+
+void QUICK_ROR_UNION_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set)
+{
+  QUICK_SELECT_I *quick;
+  List_iterator_fast<QUICK_SELECT_I> it(quick_selects);
+
+  while ((quick= it++))
+  {
+    quick->add_used_key_part_to_set(col_set);
+  }
+}
+
 
 /*******************************************************************************
 * Implementation of QUICK_GROUP_MIN_MAX_SELECT

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2013-04-03 14:51:29 +0000
+++ b/sql/opt_range.h	2014-07-22 11:52:49 +0000
@@ -394,6 +394,13 @@ class QUICK_SELECT_I
     Returns a QUICK_SELECT with reverse order of to the index.
   */
   virtual QUICK_SELECT_I *make_reverse(uint used_key_parts_arg) { return NULL; }
+  
+  /*
+    Add the key columns used by the quick select into table's read set.
+    
+    This is used by an optimization in filesort.
+  */
+  virtual void add_used_key_part_to_set(MY_BITMAP *col_set)=0;
 };
 
 
@@ -484,6 +491,9 @@ class QUICK_RANGE_SELECT : public QUICK_
 #endif
   virtual void replace_handler(handler *new_file) { file= new_file; }
   QUICK_SELECT_I *make_reverse(uint used_key_parts_arg);
+
+  virtual void add_used_key_part_to_set(MY_BITMAP *col_set);
+
 private:
   /* Default copy ctor used by QUICK_SELECT_DESC */
   friend class TRP_ROR_INTERSECT;
@@ -644,6 +654,8 @@ class QUICK_INDEX_SORT_SELECT : public Q
   virtual int read_keys_and_merge()= 0;
   /* used to get rows collected in Unique */
   READ_RECORD read_record;
+
+  virtual void add_used_key_part_to_set(MY_BITMAP *col_set);
 };
 
 
@@ -719,6 +731,7 @@ class QUICK_ROR_INTERSECT_SELECT : publi
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
   bool is_keys_used(const MY_BITMAP *fields);
+  void add_used_key_part_to_set(MY_BITMAP *col_set);
 #ifndef DBUG_OFF
   void dbug_dump(int indent, bool verbose);
 #endif
@@ -798,6 +811,7 @@ class QUICK_ROR_UNION_SELECT : public QU
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
   bool is_keys_used(const MY_BITMAP *fields);
+  void add_used_key_part_to_set(MY_BITMAP *col_set);
 #ifndef DBUG_OFF
   void dbug_dump(int indent, bool verbose);
 #endif
@@ -940,6 +954,7 @@ class QUICK_GROUP_MIN_MAX_SELECT : publi
   bool unique_key_range() { return false; }
   int get_type() { return QS_TYPE_GROUP_MIN_MAX; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
+  void add_used_key_part_to_set(MY_BITMAP *col_set);
 #ifndef DBUG_OFF
   void dbug_dump(int indent, bool verbose);
 #endif



More information about the commits mailing list