diff options
| -rw-r--r-- | mysql-test/r/table_elim.result | 44 | ||||
| -rw-r--r-- | mysql-test/t/table_elim.test | 41 |
2 files changed, 85 insertions, 0 deletions
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 6a6e81f5bcf..d26959ee527 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -218,4 +218,48 @@ select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t 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; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 8a240e42e9f..6ef9731e212 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -175,5 +175,46 @@ select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.co 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; |