At file:///home/psergey/dev/maria-5.1-table-elim-r10/
------------------------------------------------------------
revno: 2732
revision-id: [email protected]
parent: [email protected]
committer: Sergey Petrunya <[email protected]>
branch nick: maria-5.1-table-elim-r10
timestamp: Mon 2009-08-17 19:07:24 +0300
message:
MWL#17: Table elimination
- More testcases
=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result 2009-08-17 15:02:29 +0000
+++ b/mysql-test/r/table_elim.result 2009-08-17 16:07:24 +0000
@@ -218,4 +218,48 @@
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1
+drop table t1, t2, t3;
+#
+# Check things that look like functional dependencies but really are not
+#
+create table t1 (a char(10) character set latin1 collate latin1_general_ci
primary key);
+insert into t1 values ('foo');
+insert into t1 values ('bar');
+create table t2 (a char(10) character set latin1 collate latin1_general_cs
primary key);
+insert into t2 values ('foo');
+insert into t2 values ('FOO');
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a='foo' collate
latin1_general_ci;
+id select_type table type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using
index
+1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using
index
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=t1.a collate
latin1_general_ci;
+id select_type table type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using
index
+1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using
index
+drop table t1,t2;
+create table t1 (a int primary key);
+insert into t1 values (1),(2);
+create table t2 (a char(10) primary key);
+insert into t2 values ('1'),('1.0');
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=1;
+id select_type table type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using
index
+1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using
index
+this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using
index
+1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using
index
+drop table t1, t2;
+create table t1 (a char(10) primary key);
+insert into t1 values ('foo'),('bar');
+create table t2 (a char(10), unique key(a(2)));
+insert into t2 values ('foo'),('bar');
+explain select t1.* from t1 left join t2 on t2.a=t1.a;
+id select_type table type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using
index
+1 SIMPLE t2 ref a a 3 test.t1.a 2
drop table t1, t2;
=== modified file 'mysql-test/t/table_elim.test'
--- a/mysql-test/t/table_elim.test 2009-08-17 15:02:29 +0000
+++ b/mysql-test/t/table_elim.test 2009-08-17 16:07:24 +0000
@@ -175,5 +175,46 @@
explain
select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on
t2.pk=t1.col;
+drop table t1, t2, t3;
+
+--echo #
+--echo # Check things that look like functional dependencies but really are not
+--echo #
+
+create table t1 (a char(10) character set latin1 collate latin1_general_ci
primary key);
+insert into t1 values ('foo');
+insert into t1 values ('bar');
+
+create table t2 (a char(10) character set latin1 collate latin1_general_cs
primary key);
+insert into t2 values ('foo');
+insert into t2 values ('FOO');
+
+-- echo this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a='foo' collate
latin1_general_ci;
+
+-- echo this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=t1.a collate
latin1_general_ci;
+drop table t1,t2;
+
+create table t1 (a int primary key);
+insert into t1 values (1),(2);
+create table t2 (a char(10) primary key);
+insert into t2 values ('1'),('1.0');
+-- echo this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=1;
+-- echo this must not use table elimination:
+explain select t1.* from t1 left join t2 on t2.a=t1.a;
+
+drop table t1, t2;
+# partial unique keys do not work at the moment, although they are able to
+# provide one-match guarantees:
+create table t1 (a char(10) primary key);
+insert into t1 values ('foo'),('bar');
+
+create table t2 (a char(10), unique key(a(2)));
+insert into t2 values ('foo'),('bar');
+
+explain select t1.* from t1 left join t2 on t2.a=t1.a;
+
drop table t1, t2;
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
