#At file:///export/home/tmp/mysql2/opt-bug58816/ based on revid:nirbhay.choubey@stripped
3461 Olav Sandstaa 2011-01-06
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.
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
=== modified file 'mysql-test/include/icp_tests.inc'
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc 2010-12-13 15:22:45 +0000
+++ b/mysql-test/include/icp_tests.inc 2011-01-06 15:36:46 +0000
@@ -653,3 +653,28 @@
insert into t1 values ('',1);
select 1 from t1 where b <= 1 and a <> '';
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);
+INSERT INTO t1 VALUES (2,7);
+INSERT INTO t1 VALUES (3,6);
+INSERT INTO t1 VALUES (4,3);
+INSERT INTO t1 VALUES (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;
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2010-12-13 15:22:45 +0000
+++ b/mysql-test/r/innodb_icp.result 2011-01-06 15:36:46 +0000
@@ -598,5 +598,30 @@
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);
+INSERT INTO t1 VALUES (2,7);
+INSERT INTO t1 VALUES (3,6);
+INSERT INTO t1 VALUES (4,3);
+INSERT INTO t1 VALUES (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_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-06 15:36:46 +0000
@@ -598,5 +598,30 @@
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);
+INSERT INTO t1 VALUES (2,7);
+INSERT INTO t1 VALUES (3,6);
+INSERT INTO t1 VALUES (4,3);
+INSERT INTO t1 VALUES (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 2010-12-13 15:22:45 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2011-01-06 15:36:46 +0000
@@ -597,5 +597,30 @@
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);
+INSERT INTO t1 VALUES (2,7);
+INSERT INTO t1 VALUES (3,6);
+INSERT INTO t1 VALUES (4,3);
+INSERT INTO t1 VALUES (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;
set default_storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2010-12-13 15:22:45 +0000
+++ b/mysql-test/r/myisam_icp.result 2011-01-06 15:36:46 +0000
@@ -596,4 +596,29 @@
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);
+INSERT INTO t1 VALUES (2,7);
+INSERT INTO t1 VALUES (3,6);
+INSERT INTO t1 VALUES (4,3);
+INSERT INTO t1 VALUES (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_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-06 15:36:46 +0000
@@ -596,4 +596,29 @@
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);
+INSERT INTO t1 VALUES (2,7);
+INSERT INTO t1 VALUES (3,6);
+INSERT INTO t1 VALUES (4,3);
+INSERT INTO t1 VALUES (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 2010-12-13 15:22:45 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2011-01-06 15:36:46 +0000
@@ -595,4 +595,29 @@
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);
+INSERT INTO t1 VALUES (2,7);
+INSERT INTO t1 VALUES (3,6);
+INSERT INTO t1 VALUES (4,3);
+INSERT INTO t1 VALUES (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 'sql/handler.cc'
--- a/sql/handler.cc 2010-12-16 19:36:57 +0000
+++ b/sql/handler.cc 2011-01-06 15:36:46 +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,6 +5833,11 @@
free_io_cache(table);
/* reset the bitmaps to point to defaults */
table->default_column_bitmaps();
+ /* 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);
}
Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20110106153646-7d8nmagr5m5jjbb0.bundle
