Fix LP BUG#680038

Analysis:
Single-row subqueries are not considered expensive and are
evaluated both during EXPLAIN in to detect errors like
"Subquery returns more than 1 row", and during optimization to
perform constant optimization.

The cause for the failed ASSERT is in JOIN::join_free, where we set
  bool full= (!select_lex->uncacheable && !thd->lex->describe);
Thus for EXPLAIN statements full == FALSE, and as a result the call to
JOIN::cleanup doesn't call JOIN_TAB::cleanup which should have
called table->disable_keyread().

Solution:
Consider all kinds of subquery predicates as expensive.

4 files changed, 61 insertions, 19 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 2ad1dba5018..d38be0e8699 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -488,6 +488,30 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	SUBQUERY2_t3	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer
 drop table t1, t2, t3;
 #
+# LP BUG#680038 bool close_thread_table(THD*, TABLE**):
+#               Assertion `table->key_read == 0' failed in EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f3 int,f4 int) ;
+INSERT IGNORE INTO t1 VALUES (NULL,1,0);
+CREATE TABLE t2 (f2 int,f4 int,f5 int) ;
+INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0);
+CREATE TABLE t3 (f4 int,KEY (f4)) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+set @@optimizer_switch='semijoin=off';
+EXPLAIN
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+WHERE f4 <= ALL
+(SELECT SQ1_t1.f4
+FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+GROUP BY SQ1_t1.f4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	SUBQUERY	SQ1_t1	index	NULL	f4	5	NULL	2	Using index; Using temporary; Using filesort
+3	SUBQUERY	SQ1_t3	index	NULL	f4	5	NULL	2	Using where; Using index; Using join buffer
+drop table t1, t2, t3;
+#
 # BUG#52317: Assertion failing in Field_varstring::store() 
 # 	    at field.cc:6833
 #
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 286fd321afe..786a20d6b30 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -451,6 +451,32 @@ GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10;
 drop table t1, t2, t3;
 
 --echo #
+--echo # LP BUG#680038 bool close_thread_table(THD*, TABLE**):
+--echo #               Assertion `table->key_read == 0' failed in EXPLAIN
+--echo #
+
+CREATE TABLE t1 (f1 int,f3 int,f4 int) ;
+INSERT IGNORE INTO t1 VALUES (NULL,1,0);
+
+CREATE TABLE t2 (f2 int,f4 int,f5 int) ;
+INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0);
+
+CREATE TABLE t3 (f4 int,KEY (f4)) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+
+set @@optimizer_switch='semijoin=off';
+
+EXPLAIN
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+ WHERE f4 <= ALL
+       (SELECT SQ1_t1.f4
+        FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+        GROUP BY SQ1_t1.f4));
+
+drop table t1, t2, t3;
+
+--echo #
 --echo # BUG#52317: Assertion failing in Field_varstring::store() 
 --echo # 	    at field.cc:6833
 --echo #
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index bd6c3c62f7b..d7e47c77fd2 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -2381,23 +2381,6 @@ bool Item_in_subselect::init_cond_guards()
 }
 
 
-/*
-  Callback to test if an IN predicate is expensive.
-
-  @details
-    The return value affects the behavior of make_cond_for_table().
-
-  @retval TRUE  if the predicate is expensive
-  @retval FALSE otherwise
-*/
-
-bool Item_in_subselect::is_expensive_processor(uchar *arg)
-{
-  /* TIMOUR: TODO: decide on a cost basis whether it is expensive or not. */
-  return TRUE;
-}
-
-
 Item_subselect::trans_res
 Item_allany_subselect::select_transformer(JOIN *join)
 {
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 85e0191348e..eae9b0e558c 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -194,6 +194,7 @@ public:
   */
   bool is_evaluated() const;
   bool is_uncacheable() const;
+  bool is_expensive() { return TRUE; }
 
   /*
     Used by max/min subquery to initialize value presence registration
@@ -210,6 +211,16 @@ public:
   {
     return trace_unsupported_by_check_vcol_func_processor("subselect");
   }
+  /**
+    Callback to test if an IN predicate is expensive.
+
+    @notes
+    The return value affects the behavior of make_cond_for_table().
+
+    @retval TRUE  if the predicate is expensive
+    @retval FALSE otherwise
+  */
+  bool is_expensive_processor(uchar *arg) { return TRUE; }
   Item *safe_charset_converter(CHARSET_INFO *tocs);
 
   /**
@@ -484,8 +495,6 @@ public:
   bool init_left_expr_cache();
   /* Inform 'this' that it was computed, and contains a valid result. */
   void set_first_execution() { if (first_execution) first_execution= FALSE; }
-  bool is_expensive_processor(uchar *arg);
-  bool is_expensive() { return TRUE; }
   bool expr_cache_is_needed(THD *thd);
   
   /*