MWL#17: Table elimination

- More testcases

1 files changed, 44 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;