List:Commits« Previous MessageNext Message »
From:Olav.Sandstaa Date:October 19 2010 12:55pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (Olav.Sandstaa:3264) Bug#52660
View as plain text  
#At file:///home/olav/mysql/develop2/opt-bug52660/ based on revid:roy.lyseng@stripped

 3264 Olav.Sandstaa@stripped	2010-10-19
      Fix for Bug#52660 Perf. regr. using ICP for MyISAM on range queries on an index containing TEXT
      
      When an index condition is pushed down to MyISAM the content of (most
      of) the index entry will be read into the record buffer. This will be
      used for evaluating the index condition and the range check in case of
      a range scan. When reading the content of the index entry all fields
      will be read with the exception of BLOB fields. This is not a problem
      for evaluating the index condition itself since the server will not
      push down an index condition that contains a BLOB field. But it is a
      problem for the range evaluation that is performed as part of the ICP
      implementation in MyISAM since this might be done using a "non-read"
      BLOB value.
      
      A simplified version of the query given in the bug report:
      
      SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
      
      it will be done as a range scan and following happens :
      
      1. MyISAM locates the first record satisfying the lower range and this
      record will be read into the record buffer. So after this the
      content of the record buffer for the BLOB field will have the value:
      
         "c-1004=w"
      
      2. When the server requests the next record in the range MyISAM will
      due to ICP being used read in the index entry (but not including the
      BLOB field), so when the end of range check is done it will evaluate
      the upper range value (c1 <= 'c-1006=w') against the value of the
      first record read (ie. it will evaluate to true) and since the ICP
      condition (i1 > 1) evaluates to false it will skip this record and go
      to the next record. This creates a loop that instead of stopping at
      the upper range value will loop until it has read the complete index
      (or until the ICP condition evaluates to true).
      
      So in the example in the bug report about 10 million index entries
      will be read and evaluated instead of just 3 or 4.
      
      The fix for this is to let MyISAM reject pushed index conditions
      whenever the index used for ICP contains a BLOB field.
      
      Note that this change disables ICP from being used by MyISAM in some
      cases where it actually was safe to use it. The pushed index condition
      will be reject in all cases where the index contains a BLOB field also
      when the BLOB field is not used in any range evaluation. The reason for
      this is at the time the server pushes the index condition MyISAM does
      not know whether the actually data access will be a range scan with an
      upper range value or not.
      
      The patch contains a simplified version of the original test
      case. This test case only contains 100 records which is too little to
      show any performance regression. The important point with the test
      case is that without the fix the explain will show "Using index
      condition" while with the fix it will say "Using where".
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#52660 Perf. regr. using ICP for MyISAM on range 
        queries on an index containing TEXT.
     @ mysql-test/r/innodb_icp.result
        Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT.
     @ mysql-test/r/innodb_icp_all.result
        Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT.
     @ mysql-test/r/innodb_icp_none.result
        Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT.
     @ mysql-test/r/myisam_icp.result
        Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT.
     @ mysql-test/r/myisam_icp_all.result
        Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT.
     @ mysql-test/r/myisam_icp_none.result
        Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT.
     @ mysql-test/r/null_key_all.result
        Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT". With this fix ICP will no longer be
        used for MyISAM if the index for the pushed index condition contains a BLOB field.
     @ mysql-test/r/null_key_icp.result
        Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT". With this fix ICP will no longer be
        used for MyISAM if the index for the pushed index condition contains a BLOB field.
     @ mysql-test/r/order_by_all.result
        Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT". With this fix ICP will no longer be
        used for MyISAM if the index for the pushed index condition contains a BLOB field.
     @ mysql-test/r/order_by_icp_mrr.result
        Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM 
        on range queries on an index containing TEXT". With this fix ICP will no longer be
        used for MyISAM if the index for the pushed index condition contains a BLOB field.
     @ storage/myisam/ha_myisam.cc
        Change the ICP implementation for MyISAM to reject any pushed 
        index conditions if the index contains a BLOB field.
        
        The reason for this change is that MyISAM does not read in
        BLOB fields from the index entry as part of the ICP evaluation.
        If it is a range scan where the BLOB field is used as part of the
        upper range limit then the range evaluation will be done against
        a previously read BLOB value and evaluate to true. As a consequence
        MyISAM will fail to detect that it has reached the end of a range
        interval and continue to read the complete index.

    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
      mysql-test/r/null_key_all.result
      mysql-test/r/null_key_icp.result
      mysql-test/r/order_by_all.result
      mysql-test/r/order_by_icp_mrr.result
      storage/myisam/ha_myisam.cc
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2010-06-20 07:16:27 +0000
+++ b/mysql-test/include/icp_tests.inc	2010-10-19 12:55:25 +0000
@@ -402,3 +402,28 @@ ORDER BY f1;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 
+--echo #              an index containing TEXT"
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+
+CREATE TABLE t3 (
+  c1 TINYTEXT NOT NULL,
+  i1 INT NOT NULL,
+  KEY (c1(6),i1)
+);
+
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+
+DROP TABLE t1, t2, t3;

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2010-08-23 11:28:58 +0000
+++ b/mysql-test/r/innodb_icp.result	2010-10-19 12:55:25 +0000
@@ -368,5 +368,26 @@ ORDER BY f1;
 f1
 b
 DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 
