maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

At file:///home/psergey/dev/maria-5.1-table-elim-r10/

------------------------------------------------------------
revno: 2732
revision-id: psergey@xxxxxxxxxxxx-20090817160724-fmmrmwp8zorzn82q
parent: psergey@xxxxxxxxxxxx-20090817150229-jy461nqbmk8nzhha
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
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;