Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY <any non-const-function>

Several problems fixed : 
1. Non constant expressions in UNION ... ORDER BY were not correctly cleaned up
in st_select_lex_unit::cleanup() causing crashes in EXPLAIN EXTENDED because of
fields quoted by these expressions pointing to the already freed temporary table
used to calculate the UNION.
Fixed by correctly cleaning up expressions of any depth.

2. Subqueries in the order by part of UNION ... ORDER BY ... caused a crash in 
EXPLAIN EXTENDED because of a transformation attempt made during EXPLAIN EXTENDED
execution. Fixed by not doing the transformation when in EXPLAIN.

3. Fulltext functions caused crash when in the ORDER BY part of an un-parenthesized
UNION that gets "promoted" to be valid for the whole union, e.g. 
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY MATCHES (a) AGAINST ('abc' IN BOOLEAN MODE).
This is a case that demonstrates a more general problem of parts of the query being
moved to another level. When doing such transformation late in the optimization run
when most of the flags about the contents of the query are already aggregated it's possible 
to "split" the flags so that they correctly reflect the new queries after the transformation.
In specific the ST_SELECT_LEX::ftfunc_list is holding all the free text function for all the 
parts of the second SELECT in the UNION and we don't know what part of that is in the ORDER BY
that we're to move to the UNION level and what part is about the other parts of the second SELECT.
Fixed by throwing and error when such statements are about to be processed by adding a check 
for the presence of MATCH() inside the ORDER BY clause that's going to get promoted to UNION.
To workaround this new limitation one must parenthesize the UNION SELECTs and provide a real 
global ORDER BY for the UNION outside of the parenthesis.

4 files changed, 122 insertions, 5 deletions

diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result
index bc466b5aba7..bd3e79ec5c2 100644
--- a/mysql-test/r/fulltext_order_by.result
+++ b/mysql-test/r/fulltext_order_by.result
@@ -126,7 +126,7 @@ group by
 a.text, b.id, b.betreff
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join
@@ -142,7 +142,7 @@ where
 match(c.beitrag) against ('+abc' in boolean mode)
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join
@@ -158,7 +158,7 @@ where
 match(c.beitrag) against ('+abc' in boolean mode)
 order by 
 match(betreff) against ('+abc' in boolean mode) desc;
-text	id	betreff
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
 (select b.id, b.betreff from t3 b) union 
 (select b.id, b.betreff from t3 b) 
 order by match(betreff) against ('+abc' in boolean mode) desc;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 44a3812725a..dce32d2ced4 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1588,3 +1588,66 @@ Warnings:
 Note	1003	select '0' AS `a` from `test`.`t1` union select '0' AS `a` from `test`.`t1` order by `a`
 DROP TABLE t1;
 End of 5.0 tests
+# 
+# Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY 
+#   <any non-const-function>
+# 
+CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1),(2);
+# Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+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	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (`a` + 12)
+# Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+a
+1
+2
+# Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
+# Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+ERROR 42000: Incorrect usage/placement of 'MATCH()'
+# Should not crash
+(SELECT * FROM t1) UNION (SELECT * FROM t1)
+ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+a
+1
+2
+# Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY (SELECT a FROM t2 WHERE b = 12);
+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	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (select `test`.`t1`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`b` = 12))
+# Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1
+ORDER BY (SELECT a FROM t2 WHERE b = 12);
+a
+1
+2
+# Should not crash
+SELECT * FROM t2 UNION SELECT * FROM t2
+ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
+b
+1
+2
+DROP TABLE t1,t2;
+End of 5.1 tests
diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test
index 074aefbf943..814cd4a5954 100644
--- a/mysql-test/t/fulltext_order_by.test
+++ b/mysql-test/t/fulltext_order_by.test
@@ -80,7 +80,7 @@ CREATE TABLE t3 (
   FULLTEXT KEY betreff (betreff)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
 
---error 1054
+--error ER_CANT_USE_OPTION_HERE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
@@ -100,7 +100,7 @@ group by
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
   
---error 1054
+--error ER_CANT_USE_OPTION_HERE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
@@ -117,6 +117,7 @@ where
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
 
+--error ER_CANT_USE_OPTION_HERE
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index ec169838d59..91fc9546bbe 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -1102,3 +1102,56 @@ DROP TABLE t1;
 
 
 --echo End of 5.0 tests
+
+
+--echo # 
+--echo # Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY 
+--echo #   <any non-const-function>
+--echo # 
+
+CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (1),(2);
+
+--echo # Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+
+--echo # Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
+
+
+--echo # Should not crash
+--error ER_CANT_USE_OPTION_HERE
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+
+--echo # Should not crash
+--error ER_CANT_USE_OPTION_HERE
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+
+--echo # Should not crash
+(SELECT * FROM t1) UNION (SELECT * FROM t1)
+  ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
+
+
+--echo # Should not crash
+EXPLAIN EXTENDED
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY (SELECT a FROM t2 WHERE b = 12);
+
+--echo # Should not crash
+SELECT * FROM t1 UNION SELECT * FROM t1
+  ORDER BY (SELECT a FROM t2 WHERE b = 12);
+
+--echo # Should not crash
+SELECT * FROM t2 UNION SELECT * FROM t2
+  ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
+
+DROP TABLE t1,t2;
+
+
+--echo End of 5.1 tests