+#              an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	c1	c1	12	NULL	2	Using index condition; Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
 set 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-08-23 11:28:58 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2010-10-19 12:55:25 +0000
@@ -368,5 +368,26 @@ ORDER BY f1;
 f1
 b
 DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 
+#              an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	c1	c1	12	NULL	2	Using index condition; Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
 set 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-09-01 13:46:08 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2010-10-19 12:55:25 +0000
@@ -367,5 +367,26 @@ ORDER BY f1;
 f1
 b
 DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 
+#              an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	c1	c1	12	NULL	2	Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
 set 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-06-18 08:45:53 +0000
+++ b/mysql-test/r/myisam_icp.result	2010-10-19 12:55:25 +0000
@@ -366,4 +366,25 @@ ORDER BY f1;
 f1
 b
 DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 
+#              an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	c1	c1	12	NULL	2	Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result	2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/myisam_icp_all.result	2010-10-19 12:55:25 +0000
@@ -366,4 +366,25 @@ ORDER BY f1;
 f1
 b
 DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 
+#              an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	c1	c1	12	NULL	2	Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2010-10-19 12:55:25 +0000
@@ -365,4 +365,25 @@ ORDER BY f1;
 f1
 b
 DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on 
+#              an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	c1	c1	12	NULL	2	Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/null_key_all.result'
--- a/mysql-test/r/null_key_all.result	2010-08-26 21:32:48 +0000
+++ b/mysql-test/r/null_key_all.result	2010-10-19 12:55:25 +0000
@@ -77,13 +77,13 @@ insert into t2 select * from t1;
 alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
 explain select * from t1 where a is null and b = 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a is null and b = 2 and c=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a is null and b = 7 and c=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a=2 and b = 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	1	Using where
@@ -92,25 +92,25 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	5	NULL	5	Using index condition; Using where
+1	SIMPLE	t1	range	a,b	a	5	NULL	5	Using where
 explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a,b	a	5	const	4	Using index condition; Using where
+1	SIMPLE	t1	ref_or_null	a,b	a	5	const	4	Using where
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a > 1 and a < 3 limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	1	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where
 explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	5	NULL	4	Using index condition; Using where
+1	SIMPLE	t1	range	a,b	a	5	NULL	4	Using where
 explain select * from t1 where a > 8 and a < 9;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	1	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where
 explain select * from t1 where b like "6%";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	b	b	12	NULL	1	Using where

=== modified file 'mysql-test/r/null_key_icp.result'
--- a/mysql-test/r/null_key_icp.result	2010-09-01 13:46:08 +0000
+++ b/mysql-test/r/null_key_icp.result	2010-10-19 12:55:25 +0000
@@ -77,13 +77,13 @@ insert into t2 select * from t1;
 alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
 explain select * from t1 where a is null and b = 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a is null and b = 2 and c=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a is null and b = 7 and c=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a=2 and b = 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	1	Using where
@@ -92,25 +92,25 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	5	NULL	5	Using index condition; Using where
+1	SIMPLE	t1	range	a,b	a	5	NULL	5	Using where
 explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a,b	a	5	const	4	Using index condition; Using where
+1	SIMPLE	t1	ref_or_null	a,b	a	5	const	4	Using where
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	a	5	const	3	Using index condition; Using where
+1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
 explain select * from t1 where a > 1 and a < 3 limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	1	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where
 explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	5	NULL	4	Using index condition; Using where
