diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/table_elim.result | 38 | ||||
| -rw-r--r-- | mysql-test/t/table_elim.test | 27 |
2 files changed, 65 insertions, 0 deletions
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 8ce03b06cab..4e53f4892bc 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -307,3 +307,41 @@ a b 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; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index cc4a179d825..04a02233586 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -249,3 +249,30 @@ select * from t1; 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; + |