List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:January 13 2011 7:33am
Subject:bzr commit into mysql-trunk branch (olav.sandstaa:3493) Bug#58816
View as plain text  
#At file:///export/home/tmp/mysql2/opt-bug58816/ based on revid:bjorn.munch@stripped

 3493 Olav Sandstaa	2011-01-13
      Fix for Bug#58816 Extra temporary duplicate rows in result set when 
                        switching ICP off.
      
      The wrong result was caused by that the handler object was in an
      inconsistent state when running the query. The same handler object
      was previously used by an explain for the same query. 
      
      When doing the explain the optimizer pushed down an index condition to
      the handler. This set information about the pushed index condition
      as well as setting the member variable in_range_check_pushed_down to
      true. The inconsistency that this resulted in was that when the
      explain statement completed and the handler object was ready for
      re-use the information about the pushed index condition was reset but
      the in_range_check_pushed_down was still true.
      
      When executing the same query (after disabling index condition
      pushdown) using the same handler object with the
      in_range_check_pushed_down still being true caused that neither the
      server nor the storage engine were performing the range check. This
      resulted in that we would read to the end of the table for the first
      range instead of stopping at the end of range criterion.
      
      The fix for this problem is to add code to handler::ha_reset() to
      to reset both information about the pushed index condition and the
      in_range_check_pushed_down. This method is called when a
      statement closes its tables and the handler object is made ready
      for reuse.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#58816 Extra temporary duplicate rows in result
        set when switching ICP off.
     @ mysql-test/r/innodb_icp.result
        Test case for Bug#58816 Extra temporary duplicate rows in result
        set when switching ICP off.
     @ mysql-test/r/innodb_icp_all.result
        Test case for Bug#58816 Extra temporary duplicate rows in result
        set when switching ICP off.
     @ mysql-test/r/innodb_icp_none.result
        Test case for Bug#58816 Extra temporary duplicate rows in result
        set when switching ICP off.
     @ mysql-test/r/myisam_icp.result
        Test case for Bug#58816 Extra temporary duplicate rows in result
        set when switching ICP off.
     @ mysql-test/r/myisam_icp_all.result
        Test case for Bug#58816 Extra temporary duplicate rows in result
        set when switching ICP off.
     @ mysql-test/r/myisam_icp_none.result
        Test case for Bug#58816 Extra temporary duplicate rows in result
        set when switching ICP off.
     @ sql/handler.cc
        When an explain is run on a statement using index condition pushdown
        it will leave the handler object in an inconsistent state where
        the information about the pushed index condition is reset while
        the value of in_range_check_pushed_down is still true. If this
        handler object is later re-used for a range query it might lead
        to more records being found due to the range condition is not
        evaluated neither by the server nor the storage engine.
        
        The fix for this is to extend handler::ha_reset() to reset
        both the information about pushed index condition and the
        in_range_check_pushed_down.
     @ storage/innobase/handler/ha_innodb.cc
        Move resetting of the handler's ICP member variables to handler::ha_reset().
     @ storage/myisam/ha_myisam.cc
        Move resetting of the handler's ICP member variables to handler::ha_reset().

    modified:
      mysql-test/include/icp_tests.inc
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_all.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_all.result
      mysql-test/r/myisam_icp_none.result
      sql/handler.cc
      storage/innobase/handler/ha_innodb.cc
      storage/myisam/ha_myisam.cc
=== modified file 'mysql-test/include/icp_tests.inc'

=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2011-01-10 09:04:20 +0000
+++ b/mysql-test/include/icp_tests.inc	2011-01-13 07:33:03 +0000
@@ -655,6 +655,27 @@
 drop table t1;
 
 --echo #
