List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:October 18 2011 10:23am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3504 to 3505) WL#5953
View as plain text  
 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#5953Jorgen Loland18 Oct