At file:///home/psergey/dev/maria-5.1-table-elim-r11-gcov/
------------------------------------------------------------
revno: 2744
revision-id: pser...@askmonty.org-20090825092750-mnmatimn7cxm4rl8
parent: pser...@askmonty.org-20090824191048-xev0gm1miw6ezz2r
committer: Sergey Petrunya <pser...@askmonty.org>
branch nick: maria-5.1-table-elim-r11-gcov
timestamp: Tue 2009-08-25 12:27:50 +0300
message:
MWL#17: Table elimination
- Add more testcases.
- Fix trivial compile failure
- Remove handling of "column IN (one_element)". This is converted to equality
elsewhere
=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result 2009-08-24 08:12:42 +0000
+++ b/mysql-test/r/table_elim.result 2009-08-25 09:27:50 +0000
@@ -307,3 +307,41 @@
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;
=== modified file 'mysql-test/t/table_elim.test'
--- a/mysql-test/t/table_elim.test 2009-08-24 08:12:42 +0000
+++ b/mysql-test/t/table_elim.test 2009-08-25 09:27:50 +0000
@@ -249,3 +249,30 @@
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;
+
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2009-08-24 19:10:48 +0000
+++ b/sql/mysqld.cc 2009-08-25 09:27:50 +0000
@@ -393,6 +393,8 @@
"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;
=== modified file 'sql/opt_table_elimination.cc'
--- a/sql/opt_table_elimination.cc 2009-08-24 19:10:48 +0000
+++ b/sql/opt_table_elimination.cc 2009-08-25 09:27:50 +0000
@@ -467,15 +467,6 @@
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 @@
{
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 @@
#ifndef DBUG_OFF
+/* purecov: begin inspected */
static
void dbug_print_deps(Func_dep_analyzer *fda)
{
@@ -1559,6 +1551,7 @@
DBUG_UNLOCK_FILE;
DBUG_VOID_RETURN;
}
+/* purecov: end */
#endif
/**
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
