3361 Gleb Shchepa 2011-05-30
WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
Test result adjustments
@ mysql-test/r/ctype_utf8.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/ctype_utf8mb4.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/ctype_utf8mb4_innodb.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/information_schema.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl1.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl2.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl3.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl4.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl5.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl7.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_cache_jcl8.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_outer.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/join_outer_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/order_by_icp_mrr.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/order_by_none.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/select_all.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/select_all_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/select_icp_mrr.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/select_icp_mrr_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/select_none.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/select_none_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/ssl.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/ssl_compress.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_all.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_all_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_mat.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_mat_all.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_mat_none.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_nomat_nosj.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_nomat_nosj_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_none.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_none_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_all.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_all_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_all_jcl7.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_dupsweed.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_dupsweed_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_dupsweed_jcl7.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_firstmatch.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_firstmatch_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_firstmatch_jcl7.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_loosescan.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_loosescan_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_loosescan_jcl7.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_mat.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_mat_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_mat_jcl7.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_mat_nosj.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_none.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_none_jcl6.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/subquery_sj_none_jcl7.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/type_bit_innodb.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/view.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/suite/innodb/r/innodb_mysql.result
Minor changes ("extra" column message reordering) after WL4897.
modified:
mysql-test/r/ctype_utf8.result
mysql-test/r/ctype_utf8mb4.result
mysql-test/r/ctype_utf8mb4_innodb.result
mysql-test/r/information_schema.result
mysql-test/r/join_cache_jcl1.result
mysql-test/r/join_cache_jcl2.result
mysql-test/r/join_cache_jcl3.result
mysql-test/r/join_cache_jcl4.result
mysql-test/r/join_cache_jcl5.result
mysql-test/r/join_cache_jcl6.result
mysql-test/r/join_cache_jcl7.result
mysql-test/r/join_cache_jcl8.result
mysql-test/r/join_outer.result
mysql-test/r/join_outer_jcl6.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
mysql-test/r/select_all.result
mysql-test/r/select_all_jcl6.result
mysql-test/r/select_icp_mrr.result
mysql-test/r/select_icp_mrr_jcl6.result
mysql-test/r/select_none.result
mysql-test/r/select_none_jcl6.result
mysql-test/r/ssl.result
mysql-test/r/ssl_compress.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_mat_none.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_jcl6.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
mysql-test/r/type_bit_innodb.result
mysql-test/r/view.result
mysql-test/suite/innodb/r/innodb_gis.result
mysql-test/suite/innodb/r/innodb_mysql.result
3360 Gleb Shchepa 2011-05-27
WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
Minor refactoring and test result adjustments
@ mysql-test/r/compress.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/derived.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/distinct.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/gis.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/group_by.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/group_min_max.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/null_key_all.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/null_key_icp.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/null_key_none.result
Minor changes ("extra" column message reordering) after WL4897.
@ mysql-test/r/order_by_all.result
Minor changes ("extra" column message reordering) after WL4897.
@ sql/opt_explain.cc
Refactoring:
* part of Explain_join::explain_key_and_len() has been replaced
with an additional call to explain_key_and_len_index()
* a few buffer allocations have been replaced with StringBuff
declarations
@ sql/sql_string.h
+ The StringBuff template helper has been introduced
modified:
mysql-test/r/compress.result
mysql-test/r/derived.result
mysql-test/r/distinct.result
mysql-test/r/gis.result
mysql-test/r/group_by.result
mysql-test/r/group_min_max.result
mysql-test/r/null_key_all.result
mysql-test/r/null_key_icp.result
mysql-test/r/null_key_none.result
mysql-test/r/order_by_all.result
sql/opt_explain.cc
sql/sql_string.h
=== modified file 'mysql-test/r/ctype_utf8.result'
--- a/mysql-test/r/ctype_utf8.result 2011-03-04 15:55:18 +0000
+++ b/mysql-test/r/ctype_utf8.result 2011-05-30 11:03:42 +0000
@@ -1580,7 +1580,7 @@ a
e
explain select a from t1 group by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
select a from t1 group by a;
a
e
@@ -1590,7 +1590,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 filesort; Using temporary
1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
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
=== modified file 'mysql-test/r/ctype_utf8mb4.result'
--- a/mysql-test/r/ctype_utf8mb4.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/ctype_utf8mb4.result 2011-05-30 11:03:42 +0000
@@ -1619,7 +1619,7 @@ a
e
explain select a from t1 group by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
select a from t1 group by a;
a
e
@@ -1629,7 +1629,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 filesort; Using temporary
1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
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
=== modified file 'mysql-test/r/ctype_utf8mb4_innodb.result'
--- a/mysql-test/r/ctype_utf8mb4_innodb.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/ctype_utf8mb4_innodb.result 2011-05-30 11:03:42 +0000
@@ -1587,7 +1587,7 @@ COUNT(*)
1
explain select a from t1 group by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
SELECT COUNT(*) FROM (SELECT a FROM t1 GROUP BY a) AS t2;
COUNT(*)
1
@@ -1597,7 +1597,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 filesort; Using temporary
1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
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
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result 2011-03-11 18:53:12 +0000
+++ b/mysql-test/r/information_schema.result 2011-05-30 11:03:42 +0000
@@ -1286,7 +1286,7 @@ from information_schema.tables
order by object_schema;
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases; Using filesort
+1 SIMPLE tables ALL NULL NULL NULL NULL NULL Using filesort; Open_frm_only; Scanned all databases
explain select * from (select table_name from information_schema.tables) as a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result 2011-03-29 07:30:44 +0000
+++ b/mysql-test/r/join_cache_jcl1.result 2011-05-30 11:03:42 +0000
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result 2011-03-29 07:30:44 +0000
+++ b/mysql-test/r/join_cache_jcl2.result 2011-05-30 11:03:42 +0000
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result 2011-03-29 07:30:44 +0000
+++ b/mysql-test/r/join_cache_jcl3.result 2011-05-30 11:03:42 +0000
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result 2011-03-29 07:30:44 +0000
+++ b/mysql-test/r/join_cache_jcl4.result 2011-05-30 11:03:42 +0000
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 index NULL col_int_key 5 NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/join_cache_jcl5.result 2011-05-30 11:03:42 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using filesort; Using index; Using temporary
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA, regular buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA, regular buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA, regular buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA, regular buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/join_cache_jcl6.result 2011-05-30 11:03:42 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using filesort; Using index; Using temporary
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA, incremental buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA, incremental buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA, incremental buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA, incremental buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/join_cache_jcl7.result 2011-05-30 11:03:42 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using filesort; Using index; Using temporary
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA_UNIQUE, regular buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/join_cache_jcl8.result 2011-05-30 11:03:42 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using filesort; Using index; Using temporary
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA_UNIQUE, incremental buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using filesort; Using index; Using temporary
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
@@ -2220,7 +2220,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1,t2
@@ -2238,7 +2238,7 @@ GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
SELECT t1.col_int_key, t1.col_datetime
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result 2011-02-02 08:15:16 +0000
+++ b/mysql-test/r/join_outer.result 2011-05-30 11:03:42 +0000
@@ -610,7 +610,7 @@ INSERT INTO t2 VALUES (1,1);
explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ref id id 4 test.t1.id 1 Using where; Using index; Not exists
+1 SIMPLE t2 ref id id 4 test.t1.id 1 Using where; Not exists; Using index
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
id name id idx
2 no NULL NULL
@@ -737,13 +737,13 @@ explain select s.*, '*', m.*, (s.match_1
(t2 s left join t1 m on m.match_id = 1)
order by m.match_id desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
+1 SIMPLE s ALL NULL NULL NULL NULL 10 Using filesort; Using temporary
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
(t2 s left join t1 m on m.match_id = 1)
order by UUX desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
+1 SIMPLE s ALL NULL NULL NULL NULL 10 Using filesort; Using temporary
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
(t2 s left join t1 m on m.match_id = 1)
@@ -763,7 +763,7 @@ explain select s.*, '*', m.*, (s.match_1
t2 s straight_join t1 m where m.match_id = 1
order by UUX desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
+1 SIMPLE s ALL NULL NULL NULL NULL 10 Using filesort; Using temporary
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
t2 s straight_join t1 m where m.match_id = 1
@@ -1251,7 +1251,7 @@ INSERT INTO t2 VALUES (1), (2), (3);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Not exists
+1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Not exists; Using index
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
c e d
1 0 NULL
@@ -1272,7 +1272,7 @@ SELECT t1.a, COUNT( t2.b ), SUM( t2.b ),
FROM t1 LEFT JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 LEFT JOIN t2 USING( a )
@@ -1414,7 +1414,7 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON
WHERE t1.f1 = 4
GROUP BY t2.f1, t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort; Using temporary
1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index
SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
WHERE t1.f1 = 4
=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result 2011-02-02 15:05:14 +0000
+++ b/mysql-test/r/join_outer_jcl6.result 2011-05-30 11:03:42 +0000
@@ -615,7 +615,7 @@ INSERT INTO t2 VALUES (1,1);
explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ref id id 4 test.t1.id 1 Using where; Using index; Not exists
+1 SIMPLE t2 ref id id 4 test.t1.id 1 Using where; Not exists; Using index
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
id name id idx
2 no NULL NULL
@@ -742,13 +742,13 @@ explain select s.*, '*', m.*, (s.match_1
(t2 s left join t1 m on m.match_id = 1)
order by m.match_id desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
+1 SIMPLE s ALL NULL NULL NULL NULL 10 Using filesort; Using temporary
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using join buffer (BKA, incremental buffers)
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
(t2 s left join t1 m on m.match_id = 1)
order by UUX desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
+1 SIMPLE s ALL NULL NULL NULL NULL 10 Using filesort; Using temporary
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using join buffer (BKA, incremental buffers)
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
(t2 s left join t1 m on m.match_id = 1)
@@ -768,7 +768,7 @@ explain select s.*, '*', m.*, (s.match_1
t2 s straight_join t1 m where m.match_id = 1
order by UUX desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
+1 SIMPLE s ALL NULL NULL NULL NULL 10 Using filesort; Using temporary
1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using join buffer (BKA, incremental buffers)
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
t2 s straight_join t1 m where m.match_id = 1
@@ -1256,7 +1256,7 @@ INSERT INTO t2 VALUES (1), (2), (3);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Not exists
+1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Not exists; Using index
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
c e d
1 0 NULL
@@ -1277,7 +1277,7 @@ SELECT t1.a, COUNT( t2.b ), SUM( t2.b ),
FROM t1 LEFT JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 LEFT JOIN t2 USING( a )
@@ -1419,7 +1419,7 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON
WHERE t1.f1 = 4
GROUP BY t2.f1, t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort; Using temporary
1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index
SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
WHERE t1.f1 = 4
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2011-05-30 11:03:42 +0000
@@ -327,7 +327,7 @@ a b c
1 NULL NULL
explain select * from t1 where a = 1 and b is null order by a desc, b desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 9 const,const 2 Using where; Using index; Using filesort
+1 SIMPLE t1 ref a a 9 const,const 2 Using where; Using filesort; Using index
select * from t1 where a = 1 and b is null order by a desc, b desc;
a b c
1 NULL NULL
@@ -340,7 +340,7 @@ id select_type table type possible_keys
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
explain select * from t1 where a = 2 and b is null order by a desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort
+1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using filesort; Using index
explain select * from t1 where a = 2 and (b is null or b > 0) order by a
desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra
@@ -496,25 +496,25 @@ gid sid uid
103853 5 250
EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
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 filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition
drop table t1,t2,t3;
CREATE TABLE t1 (
@@ -1004,7 +1004,7 @@ explain SELECT t1.b as a, t2.b as c FROM
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
SELECT t2.b as c FROM
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
@@ -1486,7 +1486,7 @@ SELECT d FROM t1, t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort
+1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using filesort; Using temporary
1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition
SELECT d FROM t1, t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
@@ -2408,7 +2408,7 @@ INSERT INTO t1 VALUES (29, 8, 1), (30, 8
INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4);
EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using index; Using filesort
+1 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using filesort; Using index
SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
id1
13
@@ -2488,19 +2488,19 @@ INSERT INTO t2 SELECT a+4, b FROM t2;
EXPLAIN
SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort
+1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
# should have "using filesort"
EXPLAIN
SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort
+1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
# should have "using filesort"
EXPLAIN
SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort
+1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
DROP TABLE t1, t2;
#
@@ -2521,7 +2521,7 @@ GROUP BY t1.a
ORDER by c
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort
+1 SIMPLE t1 index NULL a 8 NULL 10 Using filesort; Using index; Using temporary
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where
DROP TABLE t1, t2;
#
@@ -2536,7 +2536,7 @@ CREATE TABLE t1 (a INT,KEY (a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index; Using filesort
+1 SIMPLE t1 index a a 5 NULL 10 Using where; Using filesort; Using index
SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
a 1
10 1
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_none.result 2011-05-30 11:03:42 +0000
@@ -326,7 +326,7 @@ a b c
1 NULL NULL
explain select * from t1 where a = 1 and b is null order by a desc, b desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 9 const,const 2 Using where; Using index; Using filesort
+1 SIMPLE t1 ref a a 9 const,const 2 Using where; Using filesort; Using index
select * from t1 where a = 1 and b is null order by a desc, b desc;
a b c
1 NULL NULL
@@ -339,7 +339,7 @@ id select_type table type possible_keys
1 SIMPLE t1 range a a 9 NULL 5 Using where; Using index
explain select * from t1 where a = 2 and b is null order by a desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort
+1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using filesort; Using index
explain select * from t1 where a = 2 and (b is null or b > 0) order by a
desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra
@@ -495,25 +495,25 @@ gid sid uid
103853 5 250
EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using filesort; Using temporary
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
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 filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where
drop table t1,t2,t3;
CREATE TABLE t1 (
@@ -1003,7 +1003,7 @@ explain SELECT t1.b as a, t2.b as c FROM
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort; Using temporary
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
SELECT t2.b as c FROM
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
@@ -1485,7 +1485,7 @@ SELECT d FROM t1, t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 4 const 4 Using where; Using temporary; Using filesort
+1 SIMPLE t1 ref a,b b 4 const 4 Using where; Using filesort; Using temporary
1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using where
SELECT d FROM t1, t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
@@ -2407,7 +2407,7 @@ INSERT INTO t1 VALUES (29, 8, 1), (30, 8
INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4);
EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using index; Using filesort
+1 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using filesort; Using index
SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
id1
13
@@ -2487,19 +2487,19 @@ INSERT INTO t2 SELECT a+4, b FROM t2;
EXPLAIN
SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
# should have "using filesort"
EXPLAIN
SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
# should have "using filesort"
EXPLAIN
SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using filesort; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
DROP TABLE t1, t2;
#
@@ -2520,7 +2520,7 @@ GROUP BY t1.a
ORDER by c
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort
+1 SIMPLE t1 index NULL a 8 NULL 10 Using filesort; Using index; Using temporary
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where
DROP TABLE t1, t2;
#
@@ -2535,7 +2535,7 @@ CREATE TABLE t1 (a INT,KEY (a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index; Using filesort
+1 SIMPLE t1 index a a 5 NULL 10 Using where; Using filesort; Using index
SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
a 1
10 1
=== modified file 'mysql-test/r/select_all.result'
--- a/mysql-test/r/select_all.result 2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_all.result 2011-05-30 11:03:42 +0000
@@ -600,11 +600,11 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
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
@@ -2718,7 +2718,7 @@ explain select straight_join DISTINCT 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 temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using filesort; Using temporary
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/select_all_jcl6.result'
--- a/mysql-test/r/select_all_jcl6.result 2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/select_all_jcl6.result 2011-05-30 11:03:42 +0000
@@ -604,19 +604,19 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (BKA, incremental buffers)
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 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
1 SIMPLE t1 ref period period 4 test.t3.period 4181 Using join buffer (BKA, incremental buffers)
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 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (BKA, incremental buffers)
select period from t1;
period
@@ -2722,7 +2722,7 @@ explain select straight_join DISTINCT 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 temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using filesort; Using temporary
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result 2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_icp_mrr.result 2011-05-30 11:03:42 +0000
@@ -600,11 +600,11 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
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
@@ -2718,7 +2718,7 @@ explain select straight_join DISTINCT 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 temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using filesort; Using temporary
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/select_icp_mrr_jcl6.result'
--- a/mysql-test/r/select_icp_mrr_jcl6.result 2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/select_icp_mrr_jcl6.result 2011-05-30 11:03:42 +0000
@@ -604,19 +604,19 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (BKA, incremental buffers)
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 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
1 SIMPLE t1 ref period period 4 test.t3.period 4181 Using join buffer (BKA, incremental buffers)
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 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (BKA, incremental buffers)
select period from t1;
period
@@ -2722,7 +2722,7 @@ explain select straight_join DISTINCT 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 temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using filesort; Using temporary
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/select_none.result'
--- a/mysql-test/r/select_none.result 2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_none.result 2011-05-30 11:03:42 +0000
@@ -599,11 +599,11 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
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
@@ -2717,7 +2717,7 @@ explain select straight_join DISTINCT 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 temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using filesort; Using temporary
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/select_none_jcl6.result'
--- a/mysql-test/r/select_none_jcl6.result 2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/select_none_jcl6.result 2011-05-30 11:03:42 +0000
@@ -603,11 +603,11 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
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
@@ -2721,7 +2721,7 @@ explain select straight_join DISTINCT 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 temporary; Using filesort
+1 SIMPLE t1 ref b,c b 5 const 1 Using filesort; Using temporary
1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/ssl.result'
--- a/mysql-test/r/ssl.result 2010-11-30 13:37:51 +0000
+++ b/mysql-test/r/ssl.result 2011-05-30 11:03:42 +0000
@@ -600,11 +600,11 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
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
=== modified file 'mysql-test/r/ssl_compress.result'
--- a/mysql-test/r/ssl_compress.result 2010-11-30 13:37:51 +0000
+++ b/mysql-test/r/ssl_compress.result 2011-05-30 11:03:42 +0000
@@ -603,11 +603,11 @@ select distinct fld3 from t2,t3 where t2
fld3
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using filesort; Using temporary
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort; Using temporary
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
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2011-03-29 08:10:26 +0000
+++ b/mysql-test/r/subquery_all.result 2011-05-30 11:03:42 +0000
@@ -1471,7 +1471,7 @@ a
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -3837,7 +3837,7 @@ 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
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
@@ -3875,7 +3875,7 @@ EXPLAIN EXTENDED
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
DROP TABLE t1;
@@ -4016,7 +4016,7 @@ SELECT a AS out_a, MIN(b) FROM t1 t1_out
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using filesort; Using temporary
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
@@ -4225,7 +4225,7 @@ select a, oref, a in (select max(ie)
from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
@@ -4234,7 +4234,7 @@ select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
@@ -4264,7 +4264,7 @@ Z
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
drop table t1, t2, t3;
@@ -4405,7 +4405,7 @@ select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z 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 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
@@ -4656,7 +4656,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using filesort; Using temporary
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -4817,7 +4817,7 @@ explain
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using filesort; Using temporary
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref a Z
ee NULL 0
=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result 2011-03-29 08:10:26 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result 2011-05-30 11:03:42 +0000
@@ -1475,7 +1475,7 @@ a
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -3841,7 +3841,7 @@ 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
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
@@ -3879,7 +3879,7 @@ EXPLAIN EXTENDED
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
DROP TABLE t1;
@@ -4020,7 +4020,7 @@ SELECT a AS out_a, MIN(b) FROM t1 t1_out
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using filesort; Using temporary
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
@@ -4229,7 +4229,7 @@ select a, oref, a in (select max(ie)
from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
@@ -4238,7 +4238,7 @@ select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
@@ -4268,7 +4268,7 @@ Z
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
drop table t1, t2, t3;
@@ -4409,7 +4409,7 @@ select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z 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 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
@@ -4660,7 +4660,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using filesort; Using temporary
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -4821,7 +4821,7 @@ explain
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using filesort; Using temporary
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref a Z
ee NULL 0
=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result 2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_mat.result 2011-05-30 11:03:42 +0000
@@ -50,7 +50,7 @@ explain extended
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
@@ -61,7 +61,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`)))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
@@ -72,7 +72,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`)))))
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
@@ -342,7 +342,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
6 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
7 UNION t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
@@ -495,7 +495,7 @@ a1 a2
explain extended
select * from t1 group by (a1 in (select col from columns));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort; Using temporary
2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond((<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`)))))
@@ -752,7 +752,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using filesort; Using temporary
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
a b
@@ -763,20 +763,20 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
DROP TABLE t1;
@@ -809,7 +809,7 @@ id select_type table type possible_keys
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1,t2,t3,t4;
#
# BUG#46680 - Assertion failed in file item_subselect.cc,
=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_mat_all.result 2011-05-30 11:03:42 +0000
@@ -50,7 +50,7 @@ explain extended
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
@@ -61,7 +61,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`)))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
@@ -72,7 +72,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`)))))
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
@@ -342,7 +342,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; FirstMatch(t1)
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
7 UNION t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 50.00 Using where; Using index; LooseScan
@@ -495,7 +495,7 @@ a1 a2
explain extended
select * from t1 group by (a1 in (select col from columns));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort; Using temporary
2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond((<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`)))))
@@ -752,7 +752,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using filesort; Using temporary
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
a b
@@ -763,20 +763,20 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
DROP TABLE t1;
@@ -809,7 +809,7 @@ id select_type table type possible_keys
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1,t2,t3,t4;
#
# BUG#46680 - Assertion failed in file item_subselect.cc,
=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result 2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_mat_none.result 2011-05-30 11:03:42 +0000
@@ -49,7 +49,7 @@ explain extended
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` having (<cache>(`test`.`t1`.`a1`) = <ref_null_helper>(`test`.`t2`.`b1`))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
@@ -60,7 +60,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) or isnull(`test`.`t2`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`) or isnull(`test`.`t2`.`b2`)) and <is_not_null_test>(`test`.`t2`.`b1`) and <is_not_null_test>(`test`.`t2`.`b2`))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
@@ -71,7 +71,7 @@ explain extended
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) or isnull(`test`.`t2`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = min(`test`.`t2`.`b2`)) or isnull(min(`test`.`t2`.`b2`))) and <is_not_null_test>(`test`.`t2`.`b1`) and <is_not_null_test>(min(`test`.`t2`.`b2`)))))
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
@@ -341,7 +341,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
5 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
6 DEPENDENT SUBQUERY t2i index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using index; Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using filesort; Using temporary
4 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
7 UNION t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
@@ -494,7 +494,7 @@ a1 a2
explain extended
select * from t1 group by (a1 in (select col from columns));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort; Using temporary
2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond((<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`)))))
@@ -751,7 +751,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using filesort; Using temporary
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
a b
@@ -769,13 +769,13 @@ INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
DROP TABLE t1;
@@ -808,7 +808,7 @@ id select_type table type possible_keys
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1,t2,t3,t4;
#
# BUG#46680 - Assertion failed in file item_subselect.cc,
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2011-03-24 13:16:36 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2011-05-30 11:03:42 +0000
@@ -1471,7 +1471,7 @@ a
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -3837,7 +3837,7 @@ 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
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
@@ -3875,7 +3875,7 @@ EXPLAIN EXTENDED
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
DROP TABLE t1;
@@ -4016,7 +4016,7 @@ SELECT a AS out_a, MIN(b) FROM t1 t1_out
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using filesort; Using temporary
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
@@ -4225,7 +4225,7 @@ select a, oref, a in (select max(ie)
from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
@@ -4234,7 +4234,7 @@ select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
@@ -4264,7 +4264,7 @@ Z
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
drop table t1, t2, t3;
@@ -4405,7 +4405,7 @@ select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z 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 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
@@ -4656,7 +4656,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using filesort; Using temporary
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -4817,7 +4817,7 @@ explain
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using filesort; Using temporary
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref a Z
ee NULL 0
=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-03-29 08:10:26 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-05-30 11:03:42 +0000
@@ -1475,7 +1475,7 @@ a
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -3841,7 +3841,7 @@ 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
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
@@ -3879,7 +3879,7 @@ EXPLAIN EXTENDED
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
DROP TABLE t1;
@@ -4020,7 +4020,7 @@ SELECT a AS out_a, MIN(b) FROM t1 t1_out
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using filesort; Using temporary
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
@@ -4229,7 +4229,7 @@ select a, oref, a in (select max(ie)
from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
@@ -4238,7 +4238,7 @@ select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
@@ -4268,7 +4268,7 @@ Z
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
drop table t1, t2, t3;
@@ -4409,7 +4409,7 @@ select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z 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 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
@@ -4660,7 +4660,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using filesort; Using temporary
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -4821,7 +4821,7 @@ explain
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using filesort; Using temporary
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref a Z
ee NULL 0
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_none.result 2011-05-30 11:03:42 +0000
@@ -1470,7 +1470,7 @@ a
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -3836,7 +3836,7 @@ 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
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
@@ -3874,7 +3874,7 @@ EXPLAIN EXTENDED
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
DROP TABLE t1;
@@ -4015,7 +4015,7 @@ SELECT a AS out_a, MIN(b) FROM t1 t1_out
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using filesort; Using temporary
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
@@ -4224,7 +4224,7 @@ select a, oref, a in (select max(ie)
from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
@@ -4233,7 +4233,7 @@ select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
@@ -4263,7 +4263,7 @@ Z
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
drop table t1, t2, t3;
@@ -4404,7 +4404,7 @@ select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z 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 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
@@ -4655,7 +4655,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using filesort; Using temporary
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -4816,7 +4816,7 @@ explain
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using filesort; Using temporary
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref a Z
ee NULL 0
=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result 2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result 2011-05-30 11:03:42 +0000
@@ -1474,7 +1474,7 @@ a
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -3840,7 +3840,7 @@ 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
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
@@ -3878,7 +3878,7 @@ EXPLAIN EXTENDED
SELECT * FROM (SELECT count(*) FROM t1 GROUP BY a) as res;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `res`.`count(*)` AS `count(*)` from (select count(0) AS `count(*)` from `test`.`t1` group by `test`.`t1`.`a`) `res`
DROP TABLE t1;
@@ -4019,7 +4019,7 @@ SELECT a AS out_a, MIN(b) FROM t1 t1_out
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using filesort; Using temporary
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where
SELECT a AS out_a, MIN(b) FROM t1 t1_outer
WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
@@ -4228,7 +4228,7 @@ select a, oref, a in (select max(ie)
from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
@@ -4237,7 +4237,7 @@ select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
@@ -4267,7 +4267,7 @@ Z
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select <in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
drop table t1, t2, t3;
@@ -4408,7 +4408,7 @@ select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z 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 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0))))))) AS `Z` from `test`.`t2`
@@ -4659,7 +4659,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using filesort; Using temporary
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -4820,7 +4820,7 @@ explain
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using filesort; Using temporary
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref a Z
ee NULL 0
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-05-30 11:03:42 +0000
@@ -2108,7 +2108,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start materialize; Scan
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4066,7 +4066,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start materialize; Scan
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start materialize; Scan
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-05-30 11:03:42 +0000
@@ -2107,7 +2107,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, incremental buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4065,7 +4065,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-05-30 11:03:42 +0000
@@ -2111,7 +2111,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, incremental buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4069,7 +4069,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-05-30 11:03:42 +0000
@@ -2111,7 +2111,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, regular buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
@@ -4069,7 +4069,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-05-30 11:03:42 +0000
@@ -2108,7 +2108,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, incremental buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4066,7 +4066,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, incremental buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, regular buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-05-30 11:03:42 +0000
@@ -2108,7 +2108,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, incremental buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4066,7 +4066,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, incremental buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start temporary
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (BNL, regular buffers)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-05-30 11:03:42 +0000
@@ -2108,7 +2108,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start materialize; Scan
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4066,7 +4066,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start materialize; Scan
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2011-05-30 11:03:42 +0000
@@ -2112,7 +2112,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using filesort; Using temporary; Start materialize; Scan
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
@@ -4070,7 +4070,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-05-30 11:03:42 +0000
@@ -2323,7 +2323,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using temporary; Using filesort
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using filesort; Using temporary
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
@@ -4288,7 +4288,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-03-17 08:00:10 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-05-30 11:03:42 +0000
@@ -2253,7 +2253,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using temporary; Using filesort
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using filesort; Using temporary
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
2 DEPENDENT SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
@@ -4211,7 +4211,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-05-30 11:03:42 +0000
@@ -2257,7 +2257,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using temporary; Using filesort
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using filesort; Using temporary
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
2 DEPENDENT SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
@@ -4215,7 +4215,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result 2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-05-30 11:03:42 +0000
@@ -2257,7 +2257,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using temporary; Using filesort
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using filesort; Using temporary
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
2 DEPENDENT SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
@@ -4215,7 +4215,7 @@ Note 1003 select `test`.`t1`.`one` AS `o
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
=== modified file 'mysql-test/r/type_bit_innodb.result'
--- a/mysql-test/r/type_bit_innodb.result 2010-08-17 06:26:41 +0000
+++ b/mysql-test/r/type_bit_innodb.result 2011-05-30 11:03:42 +0000
@@ -233,7 +233,7 @@ a+0 b+0
127 403
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 2 NULL 27 Using where; Using index; Using filesort
+1 SIMPLE t1 range a a 2 NULL 27 Using where; Using filesort; Using index
select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
a+0 b+0
44 307
@@ -256,7 +256,7 @@ a+0 b+0
127 403
explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 2 NULL 8 Using where; Using index; Using filesort
+1 SIMPLE t1 range a a 2 NULL 8 Using where; Using filesort; Using index
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
a+0 b+0
57 135
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2011-03-08 19:14:42 +0000
+++ b/mysql-test/r/view.result 2011-05-30 11:03:42 +0000
@@ -3490,7 +3490,7 @@ a SUM(b)
2 3
EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using filesort; Using temporary
SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
a SUM(b)
1 10
@@ -3537,7 +3537,7 @@ INSERT INTO t3 VALUES
(9,'RTOS_DCFSPOS_SUPERVISOR','RTOS');
EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE profile const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
+1 SIMPLE profile const PRIMARY PRIMARY 4 const 1 Using filesort; Using temporary
1 SIMPLE userrole ref idx_person_id,idx_role_id idx_person_id 4 const 2
1 SIMPLE role eq_ref PRIMARY PRIMARY 4 test.userrole.role_id 1
SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
=== modified file 'mysql-test/suite/innodb/r/innodb_gis.result'
--- a/mysql-test/suite/innodb/r/innodb_gis.result 2011-02-02 14:26:54 +0000
+++ b/mysql-test/suite/innodb/r/innodb_gis.result 2011-05-30 11:03:42 +0000
@@ -402,7 +402,7 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g,
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 filesort; Using temporary
1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_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`
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2011-03-29 07:30:44 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2011-05-30 11:03:42 +0000
@@ -993,7 +993,7 @@ key bkey
key_len 5
ref const
rows 16
-Extra Using where; Using index; Using filesort
+Extra Using where; Using filesort; Using index
SELECT * FROM t2 WHERE b=1 ORDER BY a;
a b c
1 1 1
@@ -1279,7 +1279,7 @@ key b
key_len 5
ref NULL
rows 3
-Extra Using index; Using filesort
+Extra Using filesort; Using index
SELECT * FROM t1 ORDER BY b ASC, a DESC;
a b
1 1
@@ -1295,7 +1295,7 @@ key b
key_len 5
ref NULL
rows 3
-Extra Using index; Using filesort
+Extra Using filesort; Using index
SELECT * FROM t1 ORDER BY b DESC, a ASC;
a b
2 2
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (gleb.shchepa:3360 to 3361) WL#4897 | Gleb Shchepa | 31 May |