+--echo # Bug #58816 "Extra temporary duplicate rows in result set when 
+--echo #             switching ICP off"
+--echo #
+
+CREATE TABLE t1 (
+  pk INT NOT NULL,
+  c1 INT NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+
+DROP TABLE t1;
+
+--echo #
 --echo # Bug#58837: ICP crash or valgrind error due to uninitialized 
 --echo #            value in innobase_index_cond
 --echo #

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2011-01-10 09:04:20 +0000
+++ b/mysql-test/r/innodb_icp.result	2011-01-13 07:33:03 +0000
@@ -599,6 +599,27 @@
 1
 drop table t1;
 #
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
+#
 # Bug#58837: ICP crash or valgrind error due to uninitialized 
 #            value in innobase_index_cond
 #

=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result	2010-12-13 15:22:45 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2011-01-13 07:33:03 +0000
@@ -598,5 +598,26 @@
 select 1 from t1 where b <= 1 and a <> '';
 1
 drop table t1;
+#
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2011-01-10 09:04:20 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2011-01-13 07:33:03 +0000
@@ -598,6 +598,27 @@
 1
 drop table t1;
 #
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
+#
 # Bug#58837: ICP crash or valgrind error due to uninitialized 
 #            value in innobase_index_cond
 #

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-01-10 09:04:20 +0000
+++ b/mysql-test/r/myisam_icp.result	2011-01-13 07:33:03 +0000
@@ -597,6 +597,27 @@
 1
 drop table t1;
 #
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	5	Using where
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
+#
 # Bug#58837: ICP crash or valgrind error due to uninitialized 
 #            value in innobase_index_cond
 #

=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result	2010-12-13 15:22:45 +0000
+++ b/mysql-test/r/myisam_icp_all.result	2011-01-13 07:33:03 +0000
@@ -596,4 +596,25 @@
 select 1 from t1 where b <= 1 and a <> '';
 1
 drop table t1;
+#
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	5	Using where
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2011-01-10 09:04:20 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2011-01-13 07:33:03 +0000
@@ -596,6 +596,27 @@
 1
 drop table t1;
 #
+# Bug #58816 "Extra temporary duplicate rows in result set when 
+#             switching ICP off"
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+c1 INT NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
+EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	5	Using where
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+SELECT pk, c1 FROM t1 WHERE pk <> 3;
+pk	c1
+1	9
+2	7
+4	3
+5	1
+DROP TABLE t1;
+#
 # Bug#58837: ICP crash or valgrind error due to uninitialized 
 #            value in innobase_index_cond
 #

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2011-01-11 11:45:02 +0000
+++ b/sql/handler.cc	2011-01-13 07:33:03 +0000
@@ -5820,7 +5820,7 @@
 */
 int handler::ha_reset()
 {
-  DBUG_ENTER("ha_reset");
+  DBUG_ENTER("handler::ha_reset");
   /* Check that we have called all proper deallocation functions */
   DBUG_ASSERT((uchar*) table->def_read_set.bitmap +
               table->s->column_bitmap_size ==
@@ -5833,7 +5833,13 @@
   free_io_cache(table);
   /* reset the bitmaps to point to defaults */
   table->default_column_bitmaps();
+  /* Reset information about pushed engine conditions */
   pushed_cond= NULL;
+  /* Reset information about pushed index conditions */
+  pushed_idx_cond= NULL;
+  pushed_idx_cond_keyno= MAX_KEY;
+  in_range_check_pushed_down= false;
+ 
   const int retval= reset();
   DBUG_RETURN(retval);
 }

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	2011-01-07 12:02:31 +0000
+++ b/storage/innobase/handler/ha_innodb.cc	2011-01-13 07:33:03 +0000
@@ -2251,8 +2251,6 @@
 	/* Reset index condition pushdown state */
 	prebuilt->idx_cond = NULL;
 	prebuilt->idx_cond_n_cols = 0;
-	pushed_idx_cond = NULL;
-	pushed_idx_cond_keyno = MAX_KEY;
 }
 
 /*****************************************************************//**

=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc	2011-01-11 09:09:21 +0000
+++ b/storage/myisam/ha_myisam.cc	2011-01-13 07:33:03 +0000
@@ -1818,8 +1818,9 @@
 
 int ha_myisam::reset(void)
 {
-  pushed_idx_cond= NULL;
-  pushed_idx_cond_keyno= MAX_KEY;
+  /* Reset MyISAM specific part for index condition pushdown */
+  DBUG_ASSERT(pushed_idx_cond == NULL);
+  DBUG_ASSERT(pushed_idx_cond_keyno == MAX_KEY);
   mi_set_index_cond_func(file, NULL, 0);
   ds_mrr.dsmrr_close();
   return mi_reset(file);


Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20110113073303-xtc6rhbo1g9iaiqm.bundle
Thread
bzr commit into mysql-trunk branch (olav.sandstaa:3493) Bug#58816Olav Sandstaa13 Jan