Bug#56814 Explain + subselect + fulltext crashes server

create_sort_index() function overwrites original JOIN_TAB::type field.
At re-execution of subquery overwritten JOIN_TAB::type(JT_ALL) is
used instead of JT_FT. It misleads test_if_skip_sort_order() and
the function tries to find suitable key for the order that should
not be allowed for FULLTEXT(JT_FT) table.
The fix is to restore JOIN_TAB strucures for subselect on re-execution
for EXPLAIN.
Additional fix:
Update TABLE::maybe_null field which
affects list_contains_unique_index() behaviour as it
could have the value(maybe_null==TRUE) based on the
assumption that this join is outer
(see setup_table_map() func).


mysql-test/r/explain.result:
  test case
mysql-test/t/explain.test:
  test case
sql/item_subselect.cc:
  Make subquery uncacheable in case of EXPLAIN. It allows to keep
  original JOIN_TAB::type(see JOIN::save_join_tab) and restore it
  on re-execution.
sql/sql_select.cc:
  -restore JOIN_TAB strucures for subselect on re-execution for EXPLAIN
  -Update TABLE::maybe_null field as it could have
   the value(maybe_null==TRUE) based on the assumption
   that this join is outer(see setup_table_map() func).
   This change is not related to the crash problem but
   affects EXPLAIN results in the test case.

1 files changed, 46 insertions, 0 deletions

diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index f46fe8daaad..4bc6c0409f3 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -251,4 +251,50 @@ EXPLAIN SELECT c1 FROM t1 WHERE c2 = 1 AND c4 = 1 AND c5 = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	c2,c2_2	c2	10	const,const	3	Using where
 DROP TABLE t1;
+#
+# Bug#56814 Explain + subselect + fulltext crashes server
+#
+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 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	Using filesort
+2	SUBQUERY	t1	fulltext	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
+ ON (MATCH(t1.f1) AGAINST (""))
+ 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	Using filesort
+2	SUBQUERY	t1	fulltext	f1	f1	0		1	Using where
+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	Using filesort
+2	SUBQUERY	t1	fulltext	f1	f1	0		1	Using where
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM
+'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))';
+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	Using filesort
+2	SUBQUERY	t1	fulltext	f1	f1	0		1	Using where
+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	Using filesort
+2	SUBQUERY	t1	fulltext	f1	f1	0		1	Using where
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
 End of 5.1 tests.