diff options
| -rw-r--r-- | mysql-test/r/table_elim.result | 38 | ||||
| -rw-r--r-- | mysql-test/t/table_elim.test | 27 | ||||
| -rw-r--r-- | sql/mysqld.cc | 2 | ||||
| -rw-r--r-- | sql/opt_table_elimination.cc | 13 |
4 files changed, 70 insertions, 10 deletions
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 8ce03b06cab..4e53f4892bc 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -307,3 +307,41 @@ a b select * from t2; a b drop table t1, t2; +# +# Tests with various edge-case ON expressions +# +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); +create table t2 (pk int primary key, b int) +as select a as pk, a as b from t1 where a in (1,2); +create table t3 (pk int primary key, b int) +as select a as pk, a as b from t1 where a in (1,3); +explain select t1.a from t1 left join t2 on t2.pk=t1.a and t2.b<t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +explain select t1.a from t1 left join t2 on t2.pk between 10 and 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +explain select t1.a from t1 left join t2 on t2.pk between 10 and 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select t1.a from t1 left join t2 on t2.pk in (10); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select t1.a from t1 left join t2 on t2.pk in (t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +drop table t1,t2,t3; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index cc4a179d825..04a02233586 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -249,3 +249,30 @@ select * from t1; select * from t2; drop table t1, t2; +--echo # +--echo # Tests with various edge-case ON expressions +--echo # +create table t1 (a int, b int, c int, d int); +insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); + +create table t2 (pk int primary key, b int) + as select a as pk, a as b from t1 where a in (1,2); + +create table t3 (pk1 int, pk2 int, b int, unique(pk1,pk2)); +insert into t3 select a as pk1, a as pk2, a as b from t1 where a in (1,3); + +explain select t1.a from t1 left join t2 on t2.pk=t1.a and t2.b<t1.b; +explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b; +explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a; + +explain select t1.a from t1 left join t2 on t2.pk between 10 and 20; +explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5; +explain select t1.a from t1 left join t2 on t2.pk between 10 and 10; + +explain select t1.a from t1 left join t2 on t2.pk in (10); +explain select t1.a from t1 left join t2 on t2.pk in (t1.a); + +explain select t1.a from t1 left join t2 on TRUE; + +drop table t1,t2,t3; + diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 8744c7316f7..14a45bff711 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -393,6 +393,8 @@ static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "index_merge_intersection=on" #ifndef DBUG_OFF ",table_elimination=on"; +#else + ; #endif static char *mysqld_user, *mysqld_chroot, *log_error_file_ptr; static char *opt_init_slave, *language_ptr, *opt_init_connect; diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc index 091538fd692..d59200806a8 100644 --- a/sql/opt_table_elimination.cc +++ b/sql/opt_table_elimination.cc @@ -467,15 +467,6 @@ void build_eq_mods_for_cond(Func_dep_analyzer *fda, Equality_module **eq_mod, Item **args= cond_func->arguments(); switch (cond_func->functype()) { - case Item_func::IN_FUNC: - { - if (cond_func->argument_count() == 2) - { - add_eq_mod(fda, eq_mod, *and_level, cond_func, args[0], args[1]); - add_eq_mod(fda, eq_mod, *and_level, cond_func, args[1], args[0]); - } - break; - } case Item_func::BETWEEN: { Item *fld; @@ -837,7 +828,7 @@ static Table_value *get_table_value(Func_dep_analyzer *fda, TABLE *table) { Table_value *tbl_dep; if (!(tbl_dep= new Table_value(table))) - return NULL; + return NULL; /* purecov: inspected */ Key_module **key_list= &(tbl_dep->keys); /* Add dependencies for unique keys */ @@ -1499,6 +1490,7 @@ static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl) #ifndef DBUG_OFF +/* purecov: begin inspected */ static void dbug_print_deps(Func_dep_analyzer *fda) { @@ -1559,6 +1551,7 @@ void dbug_print_deps(Func_dep_analyzer *fda) DBUG_UNLOCK_FILE; DBUG_VOID_RETURN; } +/* purecov: end */ #endif /** |