diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect.result | 11 | ||||
| -rw-r--r-- | mysql-test/t/join.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 9 |
3 files changed, 21 insertions, 1 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 564e102c874..b3ff4f94ced 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -294,6 +294,10 @@ patient_uq clinic_uq 1 1 1 2 2 2 +explain select * from t6 where exists (select * from t7 where uq = clinic_uq); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); Column: 'a' in field list is ambiguous drop table if exists t1,t2,t3; @@ -1113,3 +1117,10 @@ select -10 IN (select a from t1 FORCE INDEX (indexa)); -10 IN (select a from t1 FORCE INDEX (indexa)) NULL drop table t1; +create table t1 (id int not null auto_increment primary key, salary int, key(salary)); +insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); +explain SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref salary salary 5 const 1 Using where +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 7840e128ac7..5fd96d6687c 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -115,7 +115,7 @@ drop table t1, t2; create table t1 (a int primary key); insert into t1 values(1),(2); select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a); ---replace_result "31 tables" "XX tables" "63 tables" "XX tables" +--replace_result "31 tables" "XX tables" "62 tables" "XX tables" --error 1116 select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a); drop table t1; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index dffe6ec79c2..30cb7b05d74 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -125,6 +125,7 @@ create table t7( uq int primary key, name char(25)); insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); insert into t6 values (1,1),(1,2),(2,2),(1,3); select * from t6 where exists (select * from t7 where uq = clinic_uq); +explain select * from t6 where exists (select * from t7 where uq = clinic_uq); # not unique fields -- error 1052 @@ -703,3 +704,11 @@ create table t1 (a int, unique index indexa (a)); insert into t1 values (-1), (-4), (-2), (NULL); select -10 IN (select a from t1 FORCE INDEX (indexa)); drop table t1; + +# +# Test optimization for sub selects +# +create table t1 (id int not null auto_increment primary key, salary int, key(salary)); +insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); +explain SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); +drop table t1; |