Backport of WL#5953 from MySQL 5.6

The patch differs from the original MySQL patch as follows:
- All test case differences have been reviewed one by one, and
  care has been taken to restore the original plan so that each
  test case executes the code path it was designed for.
- A bug was found and fixed in MariaDB 5.3 in
  Item_allany_subselect::cleanup().
- ORDER BY is not removed because we are unsure of all effects,
  and it would prevent enabling ORDER BY ... LIMIT subqueries.
- ref_pointer_array.m_size is not adjusted because we don't do
  array bounds checking, and because it looks risky.

Original comment by Jorgen Loland:
-------------------------------------------------------------
WL#5953 - Optimize away useless subquery clauses
      
For IN/ALL/ANY/SOME/EXISTS subqueries, the following clauses are 
meaningless:
      
* ORDER BY (since we don't support LIMIT in these subqueries)
* DISTINCT
* GROUP BY if there is no HAVING clause and no aggregate 
  functions
      
This WL detects and optimizes away these useless parts of the
query during JOIN::prepare()

1 files changed, 3 insertions, 3 deletions

diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index f3d2a567227..52c58f088a1 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -265,7 +265,7 @@ CREATE TABLE t1(f1 VARCHAR(6) NOT NULL,
 FULLTEXT KEY(f1),UNIQUE(f1));
 INSERT INTO t1 VALUES ('test');
 EXPLAIN SELECT 1 FROM t1
-WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
+WHERE 1 > ALL((SELECT t1.f1 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	
@@ -273,7 +273,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
- WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
+ WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a
  ON (MATCH(t1.f1) AGAINST (""))
  WHERE t1.f1 GROUP BY t1.f1))';
 EXECUTE stmt;
@@ -289,7 +289,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 DEALLOCATE PREPARE stmt;
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
- WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a
+ WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a
  ON (MATCH(t1.f1) AGAINST (""))
  WHERE t1.f1 GROUP BY t1.f1))';
 EXECUTE stmt;