summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/heap.result8
-rw-r--r--mysql-test/r/innodb.result12
-rw-r--r--mysql-test/r/myisam.result12
-rw-r--r--mysql-test/r/range.result25
-rw-r--r--mysql-test/t/range.test28
-rw-r--r--sql/sql_select.cc23
6 files changed, 88 insertions, 20 deletions
diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index 969cb06e9fe..7f40dfa3a36 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -379,10 +379,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where
+1 SIMPLE t1 ref v v 13 const 10 Using where
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where
+1 SIMPLE t1 ref v v 13 const 10 Using where
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 1
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
@@ -602,10 +602,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 13 NULL # Using where
+1 SIMPLE t1 ref v v 13 const # Using where
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 13 NULL # Using where
+1 SIMPLE t1 ref v v 13 const # Using where
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 1
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 0d8de3a8e7d..39894348ba2 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1997,10 +1997,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 13 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 13 const # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 13 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 13 const # Using where; Using index
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 1
alter table t1 add key(v);
@@ -2188,10 +2188,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 303 NULL # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 303 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 303 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 303 const # Using where
@@ -2268,10 +2268,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 33 NULL # Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 33 NULL # Using where
+1 SIMPLE t1 ref v v 33 const # Using where
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 33 NULL # Using where
+1 SIMPLE t1 ref v v 33 const # Using where
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 33 const # Using where
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 0e5969ed83e..f484a937b27 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -802,10 +802,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 13 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 13 const # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 13 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 13 const # Using where; Using index
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 1
alter table t1 add key(v);
@@ -993,10 +993,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 303 NULL # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 303 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 303 NULL # Using where; Using index
+1 SIMPLE t1 ref v v 303 const # Using where; Using index
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 303 const # Using where
@@ -1073,10 +1073,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 33 NULL # Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 33 NULL # Using where
+1 SIMPLE t1 ref v v 33 const # Using where
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range v v 33 NULL # Using where
+1 SIMPLE t1 ref v v 33 const # Using where
explain select * from t1 where v='a';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 33 const # Using where
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 6adbea973df..69c150fc0b7 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1,4 +1,4 @@
-drop table if exists t1, t2;
+drop table if exists t1, t2, t3;
CREATE TABLE t1 (
event_date date DEFAULT '0000-00-00' NOT NULL,
type int(11) DEFAULT '0' NOT NULL,
@@ -787,3 +787,26 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range PRIMARY PRIMARY 8 NULL # Using where; Using index
drop view v1;
drop table t1;
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a varchar(10), filler char(200), key(a)) charset=binary;
+insert into t1 values ('a','');
+insert into t1 values ('a ','');
+insert into t1 values ('a ', '');
+insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), ''
+ from t3 A, t3 B, t3 C;
+create table t2 (a varchar(10), filler char(200), key(a));
+insert into t2 select * from t1;
+explain select * from t1 where a between 'a' and 'a ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 13 NULL # Using where
+explain select * from t1 where a = 'a' or a='a ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 13 NULL # Using where
+explain select * from t2 where a between 'a' and 'a ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a a 13 const # Using where
+explain select * from t2 where a = 'a' or a='a ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a a 13 const # Using where
+drop table t1,t2,t3;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index d8b3f5ef953..11c5e8d7bc5 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -3,7 +3,7 @@
#
--disable_warnings
-drop table if exists t1, t2;
+drop table if exists t1, t2, t3;
--enable_warnings
CREATE TABLE t1 (
@@ -600,3 +600,29 @@ explain select * from v1 where a between 3 and 4 and b between 1 and 2;
drop view v1;
drop table t1;
+
+# BUG#13455:
+create table t3 (a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (a varchar(10), filler char(200), key(a)) charset=binary;
+insert into t1 values ('a','');
+insert into t1 values ('a ','');
+insert into t1 values ('a ', '');
+insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), ''
+ from t3 A, t3 B, t3 C;
+
+create table t2 (a varchar(10), filler char(200), key(a));
+insert into t2 select * from t1;
+
+--replace_column 9 #
+explain select * from t1 where a between 'a' and 'a ';
+--replace_column 9 #
+explain select * from t1 where a = 'a' or a='a ';
+
+--replace_column 9 #
+explain select * from t2 where a between 'a' and 'a ';
+--replace_column 9 #
+explain select * from t2 where a = 'a' or a='a ';
+
+drop table t1,t2,t3;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5fc97385f15..1b1a35d2584 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2409,6 +2409,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
!field->table->maybe_null || field->null_ptr)
return; // Not a key. Skip it
exists_optimize= KEY_OPTIMIZE_EXISTS;
+ DBUG_ASSERT(num_values == 1);
}
else
{
@@ -2460,7 +2461,26 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
eq_func is NEVER true when num_values > 1
*/
if (!eq_func)
- return;
+ {
+ /*
+ Additional optimization: if we're processing
+ "t.key BETWEEN c1 AND c1" then proceed as if we were processing
+ "t.key = c1".
+ TODO: This is a very limited fix. A more generic fix is possible.
+ There are 2 options:
+ A) Make equality propagation code be able to handle BETWEEN
+ (including cases like t1.key BETWEEN t2.key AND t3.key)
+ B) Make range optimizer to infer additional "t.key = c" equalities
+ and use them in equality propagation process (see details in
+ OptimizerKBAndTodo)
+ */
+ if ((cond->functype() == Item_func::BETWEEN) &&
+ value[0]->eq(value[1], field->binary()))
+ eq_func= TRUE;
+ else
+ return;
+ }
+
if (field->result_type() == STRING_RESULT)
{
if ((*value)->result_type() != STRING_RESULT)
@@ -2487,7 +2507,6 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
}
}
}
- DBUG_ASSERT(num_values == 1);
/*
For the moment eq_func is always true. This slot is reserved for future
extensions where we want to remembers other things than just eq comparisons
View Lorry Controller Status