diff options
| author | unknown <holyfoot/hf@hfmain.(none)> | 2007-05-07 15:59:25 +0500 |
|---|---|---|
| committer | unknown <holyfoot/hf@hfmain.(none)> | 2007-05-07 15:59:25 +0500 |
| commit | 42c714fd5f86a1dee317deb91f02994f97dffcef (patch) | |
| tree | e059afb3677147e3bb4910d19fa4ded9f438aa17 | |
| parent | 2e1ce777814084a896841d789e43ce73bcdff03c (diff) | |
| parent | 17da0553bbbe388ae0ffcfcd915a9a1a6d70259f (diff) | |
| download | mariadb-git-42c714fd5f86a1dee317deb91f02994f97dffcef.tar.gz | |
Merge bk@192.168.21.1:mysql-5.1
into mysql.com:/d2/hf/mrg/mysql-5.1-opt
sql/ha_ndbcluster.cc:
Auto merged
sql/item_func.cc:
Auto merged
49 files changed, 516 insertions, 241 deletions
diff --git a/mysql-test/r/archive_gis.result b/mysql-test/r/archive_gis.result index 6f8175bd609..71eeb063d59 100644 --- a/mysql-test/r/archive_gis.result +++ b/mysql-test/r/archive_gis.result @@ -402,7 +402,7 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache 1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 647c210b7d0..c538d8d55e1 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -351,6 +351,12 @@ DROP TABLE t1; select isnull(date(NULL)), isnull(cast(NULL as DATE)); isnull(date(NULL)) isnull(cast(NULL as DATE)) 1 1 +SELECT CAST(cast('01-01-01' as date) AS UNSIGNED); +CAST(cast('01-01-01' as date) AS UNSIGNED) +20010101 +SELECT CAST(cast('01-01-01' as date) AS SIGNED); +CAST(cast('01-01-01' as date) AS SIGNED) +20010101 End of 4.1 tests select cast('1.2' as decimal(3,2)); cast('1.2' as decimal(3,2)) diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index 11b15ed7675..2eced78b07a 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -611,11 +611,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 41810 +1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index period period 4 NULL 41810 +1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period @@ -1434,7 +1434,7 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index d76d89d5630..3abba528164 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1454,7 +1454,7 @@ insert into t1 values ('123'), ('456'); explain select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort; Using join cache 1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; substr(Z.a,-1) a diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 3a098308b49..750d542d14d 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -57,7 +57,7 @@ a b a b 3 c 3 c explain select * from t1 as x1, (select * from t1) as x2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY x1 ALL NULL NULL NULL NULL 4 +1 PRIMARY x1 ALL NULL NULL NULL NULL 4 Using join cache 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 2 DERIVED t1 ALL NULL NULL NULL NULL 4 drop table if exists t2,t3; @@ -188,13 +188,13 @@ pla_id test 105 3 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY m2 ALL NULL NULL NULL NULL 9 +1 PRIMARY m2 ALL NULL NULL NULL NULL 9 Using join cache 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY m2 ALL NULL NULL NULL NULL 9 +1 PRIMARY m2 ALL NULL NULL NULL NULL 9 Using join cache 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 @@ -245,7 +245,7 @@ a a 2 2 explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using join cache 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 2 4 DERIVED t1 ALL NULL NULL NULL NULL 2 5 UNION t1 ALL NULL NULL NULL NULL 2 @@ -312,7 +312,7 @@ b 3.5000 explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using temporary; Using filesort -2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort +2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort; Using join cache 2 DERIVED y ALL NULL NULL NULL NULL 17 Using where drop table t1; create table t2 (a int, b int, primary key (a)); diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 8bf8c95c6fb..3419ce150eb 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -174,7 +174,7 @@ INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2'); explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary -1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index +1 SIMPLE t3 ref a a 5 test.t1.b 2 Using where; Using index; Using join cache 1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a @@ -299,11 +299,11 @@ WHERE ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2)) AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary -1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct -1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct +1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary; Using join cache +1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct; Using join cache +1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct; Using join cache 1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Distinct -1 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index; Distinct +1 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index; Distinct; Using join cache 1 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Distinct 1 SIMPLE t3_lj ref id id 4 test.j_lj_t3.id 1 Using where; Using index; Distinct SELECT DISTINCT @@ -514,7 +514,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary +1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary; Using join cache 1 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Distinct EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2 WHERE t1_1.a = t1_2.a; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 7177028cb48..0e6cae2f103 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -613,7 +613,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index +1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index; Using join cache 1 SIMPLE t2 range k1 k1 3 NULL 4 Using where; Using index explain select min(a4 - 0.01) from t1; @@ -650,7 +650,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range k2 k2 4 NULL 6 Using where; Using index +1 SIMPLE t2 range k2 k2 4 NULL 6 Using where; Using index; Using join cache 1 SIMPLE t1 index NULL PRIMARY 3 NULL 15 Using index drop table t1, t2; create table t1 (a char(10)); diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result index 230f2a7633f..dba546a72de 100644 --- a/mysql-test/r/func_group_innodb.result +++ b/mysql-test/r/func_group_innodb.result @@ -78,7 +78,7 @@ min(7) 7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select min(7) from t2i join t1i; min(7) @@ -94,7 +94,7 @@ max(7) 7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select max(7) from t2i join t1i; max(7) diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index cfca4e318c0..62486936e02 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -394,7 +394,7 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache 1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` diff --git a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result index b02ff04780b..ebd2468c9af 100644 --- a/mysql-test/r/greedy_optimizer.result +++ b/mysql-test/r/greedy_optimizer.result @@ -120,11 +120,11 @@ select @@optimizer_search_depth; 63 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -132,11 +132,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -144,11 +144,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -156,11 +156,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -168,11 +168,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -180,11 +180,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -200,11 +200,11 @@ select @@optimizer_search_depth; 0 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -212,11 +212,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -224,11 +224,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -236,11 +236,11 @@ Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -248,11 +248,11 @@ Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -260,11 +260,11 @@ Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -276,11 +276,11 @@ select @@optimizer_search_depth; 1 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -288,11 +288,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -303,9 +303,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -315,9 +315,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -327,9 +327,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -339,9 +339,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -352,11 +352,11 @@ select @@optimizer_search_depth; 62 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -364,11 +364,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -376,11 +376,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -388,11 +388,11 @@ Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -400,11 +400,11 @@ Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -412,11 +412,11 @@ Variable_name Value Last_query_cost 289.418727 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -432,11 +432,11 @@ select @@optimizer_search_depth; 0 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -444,11 +444,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -456,11 +456,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -468,11 +468,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -480,11 +480,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -492,11 +492,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -508,11 +508,11 @@ select @@optimizer_search_depth; 1 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -520,11 +520,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -535,9 +535,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -547,9 +547,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -559,9 +559,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -571,9 +571,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join cache +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where show status like 'Last_query_cost'; Variable_name Value @@ -584,11 +584,11 @@ select @@optimizer_search_depth; 62 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -596,11 +596,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; @@ -608,11 +608,11 @@ Variable_name Value Last_query_cost 821.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -620,11 +620,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index show status like 'Last_query_cost'; @@ -632,11 +632,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; @@ -644,11 +644,11 @@ Variable_name Value Last_query_cost 794.837037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where; Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where; Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 7068b62993b..807986c5f4e 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -536,11 +536,11 @@ a b 1 1 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort; Using join cache 1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using join cache 1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where drop table t1,t2; create table t1 (a int, b int); diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index dcaf249e9c4..bc07b933895 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2266,7 +2266,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index; Using join cache 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 45dfe0af505..e8a37257782 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -270,7 +270,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1 i1 4 const 1 +1 SIMPLE t0 ref i1 i1 4 const 1 Using join cache 1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); @@ -347,7 +347,7 @@ from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 < 500000 or A.key2 < 3) and (B.key1 < 500000 or B.key2 < 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where +1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where; Using join cache 1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) @@ -361,7 +361,7 @@ from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 = 1 or A.key2 = 1) and (B.key1 = 1 or B.key2 = 1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where +1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where; Using join cache 1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) @@ -376,7 +376,7 @@ from t0 as A, t0 as B where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where +1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join cache 1 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A, t0 as B diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 078946b4d81..fc7a45445a6 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -289,7 +289,7 @@ explain select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE # ALL NULL NULL NULL NULL 2 +1 SIMPLE # ALL NULL NULL NULL NULL 2 Using join cache 1 SIMPLE # ALL NULL NULL NULL NULL 2 Using where select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1; diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result index e5d921514c5..945eca234bb 100644 --- a/mysql-test/r/innodb_gis.result +++ b/mysql-test/r/innodb_gis.result @@ -402,7 +402,7 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache 1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index e51318af827..36d6f62c72c 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -163,7 +163,7 @@ min(7) 7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select min(7) from t2i join t1i; min(7) @@ -179,7 +179,7 @@ max(7) 7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join cache 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select max(7) from t2i join t1i; max(7) diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index b132c8b2e4c..86c206195b1 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -391,6 +391,56 @@ i i i 2 NULL 4 2 2 2 drop table t1,t2,t3; +CREATE TABLE t1 (a int, b int default 0, c int default 1); +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 (a) SELECT a + 8 FROM t1; +INSERT INTO t1 (a) SELECT a + 16 FROM t1; +CREATE TABLE t2 (a int, d int, e int default 0); +INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4); +INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2; +INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2; +EXPLAIN +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e +ORDER BY t1.b, t1.c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort; Using join cache +1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e +ORDER BY t1.b, t1.c; +e +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +0 +DROP TABLE t1,t2; create table t1 (c int, b int); create table t2 (a int, b int); create table t3 (b int, c int); diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 8df5a9220a8..d080b1a2a46 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -228,7 +228,7 @@ LEFT JOIN t8 ON t7.b=t8.b AND t6.b < 10; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using join cache 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Warnings: @@ -543,7 +543,7 @@ WHERE t0.a=1 AND t0.b=t1.b AND (t2.a >= 4 OR t2.c IS NULL); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 @@ -638,7 +638,7 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -646,7 +646,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join cache 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) @@ -835,7 +835,7 @@ t2 ON t3.a=1 AND t2.b=t4.b WHERE t1.a <= 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 @@ -849,7 +849,7 @@ LEFT JOIN (t1,t2) ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join cache 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 @@ -905,7 +905,7 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -913,7 +913,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join cache 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) @@ -955,7 +955,7 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -963,7 +963,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join cache 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) @@ -1004,7 +1004,7 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where @@ -1012,7 +1012,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join cache 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) @@ -1062,7 +1062,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join cache 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index e667d11195d..62dfb36bb52 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1214,3 +1214,28 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; f1 f2 f3 bla blah sheep DROP TABLE t1,t2; +CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8)); +CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id)); +INSERT INTO t1 VALUES +(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc'); +INSERT INTO t2 VALUES +(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40); +EXPLAIN +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ref idx idx 4 test.t1.id 2 Using where; Not exists +flush status; +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; +id a +1 aaaaaaa +4 ddddddd +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 5 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 6 +DROP TABLE t1,t2; diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result index 8097186bde1..35aad39edbf 100644 --- a/mysql-test/r/key_diff.result +++ b/mysql-test/r/key_diff.result @@ -35,7 +35,7 @@ D E a a a a a a explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a NULL NULL NULL 5 +1 SIMPLE t1 ALL a NULL NULL NULL 5 Using join cache 1 SIMPLE t2 ALL b NULL NULL NULL 5 Using where select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; a b a b diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index dcb471510cd..e9d798d998d 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -345,11 +345,11 @@ t1 1 c_2 1 c A 5 NULL NULL YES BTREE t1 1 c_2 2 a A 5 NULL NULL BTREE explain select * from t1,t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 2 +1 SIMPLE t2 ALL a NULL NULL NULL 2 Using join cache 1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where explain select * from t1,t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 2 +1 SIMPLE t2 ALL a NULL NULL NULL 2 Using join cache 1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra @@ -361,7 +361,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref b b 5 test.t2.b 1 Using where explain select * from t1,t2 force index(c) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join cache 1 SIMPLE t1 ALL a NULL NULL NULL 5 Using where explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result index 0d67d310a2b..df281230c9e 100644 --- a/mysql-test/r/ndb_condition_pushdown.result +++ b/mysql-test/r/ndb_condition_pushdown.result @@ -1730,7 +1730,7 @@ pk1 attr1 attr2 attr3 attr4 explain select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where with pushed condition; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL # Using where with pushed condition; Using temporary; Using filesort; Using join cache 1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where with pushed condition select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1; pk1 attr1 attr2 attr3 pk1 attr1 attr2 attr3 attr4 @@ -1746,7 +1746,7 @@ pk1 attr1 attr2 attr3 attr4 explain select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using temporary; Using filesort +1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using temporary; Using filesort; Using join cache 1 SIMPLE t3 ALL NULL NULL NULL NULL # Using where select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1; pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4 diff --git a/mysql-test/r/ndb_gis.result b/mysql-test/r/ndb_gis.result index 279a0884b5b..c01c1673e44 100644 --- a/mysql-test/r/ndb_gis.result +++ b/mysql-test/r/ndb_gis.result @@ -402,7 +402,7 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache 1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` @@ -952,7 +952,7 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Using join cache 1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` diff --git a/mysql-test/r/outfile.result b/mysql-test/r/outfile.result Binary files differindex 023c4ea205f..8503df545d2 100644 --- a/mysql-test/r/outfile.result +++ b/mysql-test/r/outfile.result diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 8e0ae617a6b..3549e987d73 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1225,4 +1225,12 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (i) (PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ DROP TABLE t1, t2; set @@sql_mode=@org_mode; +create table t1 (c1 varchar(255),c2 tinyint,primary key(c1)) +partition by key (c1) partitions 10 ; +insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; +insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; +select * from t1; +c1 c2 +aaa 2 +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index d9efe21c5d0..c2cb7426a74 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -220,27 +220,27 @@ insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); update t1 set y=x; explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache 1 SIMPLE t2 range x x 5 NULL 2 Using where explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache 1 SIMPLE t2 range x x 5 NULL 2 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache 1 SIMPLE t2 range x x 5 NULL 3 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache 1 SIMPLE t2 range x x 5 NULL 3 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache 1 SIMPLE t2 range x x 5 NULL 2 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 Using where +1 SIMPLE t1 ref y y 5 const 1 Using where; Using join cache 1 SIMPLE t2 range x x 5 NULL 2 Using where explain select count(*) from t1 where x in (1); id select_type table type possible_keys key key_len ref rows Extra @@ -255,12 +255,12 @@ CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya)); INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t2 ref j1 j1 4 const 1 Using index; Using join cache 1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index explain select * from t1 force index(i1), t2 force index(j1) where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref j1 j1 4 const 1 Using index +1 SIMPLE t2 ref j1 j1 4 const 1 Using index; Using join cache 1 SIMPLE t1 index i1 i1 4 NULL 7 Using where; Using index DROP TABLE t1,t2; CREATE TABLE t1 ( diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 9f57ef12e12..684aee864b3 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -376,7 +376,7 @@ a b a b c 3 2 3 1 3 EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index NULL PRIMARY 8 NULL 6 100.00 Using index +1 SIMPLE t1 index NULL PRIMARY 8 NULL 6 100.00 Using index; Using join cache 1 SIMPLE t2 index NULL PRIMARY 12 NULL 7 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t1`.`a` - 1) = (`test`.`t2`.`a` - 1)) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 6ffb769292f..1d6ef6fee5c 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -607,11 +607,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 41810 +1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index period period 4 NULL 41810 +1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period @@ -1430,7 +1430,7 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period @@ -2362,7 +2362,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 ref c,d d 5 const 2 Using where; Using join cache 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2370,7 +2370,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 ref c,d d 5 const 2 Using where; Using join cache 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2716,7 +2716,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort +1 SIMPLE t1 ref b,c b 5 const 1 Using where; Using temporary; Using filesort; Using join cache 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -2737,7 +2737,7 @@ INSERT INTO t2 VALUES (1,NULL), (2,10); ALTER TABLE t1 ENABLE KEYS; EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index b b 5 NULL 2 Using index +1 SIMPLE t2 index b b 5 NULL 2 Using index; Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; a b a b @@ -2747,7 +2747,7 @@ a b a b 2 10 4 10 EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index b b 5 NULL 2 Using index +1 SIMPLE t2 index b b 5 NULL 2 Using index; Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; a b a b @@ -2901,11 +2901,11 @@ a 4 EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using join cache 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where DROP TABLE t1,t2; select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 924300b0172..47b86d1a039 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -608,11 +608,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 41810 +1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index period period 4 NULL 41810 +1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period @@ -1431,7 +1431,7 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result index e6066b11ed1..e19dcb712cf 100644 --- a/mysql-test/r/ssl_compress.result +++ b/mysql-test/r/ssl_compress.result @@ -611,11 +611,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index period period 4 NULL 41810 +1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index period period 4 NULL 41810 +1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period @@ -1434,7 +1434,7 @@ companynr companynr 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary +1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary; Using join cache 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 0143b0c7fd3..e90f43b9870 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -904,7 +904,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index; Using join cache 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` @@ -1354,7 +1354,7 @@ a explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index; Using join cache 2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) @@ -4034,6 +4034,13 @@ SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) ) FROM t1; ERROR HY000: Invalid use of group function DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b int, KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1); +EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +DROP TABLE t1; End of 5.0 tests. CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 657d95b7ee3..132c36fbf11 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -254,7 +254,7 @@ select a,b, oref, from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 -2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key; Using join cache 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 275f3357c65..acf237bf125 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -499,7 +499,7 @@ a b explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index +2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using join cache 2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain (select * from t1 where a=1) union (select * from t1 where b=1); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8ebf4d40067..cc353f5e1db 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2317,7 +2317,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v2 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index; Using join cache 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where DROP VIEW v1,v2; DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index bc50d484a5f..7217d00a1bb 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -182,6 +182,12 @@ DROP TABLE t1; select isnull(date(NULL)), isnull(cast(NULL as DATE)); +# +# Bug#23656: Wrong result of CAST from DATE to int +# +SELECT CAST(cast('01-01-01' as date) AS UNSIGNED); +SELECT CAST(cast('01-01-01' as date) AS SIGNED); + --echo End of 4.1 tests diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 6c9270f76be..4d35989aa9a 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -333,6 +333,30 @@ select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i; select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; drop table t1,t2,t3; +# +# Bug #27531: Query performance degredation in 4.1.22 and greater +# +CREATE TABLE t1 (a int, b int default 0, c int default 1); + +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 (a) SELECT a + 8 FROM t1; +INSERT INTO t1 (a) SELECT a + 16 FROM t1; + +CREATE TABLE t2 (a int, d int, e int default 0); + +INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4); +INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2; +INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2; + +# should use join cache +EXPLAIN +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e + ORDER BY t1.b, t1.c; +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e + ORDER BY t1.b, t1.c; + +DROP TABLE t1,t2; + # End of 4.1 tests # diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 10856142701..0a29b4cb325 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -831,3 +831,23 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla'; SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; DROP TABLE t1,t2; + +# +# Bug 28188: 'not exists' optimization for outer joins +# + +CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8)); +CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id)); +INSERT INTO t1 VALUES + (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc'); +INSERT INTO t2 VALUES + (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40); + +EXPLAIN +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; + +flush status; +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; +show status like 'Handler_read%'; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test index 7c90fd32909..f285407efd4 100644 --- a/mysql-test/t/outfile.test +++ b/mysql-test/t/outfile.test @@ -96,3 +96,38 @@ create table t1(a int); eval select * into outfile "$MYSQL_TEST_DIR/outfile-test1" from t1; drop table t1; +# +# Bug#28181 Access denied to 'information_schema when +# select into out file (regression) +# +create database mysqltest; +create user user_1@localhost; +grant all on mysqltest.* to user_1@localhost; +connect (con28181_1,localhost,user_1,,mysqltest); + +--error 1044 +eval select schema_name +into outfile "../tmp/outfile-test.4" +fields terminated by ',' optionally enclosed by '"' + lines terminated by '\n' +from information_schema.schemata +where schema_name like 'mysqltest'; + +connection default; +grant file on *.* to user_1@localhost; + +connect (con28181_2,localhost,user_1,,mysqltest); +eval select schema_name +into outfile "../tmp/outfile-test.4" +fields terminated by ',' optionally enclosed by '"' + lines terminated by '\n' +from information_schema.schemata +where schema_name like 'mysqltest'; + +connection default; +--exec rm $MYSQLTEST_VARDIR/tmp/outfile-test.4 +use test; +revoke all privileges on *.* from user_1@localhost; +drop user user_1@localhost; +drop database mysqltest; + diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 961b1319fc4..36f3a40503b 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1431,4 +1431,15 @@ show create table t2; DROP TABLE t1, t2; set @@sql_mode=@org_mode; +# +# Bug #27123 partition + on duplicate key update + varchar = Can't find record in <table> +# +create table t1 (c1 varchar(255),c2 tinyint,primary key(c1)) + partition by key (c1) partitions 10 ; +insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; +insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1; +select * from t1; +drop table t1; + + --echo End of 5.1 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 0648c3ebda0..aaeb998d0c7 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2874,6 +2874,14 @@ FROM t1; DROP TABLE t1,t2; +# +# Bug #27807: Server crash when executing subquery with EXPLAIN +# +CREATE TABLE t1 (a int, b int, KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1); +EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); +DROP TABLE t1; + --echo End of 5.0 tests. # diff --git a/sql/field.cc b/sql/field.cc index e8718c9e407..3ae82fe6182 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -7006,34 +7006,34 @@ Field_blob::Field_blob(char *ptr_arg, uchar *null_ptr_arg, uchar null_bit_arg, } -void Field_blob::store_length(uint32 number) +void Field_blob::store_length(char *i_ptr, uint i_packlength, uint32 i_number) { - switch (packlength) { + switch (i_packlength) { case 1: - ptr[0]= (uchar) number; + i_ptr[0]= (uchar) i_number; break; case 2: #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) { - int2store(ptr,(unsigned short) number); + int2store(i_ptr,(unsigned short) i_number); } else #endif - shortstore(ptr,(unsigned short) number); + shortstore(i_ptr,(unsigned short) i_number); break; case 3: - int3store(ptr,number); + int3store(i_ptr,i_number); break; case 4: #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) { - int4store(ptr,number); + int4store(i_ptr,i_number); } else #endif - longstore(ptr,number); + longstore(i_ptr,i_number); } } diff --git a/sql/field.h b/sql/field.h index f9d77fe17ab..2c2640a8262 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1307,7 +1307,15 @@ public: } int reset(void) { bzero(ptr, packlength+sizeof(char*)); return 0; } void reset_fields() { bzero((char*) &value,sizeof(value)); } - void store_length(uint32 number); +#ifndef WORDS_BIGENDIAN + static +#endif + void store_length(char *i_ptr, uint i_packlength, uint32 i_number); + inline void store_length(uint32 number) + { + store_length(ptr, packlength, number); + } + inline uint32 get_length(uint row_offset=0) { return get_length(ptr+row_offset); } uint32 get_length(const char *ptr); @@ -1325,10 +1333,15 @@ public: memcpy(ptr,length,packlength); memcpy_fixed(ptr+packlength,&data,sizeof(char*)); } + void set_ptr_offset(my_ptrdiff_t ptr_diff, uint32 length,char *data) + { + char *ptr_ofs= ADD_TO_PTR(ptr,ptr_diff,char*); + store_length(ptr_ofs, packlength, length); + memcpy_fixed(ptr_ofs+packlength,&data,sizeof(char*)); + } inline void set_ptr(uint32 length,char *data) { - store_length(length); - memcpy_fixed(ptr+packlength,&data,sizeof(char*)); + set_ptr_offset(0, length, data); } uint get_key_image(char *buff,uint length, imagetype type); void set_key_image(char *buff,uint length); diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc index 02fbee7237f..b2a02c3b6d5 100644 --- a/sql/ha_ndbcluster.cc +++ b/sql/ha_ndbcluster.cc @@ -874,9 +874,7 @@ int get_ndb_blobs_value(TABLE* table, NdbValue* value_array, i, offset, (long) buf, len, (int)ptrdiff)); DBUG_ASSERT(len == len64); // Ugly hack assumes only ptr needs to be changed - field_blob->ptr+= ptrdiff; - field_blob->set_ptr(len, buf); - field_blob->ptr-= ptrdiff; + field_blob->set_ptr_offset(ptrdiff, len, buf); } offset+= size; } @@ -885,9 +883,7 @@ int get_ndb_blobs_value(TABLE* table, NdbValue* value_array, // have to set length even in this case char *buf= buffer + offset; // or maybe NULL uint32 len= 0; - field_blob->ptr+= ptrdiff; - field_blob->set_ptr(len, buf); - field_blob->ptr-= ptrdiff; + field_blob->set_ptr_offset(ptrdiff, len, buf); DBUG_PRINT("info", ("[%u] isNull=%d", i, isNull)); } } diff --git a/sql/item_func.cc b/sql/item_func.cc index 732c4403ca2..9219c01ccd0 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -956,7 +956,8 @@ longlong Item_func_signed::val_int() longlong value; int error; - if (args[0]->cast_to_int_type() != STRING_RESULT) + if (args[0]->cast_to_int_type() != STRING_RESULT || + args[0]->result_as_longlong()) { value= args[0]->val_int(); null_value= args[0]->null_value; @@ -995,7 +996,8 @@ longlong Item_func_unsigned::val_int() my_decimal2int(E_DEC_FATAL_ERROR, dec, 1, &value); return value; } - else if (args[0]->cast_to_int_type() != STRING_RESULT) + else if (args[0]->cast_to_int_type() != STRING_RESULT || + args[0]->result_as_longlong()) { value= args[0]->val_int(); null_value= args[0]->null_value; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 812d3c222c0..7d635ba444e 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1817,6 +1817,21 @@ int subselect_single_select_engine::exec() thd->lex->current_select= save_select; DBUG_RETURN(join->error ? join->error : 1); } + if (!select_lex->uncacheable && thd->lex->describe && + !(join->select_options & SELECT_DESCRIBE) && + join->need_tmp && item->const_item()) + { + /* + Force join->join_tmp creation, because this subquery will be replaced + by a simple select from the materialization temp table by optimize() + called by EXPLAIN and we need to preserve the initial query structure + so we can display it. + */ + select_lex->uncacheable|= UNCACHEABLE_EXPLAIN; + select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN; + if (join->init_save_join_tab()) + DBUG_RETURN(1); + } if (item->engine_changed) { DBUG_RETURN(1); diff --git a/sql/key.cc b/sql/key.cc index 19861cee134..9a3155c459e 100644 --- a/sql/key.cc +++ b/sql/key.cc @@ -214,23 +214,34 @@ void key_restore(byte *to_record, byte *from_key, KEY *key_info, } if (key_part->key_part_flag & HA_BLOB_PART) { + /* + This in fact never happens, as we have only partial BLOB + keys yet anyway, so it's difficult to find any sence to + restore the part of a record. + Maybe this branch is to be removed, but now we + have to ignore GCov compaining. + */ uint blob_length= uint2korr(from_key); + Field_blob *field= (Field_blob*) key_part->field; from_key+= HA_KEY_BLOB_LENGTH; key_length-= HA_KEY_BLOB_LENGTH; - ((Field_blob*) key_part->field)->set_ptr((ulong) blob_length, - (char*) from_key); + field->set_ptr_offset(to_record - field->table->record[0], + (ulong) blob_length, (char*) from_key); length= key_part->length; } else if (key_part->key_part_flag & HA_VAR_LENGTH_PART) { + Field *field= key_part->field; my_bitmap_map *old_map; + my_ptrdiff_t ptrdiff= to_record - field->table->record[0]; + field->move_field_offset(ptrdiff); key_length-= HA_KEY_BLOB_LENGTH; length= min(key_length, key_part->length); - old_map= dbug_tmp_use_all_columns(key_part->field->table, - key_part->field->table->write_set); - key_part->field->set_key_image((char *) from_key, length); - dbug_tmp_restore_column_map(key_part->field->table->write_set, old_map); + old_map= dbug_tmp_use_all_columns(field->table, field->table->write_set); + field->set_key_image((char *) from_key, length); + dbug_tmp_restore_column_map(field->table->write_set, old_map); from_key+= HA_KEY_BLOB_LENGTH; + field->move_field_offset(-ptrdiff); } else { diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index af80ea91397..ae9a7bdde3d 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4570,7 +4570,8 @@ check_access(THD *thd, ulong want_access, const char *db, ulong *save_priv, if (schema_db) { - if (want_access & ~(SELECT_ACL | EXTRA_ACL)) + if (!(sctx->master_access & FILE_ACL) && (want_access & FILE_ACL) || + (want_access & ~(SELECT_ACL | EXTRA_ACL | FILE_ACL))) { if (!no_errors) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index dff810e57b0..8f4fa75be91 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1436,14 +1436,13 @@ JOIN::optimize() } } - if (select_lex->uncacheable && !is_top_level_join()) - { - /* If this join belongs to an uncacheable subquery */ - if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN)))) - DBUG_RETURN(-1); - error= 0; // Ensure that tmp_join.error= 0 - restore_tmp(); - } + /* + If this join belongs to an uncacheable subquery save + the original join + */ + if (select_lex->uncacheable && !is_top_level_join() && + init_save_join_tab()) + DBUG_RETURN(-1); } error= 0; @@ -1505,6 +1504,27 @@ JOIN::reinit() DBUG_RETURN(0); } +/** + @brief Save the original join layout + + @details Saves the original join layout so it can be reused in + re-execution and for EXPLAIN. + + @return Operation status + @retval 0 success. + @retval 1 error occurred. +*/ + +bool +JOIN::init_save_join_tab() +{ + if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN)))) + return 1; + error= 0; // Ensure that tmp_join.error= 0 + restore_tmp(); + return 0; +} + bool JOIN::save_join_tab() @@ -5873,6 +5893,12 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) tab->ref.key= -1; tab->ref.key_parts=0; // Don't use ref key. join->best_positions[i].records_read= rows2double(tab->quick->records); + /* + We will use join cache here : prevent sorting of the first + table only and sort at the end. + */ + if (i != join->const_tables && join->tables > join->const_tables + 1) + join->full_join= 1; } tmp= NULL; @@ -6155,11 +6181,14 @@ make_join_readinfo(JOIN *join, ulonglong options) disable join cache because it will change the ordering of the results. Code handles sort table that is at any location (not only first after the const tables) despite the fact that it's currently prohibited. + We must disable join cache if the first non-const table alone is + ordered. If there is a temp table the ordering is done as a last + operation and doesn't prevent join cache usage. */ - if (!ordered_set && - (table == join->sort_by_table && + if (!ordered_set && !join->need_tmp && + ((table == join->sort_by_table && (!join->order || join->skip_sort_order)) || - (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)) + (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))) ordered_set= 1; tab->sorted= sorted; @@ -10768,7 +10797,6 @@ static enum_nested_loop_state evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, int error, my_bool *report_error) { - bool not_exists_optimize= join_tab->table->reginfo.not_exists_optimize; bool not_used_in_distinct=join_tab->not_used_in_distinct; ha_rows found_records=join->found_records; COND *select_cond= join_tab->select_cond; @@ -10805,6 +10833,8 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, first_unmatched->found= 1; for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++) { + if (tab->table->reginfo.not_exists_optimize) + return NESTED_LOOP_NO_MORE_ROWS; /* Check all predicates that has just been activated. */ /* Actually all predicates non-guarded by first_unmatched->found @@ -10850,8 +10880,6 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, if (found) { enum enum_nested_loop_state rc; - if (not_exists_optimize) - return NESTED_LOOP_NO_MORE_ROWS; /* A match from join_tab is found for the current partial join. */ rc= (*join_tab->next_select)(join, join_tab+1, 0); if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS) @@ -12638,6 +12666,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, uint nr; key_map keys; + /* + filesort() and join cache are usually faster than reading in + index order and not using join cache + */ + if (tab->type == JT_ALL && tab->join->tables > tab->join->const_tables + 1) + DBUG_RETURN(0); /* If not used with LIMIT, only use keys if the whole query can be resolved with a key; This is because filesort() is usually faster than @@ -15491,6 +15525,8 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, break; } } + if (tab->next_select == sub_select_cache) + extra.append(STRING_WITH_LEN("; Using join cache")); /* Skip initial "; "*/ const char *str= extra.ptr(); diff --git a/sql/sql_select.h b/sql/sql_select.h index ca37c7bd274..644224d1bed 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -473,6 +473,7 @@ public: void cleanup(bool full); void clear(); bool save_join_tab(); + bool init_save_join_tab(); bool send_row_on_empty_set() { return (do_send_rows && tmp_table_param.sum_func_count != 0 && |