3505 Jorgen Loland 2011-10-18 [merge]
Merge WL#5953 into trunk
modified:
mysql-test/r/explain.result
mysql-test/r/fulltext.result
mysql-test/r/group_by.result
mysql-test/r/subquery_mat_none.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
mysql-test/suite/innodb/r/innodb_mysql.result
mysql-test/suite/opt_trace/include/range.inc
mysql-test/suite/opt_trace/r/range_no_prot.result
mysql-test/suite/opt_trace/r/range_ps_prot.result
mysql-test/suite/opt_trace/r/subquery_no_prot.result
mysql-test/suite/opt_trace/r/subquery_ps_prot.result
mysql-test/t/fulltext.test
mysql-test/t/group_by.test
sql/item_subselect.cc
sql/sql_array.h
sql/sql_lex.cc
sql/sql_select.cc
unittest/gunit/bounds_checked_array-t.cc
3504 Raghav Kapoor 2011-10-18
BUG#11757503 - 49556: ERROR 1005 (HY000): CAN'T CREATE TABLE '#SQL-B7C_3' (ERRNO: -1)
BACKGROUND:
When altering some tables it is found in the error log of mysqld:
Found wrong key definition in #sql-1c54_7; Please do
"ALTER TABLE '#sql-1c54_7' FORCE " to fix it!
mysql> ALTER TABLE '#sql-1c54_7' FORCE ;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds
Here There should be backticks around the table name.
mysql> ALTER TABLE `#sql-1c54_7` FORCE;
ERROR 1146 (42S02): Table 'test.#sql-1c54_7' doesn't exist
FIX:
This bug is fixed by putting backticks around %s in the line
"Please do \"ALTER TABLE '%s' FORCE\" to fix it!",in file
sql/table.cc.Also a test case has been added in file
archive.test and the corresponding result file
has also been updated.
modified:
mysql-test/r/archive.result
mysql-test/t/archive.test
sql/table.cc
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result 2011-08-29 11:57:44 +0000
+++ b/mysql-test/r/explain.result 2011-10-18 10:23:09 +0000
@@ -284,9 +284,8 @@ EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
WHERE t1.f1 GROUP BY t1.f1));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
@@ -294,14 +293,12 @@ PREPARE stmt FROM
WHERE t1.f1 GROUP BY t1.f1))';
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
@@ -310,14 +307,12 @@ PREPARE stmt FROM
WHERE t1.f1 GROUP BY t1.f1))';
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
End of 5.1 tests.
=== modified file 'mysql-test/r/fulltext.result'
--- a/mysql-test/r/fulltext.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/fulltext.result 2011-08-26 08:16:16 +0000
@@ -680,12 +680,11 @@ PREPARE stmt FROM
ALL((SELECT 1 FROM t1 JOIN t1 a
ON (MATCH(t1.f1) against (""))
WHERE t1.f1 GROUP BY t1.f1))';
+# See BUG#12888306. Correct result is one row with value 1.
EXECUTE stmt;
1
-1
EXECUTE stmt;
1
-1
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
End of 5.1 tests
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2011-10-12 12:09:14 +0000
+++ b/mysql-test/r/group_by.result 2011-10-18 10:23:09 +0000
@@ -1191,10 +1191,15 @@ a
4
SELECT t1.a FROM t1 GROUP BY t1.a
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
-ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+a
+2
+3
+4
SELECT t1.a FROM t1 GROUP BY t1.a
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
-ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+a
+3
+4
SELECT t1.a FROM t1
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result 2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/subquery_mat_none.result 2011-08-26 08:16:16 +0000
@@ -49,9 +49,9 @@ 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
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ 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`))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -60,9 +60,9 @@ 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
Warnings:
-Note 1003 /* select#1 */ 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#2 */ 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`))))
+Note 1003 /* select#1 */ 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#2 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -93,9 +93,9 @@ explain extended
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t2i range it2i1,it2i3 it2i1 9 NULL 3 100.00 Using where; Using index for group-by
+2 DEPENDENT SUBQUERY t2i index_subquery it2i1,it2i3 it2i1 9 func 2 100.00 Using index; Using where
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,<exists>(/* select#2 */ select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having (<cache>(`test`.`t1i`.`a1`) = <ref_null_helper>(`test`.`t2i`.`b1`))))
+Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i1 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`)))))
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -115,9 +115,9 @@ explain extended
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
+2 DEPENDENT SUBQUERY t2i index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using index; Using where
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(/* select#2 */ select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` having (((<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`) or isnull(`test`.`t2i`.`b2`)) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(`test`.`t2i`.`b2`))))
+Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`)))))
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -181,7 +181,7 @@ id select_type table type possible_keys
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
Warnings:
-Note 1003 /* select#1 */ 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#2 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)) order by `test`.`t2`.`b1`,`test`.`t2`.`b2`))
+Note 1003 /* select#1 */ 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#2 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -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
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
@@ -350,7 +350,7 @@ id select_type table type possible_keys
8 DEPENDENT SUBQUERY t2i index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using index; Using where
NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 (/* select#1 */ 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#2 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%02') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`))))) 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`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select `test`.`t3`.
`c1`,`test`.`t3`.`c2` from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`)))))) union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`))))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t3i on it3i1 where (<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),<exists>(<index_lookup>(<
cache>(`test`.`t3i`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3i`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3i`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1i`.`a1`) = `test`.`t3i`.`c1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t3i`.`c2`)))))))
+Note 1003 (/* select#1 */ 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#2 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%02') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select `test`.`t3`.`c1`,`test`.`t3`.`c2` from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2`
> '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`)))))) union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where ((`test`.`t2i`.`b1` > '0') and (<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t2i`.`b2`))))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t3i on it3i1 where (<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3i`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') and (<cache>(`test`.`t3i`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3i`.`c2`) = `test`.`t2i`.`b2`))))) and
(<cache>(`test`.`t1i`.`a1`) = `test`.`t3i`.`c1`) and (<cache>(`test`.`t1i`.`a2`) = `test`.`t3i`.`c2`)))))))
(select * from t1
where (a1, a2) in (select b1, b2 from t2
where b2 in (select c2 from t3 where c2 LIKE '%02') or
@@ -713,7 +713,7 @@ insert into t1 values (5);
explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
-2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
min(a1)
explain select min(a1) from t1 where 7 in (select b1 from t2);
@@ -769,15 +769,15 @@ 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
Warnings:
-Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(/* select#2 */ select 1 from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
+Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(/* select#2 */ select 1 from `test`.`t1` where 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
Warnings:
-Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(/* select#2 */ select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
+Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(/* select#2 */ select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3)))
DROP TABLE t1;
#
# BUG#49630: Segfault in select_describe() with double
@@ -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 where
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-09-29 12:47:32 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2011-10-18 10:23:09 +0000
@@ -1422,7 +1422,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (/* select#2 */ select max(NULL) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1430,7 +1430,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (/* select#2 */ select min(NULL) from `test`.`t2`)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -5452,8 +5452,6 @@ SELECT * FROM t1
WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
pk a
1 10
-3 30
-2 20
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
INSERT INTO t1 VALUES (1,NULL), (9,NULL);
@@ -6653,7 +6651,7 @@ WHERE f1_key != table2.f1_key AND f1_key
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 2
1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (Block Nested Loop)
-2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Using where; Using index for group-by; Using temporary
+2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index
DROP TABLE t1,t2;
#
# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result 2011-10-18 10:23:09 +0000
@@ -1423,7 +1423,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (/* select#2 */ select max(NULL) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1431,7 +1431,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (/* select#2 */ select min(NULL) from `test`.`t2`)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -5453,8 +5453,6 @@ SELECT * FROM t1
WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
pk a
1 10
-3 30
-2 20
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
INSERT INTO t1 VALUES (1,NULL), (9,NULL);
@@ -6654,7 +6652,7 @@ WHERE f1_key != table2.f1_key AND f1_key
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 2
1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (Block Nested Loop)
-2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Using where; Using index for group-by; Using temporary
+2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index
DROP TABLE t1,t2;
#
# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2011-10-18 10:23:09 +0000
@@ -1423,7 +1423,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (/* select#2 */ select max(NULL) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1431,7 +1431,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (/* select#2 */ select min(NULL) from `test`.`t2`)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -5453,8 +5453,6 @@ SELECT * FROM t1
WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
pk a
1 10
-3 30
-2 20
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
INSERT INTO t1 VALUES (1,NULL), (9,NULL);
@@ -6654,7 +6652,7 @@ WHERE f1_key != table2.f1_key AND f1_key
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 2
1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index
-2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Using where; Using index for group-by; Using temporary
+2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index
DROP TABLE t1,t2;
#
# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2011-09-29 12:47:32 +0000
+++ b/mysql-test/r/subquery_none.result 2011-10-18 10:23:09 +0000
@@ -1421,7 +1421,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (/* select#2 */ select max(NULL) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1429,7 +1429,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (/* select#2 */ select min(NULL) from `test`.`t2`)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -5451,8 +5451,6 @@ SELECT * FROM t1
WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
pk a
1 10
-3 30
-2 20
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
INSERT INTO t1 VALUES (1,NULL), (9,NULL);
@@ -6652,7 +6650,7 @@ WHERE f1_key != table2.f1_key AND f1_key
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 2
1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (Block Nested Loop)
-2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Using where; Using index for group-by; Using temporary
+2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index
DROP TABLE t1,t2;
#
# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/subquery_none_bka.result 2011-10-18 10:23:09 +0000
@@ -1422,7 +1422,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (/* select#2 */ select max(NULL) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1430,7 +1430,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (/* select#2 */ select min(NULL) from `test`.`t2`)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -5452,8 +5452,6 @@ SELECT * FROM t1
WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
pk a
1 10
-3 30
-2 20
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
INSERT INTO t1 VALUES (1,NULL), (9,NULL);
@@ -6653,7 +6651,7 @@ WHERE f1_key != table2.f1_key AND f1_key
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 2
1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (Block Nested Loop)
-2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Using where; Using index for group-by; Using temporary
+2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index
DROP TABLE t1,t2;
#
# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result 2011-10-18 10:23:09 +0000
@@ -1422,7 +1422,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (/* select#2 */ select max(NULL) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
@@ -1430,7 +1430,7 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(/* select#2 */ select NULL from `test`.`t2` group by 1)))
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (/* select#2 */ select min(NULL) from `test`.`t2`)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
@@ -5452,8 +5452,6 @@ SELECT * FROM t1
WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
pk a
1 10
-3 30
-2 20
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
INSERT INTO t1 VALUES (1,NULL), (9,NULL);
@@ -6653,7 +6651,7 @@ WHERE f1_key != table2.f1_key AND f1_key
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 2
1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index
-2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Using where; Using index for group-by; Using temporary
+2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index
DROP TABLE t1,t2;
#
# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-10-07 12:44:17 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-10-18 10:23:09 +0000
@@ -4877,9 +4877,9 @@ Note 1003 /* select#1 */ select `test`.`
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
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ 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_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2011-10-13 07:54:52 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2011-10-18 10:23:09 +0000
@@ -4878,9 +4878,9 @@ Note 1003 /* select#1 */ select `test`.`
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
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ 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_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2011-10-13 07:54:52 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2011-10-18 10:23:09 +0000
@@ -4878,9 +4878,9 @@ Note 1003 /* select#1 */ select `test`.`
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
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ 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_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2011-10-13 07:54:52 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2011-10-18 10:23:09 +0000
@@ -4879,9 +4879,9 @@ Note 1003 /* select#1 */ select `test`.`
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
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ 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_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(/* select#2 */ select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`one`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`two`), true)))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2011-10-12 12:09:14 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2011-10-18 10:23:09 +0000
@@ -1436,8 +1436,7 @@ explain
select b from t1 where a not in (select b from t1,t2 group by a) group by a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1,t2;
End of 5.0 tests
CREATE TABLE `t2` (
=== modified file 'mysql-test/suite/opt_trace/include/range.inc'
--- a/mysql-test/suite/opt_trace/include/range.inc 2011-09-22 12:04:38 +0000
+++ b/mysql-test/suite/opt_trace/include/range.inc 2011-10-18 10:23:09 +0000
@@ -218,21 +218,6 @@ DROP TABLE t1,t2;
SET optimizer_trace_features=default;
-CREATE TABLE `t1` (
- `mot` varchar(4) NOT NULL,
- `topic` int NOT NULL,
- PRIMARY KEY (`mot`,`topic`)
- );
-INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
-
-# rechecking_index_usage/no_indices_to_analyze
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-drop table t1;
-
# Range analysis in test_if_skip_sort_order
# (records_estimation_for_index_ordering)
CREATE TABLE t1 (
=== modified file 'mysql-test/suite/opt_trace/r/range_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_no_prot.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result 2011-10-18 10:23:09 +0000
@@ -4381,382 +4381,6 @@ EXPLAIN SELECT 1 FROM
DROP TABLE t1,t2;
SET optimizer_trace_features=default;
-CREATE TABLE `t1` (
-`mot` varchar(4) NOT NULL,
-`topic` int NOT NULL,
-PRIMARY KEY (`mot`,`topic`)
-);
-INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
-mot topic
-
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic) {
- "steps": [
- {
- "join_preparation": {
- "select#": 1,
- "steps": [
- {
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic`"
- },
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "EXISTS (CORRELATED SELECT)",
- "chosen": true,
- "evaluating_constant_having_conditions": [
- ] /* evaluating_constant_having_conditions */
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "expanded_query": "/* select#1 */ select `test`.`t1`.`mot` AS `mot`,`test`.`t1`.`topic` AS `topic` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
- "steps": [
- {
- "transformation": "equality_propagation",
- "subselect_evaluation": [
- ] /* subselect_evaluation */,
- "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- },
- {
- "transformation": "constant_propagation",
- "subselect_evaluation": [
- ] /* subselect_evaluation */,
- "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- },
- {
- "transformation": "trivial_condition_removal",
- "subselect_evaluation": [
- ] /* subselect_evaluation */,
- "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- }
- ] /* steps */
- } /* condition_processing */
- },
- {
- "table_dependencies": [
- {
- "database": "test",
- "table": "t1",
- "row_may_be_null": false,
- "map_bit": 0,
- "depends_on_map_bits": [
- ] /* depends_on_map_bits */
- }
- ] /* table_dependencies */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "rows_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "rows": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* rows_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "rows": 2,
- "cost": 2.0098,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.4098,
- "rows_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
- "attached_conditions_computation": [
- ] /* attached_conditions_computation */,
- "attached_conditions_summary": [
- {
- "database": "test",
- "table": "t1",
- "attached": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- }
- ] /* attached_conditions_summary */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "access_type": "index_scan"
- }
- ] /* refine_plan */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_execution": {
- "select#": 1,
- "steps": [
- {
- "subselect_execution": {
- "select#": 2,
- "steps": [
- {
- "join_optimization": {
- "select#": 2,
- "steps": [
- {
- "condition_processing": {
- "condition": "HAVING",
- "original_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))",
- "steps": [
- {
- "transformation": "constant_propagation",
- "resulting_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
- },
- {
- "transformation": "trivial_condition_removal",
- "resulting_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
- }
- ] /* steps */
- } /* condition_processing */
- },
- {
- "table_dependencies": [
- {
- "database": "test",
- "table": "t1",
- "row_may_be_null": false,
- "map_bit": 0,
- "depends_on_map_bits": [
- ] /* depends_on_map_bits */
- }
- ] /* table_dependencies */
- },
- {
- "rows_estimation": [
- {
- "database": "test",
- "table": "t1",
- "const_keys_added": {
- "keys": [
- "PRIMARY"
- ] /* keys */,
- "cause": "group_by"
- } /* const_keys_added */,
- "range_analysis": {
- "table_scan": {
- "rows": 2,
- "cost": 4.5098
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "PRIMARY",
- "usable": true,
- "key_parts": [
- "mot",
- "topic"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "PRIMARY",
- "cost": 1.4303,
- "chosen": true
- } /* best_covering_index_scan */,
- "group_index_range": {
- "potential_group_range_indices": [
- {
- "index": "PRIMARY",
- "covering": true,
- "usable": false,
- "cause": "group_attribute_not_prefix_in_index"
- }
- ] /* potential_group_range_indices */
- } /* group_index_range */
- } /* range_analysis */
- }
- ] /* rows_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "rows": 2,
- "cost": 2.0098,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.4098,
- "rows_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions_computation": [
- {
- "database": "test",
- "table": "t1",
- "rechecking_index_usage": {
- } /* rechecking_index_usage */
- }
- ] /* attached_conditions_computation */,
- "attached_conditions_summary": [
- {
- "database": "test",
- "table": "t1",
- "attached": null
- }
- ] /* attached_conditions_summary */
- } /* attaching_conditions_to_tables */
- },
- {
- "clause_processing": {
- "clause": "GROUP BY",
- "original_clause": "`test`.`t1`.`topic`",
- "items": [
- {
- "item": "`test`.`t1`.`topic`"
- }
- ] /* items */,
- "resulting_clause_is_simple": true,
- "resulting_clause": "`test`.`t1`.`topic`"
- } /* clause_processing */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "access_type": "index_scan"
- }
- ] /* refine_plan */
- },
- {
- "reconsidering_access_paths_for_index_ordering": {
- } /* reconsidering_access_paths_for_index_ordering */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_execution": {
- "select#": 2,
- "steps": [
- {
- "filesort_information": [
- {
- "direction": "asc",
- "database": "",
- "table": "",
- "field": "topic"
- }
- ] /* filesort_information */,
- "filesort_priority_queue_optimization": {
- "usable": false,
- "cause": "not applicable (no LIMIT)"
- } /* filesort_priority_queue_optimization */,
- "filesort_execution": [
- ] /* filesort_execution */,
- "filesort_summary": {
- "records": 2,
- "number_of_tmp_files": 0,
- "sort_mode": "<sort_key, rowid>"
- } /* filesort_summary */
- }
- ] /* steps */
- } /* join_execution */
- }
- ] /* steps */
- } /* subselect_execution */
- },
- {
- "subselect_execution": {
- "select#": 2,
- "steps": [
- {
- "join_execution": {
- "select#": 2,
- "steps": [
- {
- "filesort_information": [
- {
- "direction": "asc",
- "database": "",
- "table": "",
- "field": "topic"
- }
- ] /* filesort_information */,
- "filesort_priority_queue_optimization": {
- "usable": false,
- "cause": "not applicable (no LIMIT)"
- } /* filesort_priority_queue_optimization */,
- "filesort_execution": [
- ] /* filesort_execution */,
- "filesort_summary": {
- "records": 2,
- "number_of_tmp_files": 0,
- "sort_mode": "<sort_key, rowid>"
- } /* filesort_summary */
- }
- ] /* steps */
- } /* join_execution */
- }
- ] /* steps */
- } /* subselect_execution */
- }
- ] /* steps */
- } /* join_execution */
- }
- ] /* steps */
-} 0 0
-
-drop table t1;
CREATE TABLE t1 (
i1 int,
i2 int,
=== modified file 'mysql-test/suite/opt_trace/r/range_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_ps_prot.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/suite/opt_trace/r/range_ps_prot.result 2011-10-18 10:23:09 +0000
@@ -4381,372 +4381,6 @@ EXPLAIN SELECT 1 FROM
DROP TABLE t1,t2;
SET optimizer_trace_features=default;
-CREATE TABLE `t1` (
-`mot` varchar(4) NOT NULL,
-`topic` int NOT NULL,
-PRIMARY KEY (`mot`,`topic`)
-);
-INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
-mot topic
-
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic) {
- "steps": [
- {
- "join_preparation": {
- "select#": 1,
- "steps": [
- {
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "expanded_query": "/* select#1 */ select `test`.`t1`.`mot` AS `mot`,`test`.`t1`.`topic` AS `topic` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- }
- ] /* steps */
- } /* join_preparation */
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
- "steps": [
- {
- "transformation": "equality_propagation",
- "subselect_evaluation": [
- ] /* subselect_evaluation */,
- "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- },
- {
- "transformation": "constant_propagation",
- "subselect_evaluation": [
- ] /* subselect_evaluation */,
- "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- },
- {
- "transformation": "trivial_condition_removal",
- "subselect_evaluation": [
- ] /* subselect_evaluation */,
- "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- }
- ] /* steps */
- } /* condition_processing */
- },
- {
- "table_dependencies": [
- {
- "database": "test",
- "table": "t1",
- "row_may_be_null": false,
- "map_bit": 0,
- "depends_on_map_bits": [
- ] /* depends_on_map_bits */
- }
- ] /* table_dependencies */
- },
- {
- "ref_optimizer_key_uses": [
- ] /* ref_optimizer_key_uses */
- },
- {
- "rows_estimation": [
- {
- "database": "test",
- "table": "t1",
- "table_scan": {
- "rows": 2,
- "cost": 2
- } /* table_scan */
- }
- ] /* rows_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "rows": 2,
- "cost": 2.0098,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.4098,
- "rows_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
- "attached_conditions_computation": [
- ] /* attached_conditions_computation */,
- "attached_conditions_summary": [
- {
- "database": "test",
- "table": "t1",
- "attached": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
- }
- ] /* attached_conditions_summary */
- } /* attaching_conditions_to_tables */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "access_type": "index_scan"
- }
- ] /* refine_plan */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_execution": {
- "select#": 1,
- "steps": [
- {
- "subselect_execution": {
- "select#": 2,
- "steps": [
- {
- "join_optimization": {
- "select#": 2,
- "steps": [
- {
- "condition_processing": {
- "condition": "HAVING",
- "original_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))",
- "steps": [
- {
- "transformation": "constant_propagation",
- "resulting_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
- },
- {
- "transformation": "trivial_condition_removal",
- "resulting_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
- }
- ] /* steps */
- } /* condition_processing */
- },
- {
- "table_dependencies": [
- {
- "database": "test",
- "table": "t1",
- "row_may_be_null": false,
- "map_bit": 0,
- "depends_on_map_bits": [
- ] /* depends_on_map_bits */
- }
- ] /* table_dependencies */
- },
- {
- "rows_estimation": [
- {
- "database": "test",
- "table": "t1",
- "const_keys_added": {
- "keys": [
- "PRIMARY"
- ] /* keys */,
- "cause": "group_by"
- } /* const_keys_added */,
- "range_analysis": {
- "table_scan": {
- "rows": 2,
- "cost": 4.5098
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "PRIMARY",
- "usable": true,
- "key_parts": [
- "mot",
- "topic"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "PRIMARY",
- "cost": 1.4303,
- "chosen": true
- } /* best_covering_index_scan */,
- "group_index_range": {
- "potential_group_range_indices": [
- {
- "index": "PRIMARY",
- "covering": true,
- "usable": false,
- "cause": "group_attribute_not_prefix_in_index"
- }
- ] /* potential_group_range_indices */
- } /* group_index_range */
- } /* range_analysis */
- }
- ] /* rows_estimation */
- },
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t1",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "rows": 2,
- "cost": 2.0098,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.4098,
- "rows_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions_computation": [
- {
- "database": "test",
- "table": "t1",
- "rechecking_index_usage": {
- } /* rechecking_index_usage */
- }
- ] /* attached_conditions_computation */,
- "attached_conditions_summary": [
- {
- "database": "test",
- "table": "t1",
- "attached": null
- }
- ] /* attached_conditions_summary */
- } /* attaching_conditions_to_tables */
- },
- {
- "clause_processing": {
- "clause": "GROUP BY",
- "original_clause": "`test`.`t1`.`topic`",
- "items": [
- {
- "item": "`test`.`t1`.`topic`"
- }
- ] /* items */,
- "resulting_clause_is_simple": true,
- "resulting_clause": "`test`.`t1`.`topic`"
- } /* clause_processing */
- },
- {
- "refine_plan": [
- {
- "database": "test",
- "table": "t1",
- "access_type": "index_scan"
- }
- ] /* refine_plan */
- },
- {
- "reconsidering_access_paths_for_index_ordering": {
- } /* reconsidering_access_paths_for_index_ordering */
- }
- ] /* steps */
- } /* join_optimization */
- },
- {
- "join_execution": {
- "select#": 2,
- "steps": [
- {
- "filesort_information": [
- {
- "direction": "asc",
- "database": "",
- "table": "",
- "field": "topic"
- }
- ] /* filesort_information */,
- "filesort_priority_queue_optimization": {
- "usable": false,
- "cause": "not applicable (no LIMIT)"
- } /* filesort_priority_queue_optimization */,
- "filesort_execution": [
- ] /* filesort_execution */,
- "filesort_summary": {
- "records": 2,
- "number_of_tmp_files": 0,
- "sort_mode": "<sort_key, rowid>"
- } /* filesort_summary */
- }
- ] /* steps */
- } /* join_execution */
- }
- ] /* steps */
- } /* subselect_execution */
- },
- {
- "subselect_execution": {
- "select#": 2,
- "steps": [
- {
- "join_execution": {
- "select#": 2,
- "steps": [
- {
- "filesort_information": [
- {
- "direction": "asc",
- "database": "",
- "table": "",
- "field": "topic"
- }
- ] /* filesort_information */,
- "filesort_priority_queue_optimization": {
- "usable": false,
- "cause": "not applicable (no LIMIT)"
- } /* filesort_priority_queue_optimization */,
- "filesort_execution": [
- ] /* filesort_execution */,
- "filesort_summary": {
- "records": 2,
- "number_of_tmp_files": 0,
- "sort_mode": "<sort_key, rowid>"
- } /* filesort_summary */
- }
- ] /* steps */
- } /* join_execution */
- }
- ] /* steps */
- } /* subselect_execution */
- }
- ] /* steps */
- } /* join_execution */
- }
- ] /* steps */
-} 0 0
-
-drop table t1;
CREATE TABLE t1 (
i1 int,
i2 int,
=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2011-10-18 10:23:09 +0000
@@ -825,13 +825,13 @@ field4,field5,field6 {
"select#": 3,
"steps": [
{
- "expanded_query": "/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)"
+ "expanded_query": "/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)"
}
] /* steps */
} /* join_preparation */
},
{
- "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))"
+ "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))"
}
] /* steps */
} /* join_preparation */
@@ -883,7 +883,7 @@ field4,field5,field6 {
} /* join_preparation */
},
{
- "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar_n
okey` AS `field5`,`alias2`.`col_varchar_nokey` AS `field6` from ((`test`.`t4` `alias3` left join ((/* select#5 */ select `test`.`sq3_alias2`.`pk` AS `pk`,`test`.`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`test`.`sq3_alias2`.`col_int_key` AS `col_int_key`,`test`.`sq3_alias2`.`col_date_key` AS `col_date_key`,`test`.`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`test`.`sq3_alias2`.`col_time_key` AS `col_time_key`,`test`.`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`test`.`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`test`.`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`test`.`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`test`.`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`test`.`t5` `sq3_alias1` join `test`.`t4` `sq3_alias2`))) `alias2` on((`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) left join `test`.`t5` `alias1` on((`test`.`alias3`.`col_int_key` = `alias2`.`pk`))) where (<in_optimizer>(`test`.`alia
s1`.`col_varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.`col_varchar_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nok
ey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
+ "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar_nokey` AS
`field5`,`alias2`.`col_varchar_nokey` AS `field6` from ((`test`.`t4` `alias3` left join ((/* select#5 */ select `test`.`sq3_alias2`.`pk` AS `pk`,`test`.`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`test`.`sq3_alias2`.`col_int_key` AS `col_int_key`,`test`.`sq3_alias2`.`col_date_key` AS `col_date_key`,`test`.`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`test`.`sq3_alias2`.`col_time_key` AS `col_time_key`,`test`.`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`test`.`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`test`.`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`test`.`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`test`.`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`test`.`t5` `sq3_alias1` join `test`.`t4` `sq3_alias2`))) `alias2` on((`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) left join `test`.`t5` `alias1` on((`test`.`alias3`.`col_int_key` = `alias2`.`pk`))) where (<in_optimizer>(`test`.`alias1`.`col_
varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.`col_varchar_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `te
st`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
}
] /* steps */
} /* join_preparation */
@@ -997,7 +997,7 @@ field4,field5,field6 {
"JOIN_condition_to_WHERE",
"parenthesis_removal"
] /* transformations */,
- "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar
_nokey` AS `field5`,`alias2`.`col_varchar_nokey` AS `field6` from `test`.`t4` `alias3` join ((/* select#5 */ select '0' AS `pk`,'7' AS `col_int_nokey`,'8' AS `col_int_key`,'2008-10-02' AS `col_date_key`,'2008-10-02' AS `col_date_nokey`,'04:07:22' AS `col_time_key`,'04:07:22' AS `col_time_nokey`,'2001-10-08 00:00:00' AS `col_datetime_key`,'2001-10-08 00:00:00' AS `col_datetime_nokey`,'g' AS `col_varchar_key`,'g' AS `col_varchar_nokey` from `test`.`t5` `sq3_alias1`)) `alias2` join `test`.`t5` `alias1` where (<in_optimizer>(`test`.`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.
`col_varchar_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214) and (`test`.`alias3`.`col_int_key` = `alias2`.`pk`) and (`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varc
har_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
+ "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar_nokey` A
S `field5`,`alias2`.`col_varchar_nokey` AS `field6` from `test`.`t4` `alias3` join ((/* select#5 */ select '0' AS `pk`,'7' AS `col_int_nokey`,'8' AS `col_int_key`,'2008-10-02' AS `col_date_key`,'2008-10-02' AS `col_date_nokey`,'04:07:22' AS `col_time_key`,'04:07:22' AS `col_time_nokey`,'2001-10-08 00:00:00' AS `col_datetime_key`,'2001-10-08 00:00:00' AS `col_datetime_nokey`,'g' AS `col_varchar_key`,'g' AS `col_varchar_nokey` from `test`.`t5` `sq3_alias1`)) `alias2` join `test`.`t5` `alias1` where (<in_optimizer>(`test`.`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.`col_varc
har_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214) and (`test`.`alias3`.`col_int_key` = `alias2`.`pk`) and (`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.
`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
} /* transformations_to_nested_joins */
},
{
@@ -1298,7 +1298,7 @@ field4,field5,field6 {
{
"clause_processing": {
"clause": "GROUP BY",
- "original_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`",
+ "original_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`",
"items": [
{
"item": "`test`.`alias1`.`col_varchar_key`"
@@ -1307,7 +1307,7 @@ field4,field5,field6 {
"item": "`test`.`alias1`.`col_date_key`"
},
{
- "item": "(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)))",
+ "item": "(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)))",
"subselect_evaluation": [
{
"subselect_execution": {
@@ -1324,31 +1324,31 @@ field4,field5,field6 {
"JOIN_condition_to_WHERE",
"parenthesis_removal"
] /* transformations */,
- "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `test`.`t1` `sq1_alias1` join `test`.`t5` `sq1_alias2` join `test`.`t5` `sq1_alias3` where (exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))"
+ "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `test`.`t1` `sq1_alias1` join `test`.`t5` `sq1_alias2` join `test`.`t5` `sq1_alias3` where (exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))"
} /* transformations_to_nested_joins */
},
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))",
+ "original_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))",
"steps": [
{
"transformation": "equality_propagation",
"subselect_evaluation": [
] /* subselect_evaluation */,
- "resulting_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
+ "resulting_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
},
{
"transformation": "constant_propagation",
"subselect_evaluation": [
] /* subselect_evaluation */,
- "resulting_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
+ "resulting_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
},
{
"transformation": "trivial_condition_removal",
"subselect_evaluation": [
] /* subselect_evaluation */,
- "resulting_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
+ "resulting_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
}
] /* steps */
} /* condition_processing */
=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2011-10-18 10:23:09 +0000
@@ -825,13 +825,13 @@ field4,field5,field6 {
"select#": 3,
"steps": [
{
- "expanded_query": "/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)"
+ "expanded_query": "/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)"
}
] /* steps */
} /* join_preparation */
},
{
- "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))"
+ "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))"
}
] /* steps */
} /* join_preparation */
@@ -873,7 +873,7 @@ field4,field5,field6 {
} /* join_preparation */
},
{
- "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar_n
okey` AS `field5`,`alias2`.`col_varchar_nokey` AS `field6` from ((`test`.`t4` `alias3` left join ((/* select#5 */ select `test`.`sq3_alias2`.`pk` AS `pk`,`test`.`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`test`.`sq3_alias2`.`col_int_key` AS `col_int_key`,`test`.`sq3_alias2`.`col_date_key` AS `col_date_key`,`test`.`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`test`.`sq3_alias2`.`col_time_key` AS `col_time_key`,`test`.`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`test`.`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`test`.`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`test`.`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`test`.`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`test`.`t5` `sq3_alias1` join `test`.`t4` `sq3_alias2`))) `alias2` on((`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) left join `test`.`t5` `alias1` on((`test`.`alias3`.`col_int_key` = `alias2`.`pk`))) where (<in_optimizer>(`test`.`alia
s1`.`col_varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.`col_varchar_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nok
ey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
+ "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar_nokey` AS
`field5`,`alias2`.`col_varchar_nokey` AS `field6` from ((`test`.`t4` `alias3` left join ((/* select#5 */ select `test`.`sq3_alias2`.`pk` AS `pk`,`test`.`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`test`.`sq3_alias2`.`col_int_key` AS `col_int_key`,`test`.`sq3_alias2`.`col_date_key` AS `col_date_key`,`test`.`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`test`.`sq3_alias2`.`col_time_key` AS `col_time_key`,`test`.`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`test`.`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`test`.`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`test`.`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`test`.`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`test`.`t5` `sq3_alias1` join `test`.`t4` `sq3_alias2`))) `alias2` on((`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) left join `test`.`t5` `alias1` on((`test`.`alias3`.`col_int_key` = `alias2`.`pk`))) where (<in_optimizer>(`test`.`alias1`.`col_
varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.`col_varchar_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `te
st`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
}
] /* steps */
} /* join_preparation */
@@ -987,7 +987,7 @@ field4,field5,field6 {
"JOIN_condition_to_WHERE",
"parenthesis_removal"
] /* transformations */,
- "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar
_nokey` AS `field5`,`alias2`.`col_varchar_nokey` AS `field6` from `test`.`t4` `alias3` join ((/* select#5 */ select '0' AS `pk`,'7' AS `col_int_nokey`,'8' AS `col_int_key`,'2008-10-02' AS `col_date_key`,'2008-10-02' AS `col_date_nokey`,'04:07:22' AS `col_time_key`,'04:07:22' AS `col_time_nokey`,'2001-10-08 00:00:00' AS `col_datetime_key`,'2001-10-08 00:00:00' AS `col_datetime_nokey`,'g' AS `col_varchar_key`,'g' AS `col_varchar_nokey` from `test`.`t5` `sq3_alias1`)) `alias2` join `test`.`t5` `alias1` where (<in_optimizer>(`test`.`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.
`col_varchar_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214) and (`test`.`alias3`.`col_int_key` = `alias2`.`pk`) and (`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varc
har_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
+ "expanded_query": "/* select#1 */ select distinct `test`.`alias1`.`col_varchar_key` AS `field1`,`test`.`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`) AS `field4`,`alias2`.`col_varchar_nokey` A
S `field5`,`alias2`.`col_varchar_nokey` AS `field6` from `test`.`t4` `alias3` join ((/* select#5 */ select '0' AS `pk`,'7' AS `col_int_nokey`,'8' AS `col_int_key`,'2008-10-02' AS `col_date_key`,'2008-10-02' AS `col_date_nokey`,'04:07:22' AS `col_time_key`,'04:07:22' AS `col_time_nokey`,'2001-10-08 00:00:00' AS `col_datetime_key`,'2001-10-08 00:00:00' AS `col_datetime_nokey`,'g' AS `col_varchar_key`,'g' AS `col_varchar_nokey` from `test`.`t5` `sq3_alias1`)) `alias2` join `test`.`t5` `alias1` where (<in_optimizer>(`test`.`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select 1 from (`test`.`t3` `sq4_alias1` join (`test`.`t3` `sq4_alias3` left join `test`.`t2` `sq4_alias2` on((`test`.`sq4_alias3`.`pk` = `test`.`sq4_alias2`.`col_int_key`))) on((`test`.`sq4_alias3`.`col_varchar_nokey` = `test`.`sq4_alias2`.`col_varchar_key`))) where ((`test`.`sq4_alias2`.`col_int_key` < `test`.`alias1`.`col_int_nokey`) and (`test`.`sq4_alias3`.`col_varchar_nokey` <> `test`.`alias1`.`col_varc
har_key`) and (<cache>(`test`.`alias1`.`col_varchar_nokey`) = `test`.`sq4_alias1`.`col_varchar_key`)))) and (`test`.`alias1`.`col_int_key` <> 214) and (`test`.`alias3`.`col_int_key` = `alias2`.`pk`) and (`test`.`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`)) group by `test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.
`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
} /* transformations_to_nested_joins */
},
{
@@ -1288,7 +1288,7 @@ field4,field5,field6 {
{
"clause_processing": {
"clause": "GROUP BY",
- "original_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`",
+ "original_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`))),(/* select#4 */ select max(`test`.`sq2_alias1`.`pk`) AS `sq2_field1` from `test`.`t5` `sq2_alias1`),`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`",
"items": [
{
"item": "`test`.`alias1`.`col_varchar_key`"
@@ -1297,7 +1297,7 @@ field4,field5,field6 {
"item": "`test`.`alias1`.`col_date_key`"
},
{
- "item": "(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)))",
+ "item": "(/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`test`.`t1` `sq1_alias1` join (`test`.`t5` `sq1_alias2` left join `test`.`t5` `sq1_alias3` on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) on((`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)))",
"subselect_evaluation": [
{
"subselect_execution": {
@@ -1314,31 +1314,31 @@ field4,field5,field6 {
"JOIN_condition_to_WHERE",
"parenthesis_removal"
] /* transformations */,
- "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `test`.`t1` `sq1_alias1` join `test`.`t5` `sq1_alias2` join `test`.`t5` `sq1_alias3` where (exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))"
+ "expanded_query": "/* select#2 */ select min(`test`.`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `test`.`t1` `sq1_alias1` join `test`.`t5` `sq1_alias2` join `test`.`t5` `sq1_alias3` where (exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))"
} /* transformations_to_nested_joins */
},
{
"condition_processing": {
"condition": "WHERE",
- "original_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))",
+ "original_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`) and (`test`.`sq1_alias3`.`col_varchar_nokey` = `test`.`sq1_alias2`.`col_varchar_key`))",
"steps": [
{
"transformation": "equality_propagation",
"subselect_evaluation": [
] /* subselect_evaluation */,
- "resulting_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
+ "resulting_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
},
{
"transformation": "constant_propagation",
"subselect_evaluation": [
] /* subselect_evaluation */,
- "resulting_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
+ "resulting_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
},
{
"transformation": "trivial_condition_removal",
"subselect_evaluation": [
] /* subselect_evaluation */,
- "resulting_condition": "(exists(/* select#3 */ select distinct `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
+ "resulting_condition": "(exists(/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`test`.`sq1_alias3`.`col_varchar_nokey`, `test`.`sq1_alias2`.`col_varchar_key`))"
}
] /* steps */
} /* condition_processing */
=== modified file 'mysql-test/t/fulltext.test'
--- a/mysql-test/t/fulltext.test 2010-12-17 11:28:59 +0000
+++ b/mysql-test/t/fulltext.test 2011-08-26 08:16:16 +0000
@@ -623,6 +623,7 @@ PREPARE stmt FROM
ON (MATCH(t1.f1) against (""))
WHERE t1.f1 GROUP BY t1.f1))';
+--echo # See BUG#12888306. Correct result is one row with value 1.
EXECUTE stmt;
EXECUTE stmt;
=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test 2011-09-26 13:48:06 +0000
+++ b/mysql-test/t/group_by.test 2011-10-18 10:23:09 +0000
@@ -870,10 +870,10 @@ SELECT t1.a FROM t1 GROUP BY t1.a
SELECT t1.a FROM t1 GROUP BY t1.a
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
---error 1140
+# No error since ORDER BY is optimized away in IN/ALL/ANY subqueries
SELECT t1.a FROM t1 GROUP BY t1.a
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
---error 1140
+# No error since ORDER BY is optimized away in IN/ALL/ANY subqueries
SELECT t1.a FROM t1 GROUP BY t1.a
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2011-10-05 10:19:50 +0000
+++ b/sql/item_subselect.cc 2011-10-18 10:23:09 +0000
@@ -1867,18 +1867,19 @@ Item_in_subselect::select_in_like_transf
DBUG_ENTER("Item_in_subselect::select_in_like_transformer");
+#ifndef DBUG_OFF
{
/*
- IN/SOME/ALL/ANY subqueries aren't support LIMIT clause. Without it
- ORDER BY clause becomes meaningless thus we drop it here.
+ IN/SOME/ALL/ANY subqueries don't support LIMIT clause. Without
+ it, ORDER BY becomes meaningless and should already have been
+ removed in resolve_subquery()
*/
SELECT_LEX *sl= current->master_unit()->first_select();
for (; sl; sl= sl->next_select())
- {
if (sl->join)
- sl->join->order= 0;
- }
+ DBUG_ASSERT(!sl->join->order);
}
+#endif
if (changed)
DBUG_RETURN(RES_OK);
=== modified file 'sql/sql_array.h'
--- a/sql/sql_array.h 2011-08-25 13:51:44 +0000
+++ b/sql/sql_array.h 2011-10-18 10:23:09 +0000
@@ -47,6 +47,17 @@ public:
m_size= size;
}
+ /**
+ Set a new bound on the array. Does not resize the underlying
+ array, so the new size must be smaller than or equal to the
+ current size.
+ */
+ void resize(size_t new_size)
+ {
+ DBUG_ASSERT(new_size <= m_size);
+ m_size= new_size;
+ }
+
Element_type &operator[](size_t n)
{
DBUG_ASSERT(n < m_size);
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2011-10-12 12:09:14 +0000
+++ b/sql/sql_lex.cc 2011-10-18 10:23:09 +0000
@@ -2187,6 +2187,15 @@ bool st_select_lex::setup_ref_array(THD
order_group_num));
if (!ref_pointer_array.is_null())
{
+ /*
+ The Query may have been permanently transformed by removal of
+ ORDER BY or GROUP BY. Memory has already been allocated, but by
+ reducing the size of ref_pointer_array a tight bound is
+ maintained by Bounds_checked_array
+ */
+ if (ref_pointer_array.size() > n_elems)
+ ref_pointer_array.resize(n_elems);
+
DBUG_ASSERT(ref_pointer_array.size() == n_elems);
return false;
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-10-13 07:54:52 +0000
+++ b/sql/sql_select.cc 2011-10-18 10:23:09 +0000
@@ -559,6 +559,69 @@ fix_inner_refs(THD *thd, List<Item> &all
}
#define MAGIC_IN_WHERE_TOP_LEVEL 10
+
+/**
+ Since LIMIT is not supported for table subquery predicates
+ (IN/ALL/EXISTS/etc), the following clauses are redundant for
+ subqueries:
+
+ ORDER BY
+ DISTINCT
+ GROUP BY if there are no aggregate functions and no HAVING
+ clause
+
+ Because redundant clauses are removed both from JOIN and
+ select_lex, the removal is permanent. Thus, it only makes sense to
+ call this function for normal queries and on first execution of
+ SP/PS
+
+ @param subq_select_lex select_lex that is part of a subquery
+ predicate. This object and the associated
+ join is modified.
+*/
+static
+void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex)
+{
+ Item_subselect *subq_predicate= subq_select_lex->master_unit()->item;
+ /*
+ The removal should happen for IN, ALL, ANY and EXISTS subqueries,
+ which means all but single row subqueries. Example single row
+ subqueries:
+ a) SELECT * FROM t1 WHERE t1.a = (<single row subquery>)
+ b) SELECT a, (<single row subquery) FROM t1
+ */
+ if (subq_predicate->substype() == Item_subselect::SINGLEROW_SUBS)
+ return;
+
+ // A subquery that is not single row should be one of IN/ALL/ANY/EXISTS.
+ DBUG_ASSERT (subq_predicate->substype() == Item_subselect::EXISTS_SUBS ||
+ subq_predicate->substype() == Item_subselect::IN_SUBS ||
+ subq_predicate->substype() == Item_subselect::ALL_SUBS ||
+ subq_predicate->substype() == Item_subselect::ANY_SUBS);
+ if (subq_select_lex->order_list.elements)
+ {
+ subq_select_lex->join->order= NULL;
+ subq_select_lex->order_list.empty();
+ }
+
+ if (subq_select_lex->options & SELECT_DISTINCT)
+ {
+ subq_select_lex->join->select_distinct= false;
+ subq_select_lex->options&= ~SELECT_DISTINCT;
+ }
+
+ /*
+ Remove GROUP BY if there are no aggregate functions and no HAVING
+ clause
+ */
+ if (subq_select_lex->group_list.elements &&
+ !subq_select_lex->with_sum_func && !subq_select_lex->join->having)
+ {
+ subq_select_lex->join->group_list= NULL;
+ subq_select_lex->group_list.empty();
+ }
+}
+
/**
Function to setup clauses without sum functions.
*/
@@ -653,6 +716,22 @@ JOIN::prepare(TABLE_LIST *tables_init,
trace_prepare.add_select_number(select_lex->select_number);
Opt_trace_array trace_steps(trace, "steps");
+ /*
+ Permanently remove redundant parts from the query if
+ 1) This is a subquery
+ 2) This is the first time this query is optimized (since the
+ transformation is permanent
+ 3) Not normalizing a view. Removal should take place when a
+ query involving a view is optimized, not when the view
+ is created
+ */
+ if (select_lex->master_unit()->item && // 1)
+ select_lex->first_cond_optimization && // 2)
+ !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)) // 3)
+ {
+ remove_redundant_subquery_clauses(select_lex);
+ }
+
/* Check that all tables, fields, conds and order are ok */
if (!(select_options & OPTION_SETUP_TABLES_DONE) &&
@@ -1000,7 +1079,7 @@ bool resolve_subquery(THD *thd, JOIN *jo
semi-join (which is done in flatten_subqueries()). The requirements are:
1. Subquery predicate is an IN/=ANY subquery predicate
2. Subquery is a single SELECT (not a UNION)
- 3. Subquery does not have GROUP BY or ORDER BY
+ 3. Subquery does not have GROUP BY
4. Subquery does not use aggregate functions or HAVING
5. Subquery predicate is at the AND-top-level of ON/WHERE clause
6. We are not in a subquery of a single table UPDATE/DELETE that
@@ -1019,7 +1098,7 @@ bool resolve_subquery(THD *thd, JOIN *jo
if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_SEMIJOIN) &&
subq_predicate_substype == Item_subselect::IN_SUBS && // 1
!select_lex->is_part_of_union() && // 2
- !select_lex->group_list.elements && !join->order && // 3
+ !select_lex->group_list.elements && // 3
!join->having && !select_lex->with_sum_func && // 4
(outer->resolve_place == st_select_lex::RESOLVE_CONDITION || // 5
outer->resolve_place == st_select_lex::RESOLVE_JOIN_NEST) && // 5
=== modified file 'unittest/gunit/bounds_checked_array-t.cc'
--- a/unittest/gunit/bounds_checked_array-t.cc 2011-06-24 09:29:07 +0000
+++ b/unittest/gunit/bounds_checked_array-t.cc 2011-08-26 08:16:16 +0000
@@ -81,6 +81,24 @@ TEST_F(BoundsCheckedArrayDeathTest, Boun
".*Assertion .*m_size > 0.*");
}
+TEST_F(BoundsCheckedArrayDeathTest, BoundsCheckResize)
+{
+ ::testing::FLAGS_gtest_death_test_style = "threadsafe";
+ int_array= Int_array(c_array, 1);
+ EXPECT_DEATH_IF_SUPPORTED(int_array.resize(2),
+ ".*Assertion .*new_size <= m_size.*");
+}
+
+TEST_F(BoundsCheckedArrayDeathTest, BoundsCheckResizeAssign)
+{
+ ::testing::FLAGS_gtest_death_test_style = "threadsafe";
+ int_array= Int_array(c_array, 2);
+ int_array[1]= some_integer;
+ int_array.resize(1);
+ EXPECT_DEATH_IF_SUPPORTED(int_array[1]= some_integer,
+ ".*Assertion .*n < m_size.*");
+}
+
#endif // !defined(DBUG_OFF)
TEST_F(BoundsCheckedArray, Indexing)
@@ -103,6 +121,23 @@ TEST_F(BoundsCheckedArray, Reset)
EXPECT_TRUE(int_array.is_null());
}
+TEST_F(BoundsCheckedArray, Resize)
+{
+ int_array= Int_array(c_array, c_array_size);
+ int_array.resize(c_array_size - 1);
+ EXPECT_EQ(c_array_size - 1, static_cast<int>(int_array.size()));
+
+ int count= 0;
+ while (int_array.size() > 0)
+ {
+ EXPECT_EQ(count, int_array[0]);
+ count++;
+ int_array.pop_front();
+ }
+
+ EXPECT_EQ(count, c_array_size - 1);
+}
+
TEST_F(BoundsCheckedArray, PopFront)
{
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3504 to 3505) WL#5953 | Jorgen Loland | 18 Oct |