+1	SIMPLE	t1	range	a,b	a	5	NULL	4	Using where
 explain select * from t1 where a > 8 and a < 9;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	1	Using index condition
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where
 explain select * from t1 where b like "6%";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	b	b	12	NULL	1	Using where

=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result	2010-09-27 13:20:24 +0000
+++ b/mysql-test/r/order_by_all.result	2010-10-19 12:55:25 +0000
@@ -610,7 +610,7 @@ FieldKey	LongVal	StringVal
 1	2	1
 EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	StringField	StringField	38	NULL	4	Using index condition; Using filesort
+1	SIMPLE	t1	range	StringField	StringField	38	NULL	4	Using where; Using filesort
 SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
 FieldKey	LongVal	StringVal
 3	1	2

=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2010-09-30 14:57:06 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2010-10-19 12:55:25 +0000
@@ -610,7 +610,7 @@ FieldKey	LongVal	StringVal
 1	2	1
 EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	StringField	StringField	38	NULL	4	Using index condition; Using filesort
+1	SIMPLE	t1	range	StringField	StringField	38	NULL	4	Using where; Using filesort
 SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
 FieldKey	LongVal	StringVal
 3	1	2

=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc	2010-07-13 17:29:44 +0000
+++ b/storage/myisam/ha_myisam.cc	2010-10-19 12:55:25 +0000
@@ -2150,12 +2150,37 @@ ha_rows ha_myisam::multi_range_read_info
 
 Item *ha_myisam::idx_cond_push(uint keyno_arg, Item* idx_cond_arg)
 {
-  pushed_idx_cond_keyno= keyno_arg;
-  pushed_idx_cond= idx_cond_arg;
-  in_range_check_pushed_down= TRUE;
-  if (active_index == pushed_idx_cond_keyno)
-    mi_set_index_cond_func(file, index_cond_func_myisam, this);
-  return NULL;
+  /*
+    Check if the key contains a blob field. If it does then MyISAM
+    should not accept the pushed index condition since MyISAM will not
+    read the blob field from the index entry during evaluation of the
+    pushed index condition and the BLOB field might be part of the
+    range evaluation done by the ICP code.
+  */
+  bool key_contains_blob= false;
+  const KEY *key= &table->key_info[keyno_arg];
+
+  for (uint k= 0; k < key->key_parts; ++k)
+  {
+    const KEY_PART_INFO *key_part= &key->key_part[k];
+    if (key_part->key_part_flag & HA_BLOB_PART)
+    {
+      key_contains_blob= true;
+      break;
+    }
+  }
+
+  if (!key_contains_blob)
+  {
+    pushed_idx_cond_keyno= keyno_arg;
+    pushed_idx_cond= idx_cond_arg;
+    in_range_check_pushed_down= TRUE;
+    if (active_index == pushed_idx_cond_keyno)
+      mi_set_index_cond_func(file, index_cond_func_myisam, this);
+    return NULL;
+  }
+
+  return idx_cond_arg;
 }
 
 


Attachment: [text/bzr-bundle] bzr/olav.sandstaa@sun.com-20101019125525-lqxxaalnytunyv25.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (Olav.Sandstaa:3264) Bug#52660Olav.Sandstaa19 Oct
  • Re: bzr commit into mysql-next-mr-bugfixing branch(Olav.Sandstaa:3264) Bug#52660Tor Didriksen27 Oct
    • Re: bzr commit into mysql-next-mr-bugfixing branch (Olav.Sandstaa:3264)Bug#52660Olav Sandstaa27 Oct
    • Re: bzr commit into mysql-next-mr-bugfixing branch(Olav.Sandstaa:3264) Bug#52660Olav SandstÃ¥1 Nov
      • Re: bzr commit into mysql-next-mr-bugfixing branch(Olav.Sandstaa:3264) Bug#52660Tor Didriksen2 Nov
  • Re: bzr commit into mysql-next-mr-bugfixing branch (Olav.Sandstaa:3264)Bug#52660Jorgen Loland28 Oct
    • Re: bzr commit into mysql-next-mr-bugfixing branch(Olav.Sandstaa:3264) Bug#52660Olav SandstÃ¥1 Nov
      • Re: bzr commit into mysql-next-mr-bugfixing branch (Olav.Sandstaa:3264)Bug#52660Jorgen Loland2 Nov