Merge

7 files changed, 344 insertions, 122 deletions

diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index b770cb8afd5..cf615caf30b 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1765,6 +1765,7 @@ MIN(a)
 1
 DROP TABLE t1,t2,t3;
 #
+#
 # BUG#902632: Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread
 #
 CREATE TABLE t1 ( a INT );
@@ -1780,6 +1781,20 @@ AND b IN ( SELECT c FROM t3 GROUP BY c )
 );
 a
 DROP TABLE t1,t2,t3;
+#
+# BUG#901506: Crash in TABLE_LIST::print on EXPLAIN EXTENDED
+#
+CREATE TABLE t1 ( a INT, KEY(a) );
+INSERT INTO t1 VALUES (8);
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE a IN ( SELECT MIN(a) FROM t1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+Warnings:
+Note	1003	select 8 AS `a` from `test`.`t1` where <expr_cache><8>(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` having (<cache>(8) = <ref_null_helper>(min(`test`.`t1`.`a`))))))
+DROP TABLE t1;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set @subselect_mat_test_optimizer_switch_value=null;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 50e2f047005..af7c9a1de5b 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1801,6 +1801,7 @@ MIN(a)
 1
 DROP TABLE t1,t2,t3;
 #
+#
 # BUG#902632: Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread
 #
 CREATE TABLE t1 ( a INT );
@@ -1816,5 +1817,20 @@ AND b IN ( SELECT c FROM t3 GROUP BY c )
 );
 a
 DROP TABLE t1,t2,t3;
+#
+# BUG#901506: Crash in TABLE_LIST::print on EXPLAIN EXTENDED
+#
+CREATE TABLE t1 ( a INT, KEY(a) );
+INSERT INTO t1 VALUES (8);
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE a IN ( SELECT MIN(a) FROM t1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	a	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	<subquery2>	system	NULL	NULL	NULL	NULL	1	100.00	
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+Warnings:
+Note	1003	select 8 AS `a` from  <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1`) join `test`.`t1` where 1
+DROP TABLE t1;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 9614a41767e..e660c63df5b 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1466,6 +1466,7 @@ SELECT MIN(a) FROM t1, t2 WHERE b IN (SELECT c FROM t3 GROUP BY c);
 DROP TABLE t1,t2,t3;
 
 --echo #
+--echo #
 --echo # BUG#902632: Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread
 --echo #
 CREATE TABLE t1 ( a INT );
@@ -1482,6 +1483,18 @@ SELECT * FROM t1 WHERE EXISTS (
    );
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # BUG#901506: Crash in TABLE_LIST::print on EXPLAIN EXTENDED
+--echo #
+CREATE TABLE t1 ( a INT, KEY(a) );
+INSERT INTO t1 VALUES (8);
+
+EXPLAIN EXTENDED
+  SELECT * FROM t1
+   WHERE a IN ( SELECT MIN(a) FROM t1 );
+
+DROP TABLE t1;
+
 --echo # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index fbd70a04054..60ca851c881 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -448,11 +448,44 @@ public:
     Same as above, but they also allow to scan the materialized table. 
   */
   bool sjm_scan_allowed;
+
+  /* 
+    JoinTaB Materialization (JTBM) members
+  */
+  
+  /* 
+    TRUE <=> This subselect has been converted into non-mergeable semi-join
+    table.
+  */
+  bool is_jtbm_merged;
+  
+  /* (Applicable if is_jtbm_merged==TRUE) Time required to run the materialized join */
   double jtbm_read_time;
+
+  /* (Applicable if is_jtbm_merged==TRUE) Number of output rows in materialized join */
   double jtbm_record_count;   
-  bool is_jtbm_merged;
-  bool is_jtbm_const_tab;
+  
+  /*
+    (Applicable if is_jtbm_merged==TRUE) TRUE <=> The materialized subselect is
+    a degenerate subselect which produces 0 or 1 rows, which we know at
+    optimization phase.
+    Examples:
+    1. subquery has "Impossible WHERE": 
+
+      SELECT * FROM ot WHERE ot.column IN (SELECT it.col FROM it WHERE 2 > 3)
+    
+    2. Subquery produces one row which opt_sum.cc is able to get with one lookup:
 
+      SELECT * FROM ot WHERE ot.column IN (SELECT MAX(it.key_col) FROM it)
+  */
+  bool is_jtbm_const_tab;
+  
+  /* 
+    (Applicable if is_jtbm_const_tab==TRUE) Whether the subquery has produced 
+     the row (or not)
+  */
+  bool jtbm_const_row_found;
+  
   /*
     TRUE<=>this is a flattenable semi-join, false overwise.
   */
@@ -744,6 +777,9 @@ public:
 
   friend class subselect_hash_sj_engine;
   friend class Item_in_subselect;
+  friend bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list,
+                                    Item **join_where);
+
 };
 
 
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index ff0b80acb0e..3aa5a188a26 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -4623,6 +4623,234 @@ enum_nested_loop_state join_tab_execution_startup(JOIN_TAB *tab)
 }
 
 
+/*
+  Create a dummy temporary table, useful only for the sake of having a 
+  TABLE* object with map,tablenr and maybe_null properties.
+  
+  This is used by non-mergeable semi-join materilization code to handle
+  degenerate cases where materialized subquery produced "Impossible WHERE" 
+  and thus wasn't materialized.
+*/
+
+TABLE *create_dummy_tmp_table(THD *thd)
+{
+  DBUG_ENTER("create_dummy_tmp_table");
+  TABLE *table;
+  TMP_TABLE_PARAM sjm_table_param;
+  sjm_table_param.init();
+  sjm_table_param.field_count= 1;
+  List<Item> sjm_table_cols;
+  Item *column_item= new Item_int(1);
+  sjm_table_cols.push_back(column_item);
+  if (!(table= create_tmp_table(thd, &sjm_table_param, 
+                                sjm_table_cols, (ORDER*) 0, 
+                                TRUE /* distinct */, 
+                                1, /*save_sum_fields*/
+                                thd->options | TMP_TABLE_ALL_COLUMNS, 
+                                HA_POS_ERROR /*rows_limit */, 
+                                (char*)"dummy", TRUE /* Do not open */)))
+  {
+    DBUG_RETURN(NULL);
+  }
+  DBUG_RETURN(table);
+}
+
+
+/*
+  A class that is used to catch one single tuple that is sent to the join
+  output, and save it in Item_cache element(s).
+
+  It is very similar to select_singlerow_subselect but doesn't require a 
+  Item_singlerow_subselect item.
+*/
+
+class select_value_catcher :public select_subselect
+{
+public:
+  select_value_catcher(Item_subselect *item_arg)
+    :select_subselect(item_arg)
+  {}
+  int send_data(List<Item> &items);
+  int setup(List<Item> *items);
+  bool assigned;  /* TRUE <=> we've caught a value */
+  uint n_elements; /* How many elements we get */
+  Item_cache **row; /* Array of cache elements */
+};
+
+
+int select_value_catcher::setup(List<Item> *items)
+{
+  assigned= FALSE;
+  n_elements= items->elements;
+ 
+  if (!(row= (Item_cache**) sql_alloc(sizeof(Item_cache*)*n_elements)))
+    return TRUE;
+  
+  Item *sel_item;
+  List_iterator<Item> li(*items);
+  for (uint i= 0; (sel_item= li++); i++)
+  {
+    if (!(row[i]= Item_cache::get_cache(sel_item)))
+      return TRUE;
+    row[i]->setup(sel_item);
+  }
+  return FALSE;
+}
+
+
+int select_value_catcher::send_data(List<Item> &items)
+{
+  DBUG_ENTER("select_value_catcher::send_data");
+  DBUG_ASSERT(!assigned);
+  DBUG_ASSERT(items.elements == n_elements);
+
+  if (unit->offset_limit_cnt)
+  {				          // Using limit offset,count
+    unit->offset_limit_cnt--;
+    DBUG_RETURN(0);
+  }
+
+  Item *val_item;
+  List_iterator_fast<Item> li(items);
+  for (uint i= 0; (val_item= li++); i++)
+  {
+    row[i]->store(val_item);
+    row[i]->cache_value();
+  }
+  assigned= TRUE;
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Setup JTBM join tabs for execution
+*/
+
+bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, 
+                           Item **join_where)
+{
+  TABLE_LIST *table;
+  NESTED_JOIN *nested_join;
+  List_iterator<TABLE_LIST> li(*join_list);
+  DBUG_ENTER("setup_jtbm_semi_joins");
+  
+  while ((table= li++))
+  {
+    Item_in_subselect *item;
+    
+    if ((item= table->jtbm_subselect))
+    {
+      Item_in_subselect *subq_pred= item;
+      double rows;
+      double read_time;
+
+      /*
+        Perform optimization of the subquery, so that we know estmated
+        - cost of materialization process 
+        - how many records will be in the materialized temp.table
+      */
+      if (subq_pred->optimize(&rows, &read_time))
+        DBUG_RETURN(TRUE);
+
+      subq_pred->jtbm_read_time= read_time;
+      subq_pred->jtbm_record_count=rows;
+      JOIN *subq_join= subq_pred->unit->first_select()->join;
+
+      if (!subq_join->tables_list || !subq_join->table_count)
+      {
+        /*
+          A special case; subquery's join is degenerate, and it either produces
+          0 or 1 record. Examples of both cases:
+
+            select * from ot where col in (select ... from it where 2>3) 
+            select * from ot where col in (select min(it.key) from it)
+          
+          in this case, the subquery predicate has not been setup for
+          materialization. In particular, there is no materialized temp.table.
+          We'll now need to
+          1. Check whether 1 or 0 records are produced, setup this as a
+             constant join tab.
+          2. Create a dummy temporary table, because all of the join
+             optimization code relies on TABLE object being present (here we
+             follow a bad tradition started by derived tables)
+        */
+        DBUG_ASSERT(subq_pred->engine->engine_type() == 
+                    subselect_engine::SINGLE_SELECT_ENGINE);
+        subselect_single_select_engine *engine=
+          (subselect_single_select_engine*)subq_pred->engine;
+        select_value_catcher *new_sink;
+        if (!(new_sink= new select_value_catcher(subq_pred)))
+          DBUG_RETURN(TRUE);
+        if (new_sink->setup(&engine->select_lex->join->fields_list) ||
+            engine->select_lex->join->change_result(new_sink) ||
+            engine->exec())
+        {
+          DBUG_RETURN(TRUE);
+        }
+        subq_pred->is_jtbm_const_tab= TRUE;
+
+        if (new_sink->assigned)
+        {
+          subq_pred->jtbm_const_row_found= TRUE;
+          /* 
+            Subselect produced one row, which is saved in new_sink->row. 
+            Inject "left_expr[i] == row[i] equalities into parent's WHERE.
+          */
+          Item *eq_cond;
+          for (uint i= 0; i < subq_pred->left_expr->cols(); i++)
+          {
+            eq_cond= new Item_func_eq(subq_pred->left_expr->element_index(i),
+                                      new_sink->row[i]);
+            if (!eq_cond || eq_cond->fix_fields(join->thd, &eq_cond))
+              DBUG_RETURN(1);
+
+            (*join_where)= and_items(*join_where, eq_cond);
+          }
+        }
+        else
+        {
+          /* Subselect produced no rows. Just set the flag, */
+          subq_pred->jtbm_const_row_found= FALSE;
+        }
+
+        /* Set up a dummy TABLE*, optimizer code needs JOIN_TABs to have TABLE */
+        TABLE *dummy_table;
+        if (!(dummy_table= create_dummy_tmp_table(join->thd)))
+          DBUG_RETURN(1);
+        table->table= dummy_table;
+        table->table->pos_in_table_list= table;
+        setup_table_map(table->table, table, table->jtbm_table_no);
+      }
+      else
+      {
+        DBUG_ASSERT(subq_pred->test_set_strategy(SUBS_MATERIALIZATION));
+        subq_pred->is_jtbm_const_tab= FALSE;
+        subselect_hash_sj_engine *hash_sj_engine=
+          ((subselect_hash_sj_engine*)item->engine);
+        
+        table->table= hash_sj_engine->tmp_table;
+        table->table->pos_in_table_list= table;
+
+        setup_table_map(table->table, table, table->jtbm_table_no);
+
+        Item *sj_conds= hash_sj_engine->semi_join_conds;
+
+        (*join_where)= and_items(*join_where, sj_conds);
+        if (!(*join_where)->fixed)
+          (*join_where)->fix_fields(join->thd, join_where);
+      }
+    }
+
+    if ((nested_join= table->nested_join))
+    {
+      if (setup_jtbm_semi_joins(join, &nested_join->join_list, join_where))
+        DBUG_RETURN(TRUE);
+    }
+  }
+  DBUG_RETURN(FALSE);
+}
+
+
 /**
   Choose an optimal strategy to execute an IN/ALL/ANY subquery predicate
   based on cost.
@@ -4931,8 +5159,16 @@ bool JOIN::choose_tableless_subquery_plan()
          NULL.
       */
     }
-
-    if (subs_predicate->is_in_predicate())
+    
+    /*
+      For IN subqueries, use IN->EXISTS transfomation, unless the subquery 
+      has been converted to a JTBM semi-join. In that case, just leave
+      everything as-is, setup_jtbm_semi_joins() has special handling for cases
+      like this.
+    */
+    if (subs_predicate->is_in_predicate() && 
+        !(subs_predicate->substype() == Item_subselect::IN_SUBS && 
+          ((Item_in_subselect*)subs_predicate)->is_jtbm_merged))
     {
       Item_in_subselect *in_subs;
       in_subs= (Item_in_subselect*) subs_predicate;
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 94c6153f0c1..07f1fc77a20 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -10,6 +10,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join);
 bool convert_join_subqueries_to_semijoins(JOIN *join);
 int pull_out_semijoin_tables(JOIN *join);
 bool optimize_semijoin_nests(JOIN *join, table_map all_table_map);
+bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list,  
+                           Item **join_where);
 
 // used by Loose_scan_opt
 ulonglong get_bound_sj_equalities(TABLE_LIST *sj_nest, 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bf559fa40bf..313159d33d1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -798,117 +798,6 @@ err:
 }
 
 
-/*
-  Create a dummy temporary table, useful only for the sake of having a 
-  TABLE* object with map,tablenr and maybe_null properties.
-  
-  This is used by non-mergeable semi-join materilization code to handle
-  degenerate cases where materialized subquery produced "Impossible WHERE" 
-  and thus wasn't materialized.
-*/
-
-TABLE *create_dummy_tmp_table(THD *thd)
-{
-  DBUG_ENTER("create_dummy_tmp_table");
-  TABLE *table;
-  TMP_TABLE_PARAM sjm_table_param;
-  sjm_table_param.init();
-  sjm_table_param.field_count= 1;
-  List<Item> sjm_table_cols;
-  Item *column_item= new Item_int(1);
-  sjm_table_cols.push_back(column_item);
-  if (!(table= create_tmp_table(thd, &sjm_table_param, 
-                                sjm_table_cols, (ORDER*) 0, 
-                                TRUE /* distinct */, 
-                                1, /*save_sum_fields*/
-                                thd->options | TMP_TABLE_ALL_COLUMNS, 
-                                HA_POS_ERROR /*rows_limit */, 
-                                (char*)"dummy", TRUE /* Do not open */)))
-  {
-    DBUG_RETURN(NULL);
-  }
-  DBUG_RETURN(table);
-}
-
-
-void
-setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, Item **join_where)
-{
-  TABLE_LIST *table;
-  NESTED_JOIN *nested_join;
-  List_iterator<TABLE_LIST> li(*join_list);
-  DBUG_ENTER("setup_jtbm_semi_joins");
-
-  
-  while ((table= li++))
-  {
-    Item_in_subselect *item;
-    
-    if ((item= table->jtbm_subselect))
-    {
-      Item_in_subselect *subq_pred= item;
-      double rows;
-      double read_time;
-
-      subq_pred->optimize(&rows, &read_time);
-      subq_pred->jtbm_read_time= read_time;
-      subq_pred->jtbm_record_count=rows;
-      subq_pred->is_jtbm_merged= TRUE;
-      JOIN *subq_join= subq_pred->unit->first_select()->join;
-      if (!subq_join->tables_list || !subq_join->table_count)
-      {
-        /*
-          This is an empty and constant table.
-
-          TODO: what if this is not empty but still constant?
-          
-          We'll need to check the equality but there's no materializatnion
-          table?
-
-          A: create an IN-equality from 
-           - left_expr
-           - right_expr. Q: how can right-expr exist in the context of 
-              parent select? We don't have refs from outside to inside!
-              A: create/check in the context of the child select?
-              
-              for injection, check how in->exists is performed.
-        */
-        subq_pred->is_jtbm_const_tab= TRUE;
-
-        TABLE *dummy_table= create_dummy_tmp_table(join->thd);
-        table->table= dummy_table;
-        table->table->pos_in_table_list= table;
-
-        setup_table_map(table->table, table, table->jtbm_table_no);
-      }
-      else
-      {
-        DBUG_ASSERT(subq_pred->test_set_strategy(SUBS_MATERIALIZATION));
-        subq_pred->is_jtbm_const_tab= FALSE;
-        subselect_hash_sj_engine *hash_sj_engine=
-          ((subselect_hash_sj_engine*)item->engine);
-        
-        table->table= hash_sj_engine->tmp_table;
-        table->table->pos_in_table_list= table;
-
-        setup_table_map(table->table, table, table->jtbm_table_no);
-
-        Item *sj_conds= hash_sj_engine->semi_join_conds;
-
-        (*join_where)= and_items(*join_where, sj_conds);
-        if (!(*join_where)->fixed)
-          (*join_where)->fix_fields(join->thd, join_where);
-      }
-    }
-
-    if ((nested_join= table->nested_join))
-    {
-      setup_jtbm_semi_joins(join, &nested_join->join_list, join_where);
-    }
-  }
-  DBUG_VOID_RETURN;
-}
-
 /**
   global select optimisation.
 
@@ -1030,7 +919,8 @@ JOIN::optimize()
       thd->restore_active_arena(arena, &backup);
   }
   
-  setup_jtbm_semi_joins(this, join_list, &conds);
+  if (setup_jtbm_semi_joins(this, join_list, &conds))
+    DBUG_RETURN(1);
 
   conds= optimize_cond(this, conds, join_list, &cond_value, &cond_equal);
      
@@ -15658,7 +15548,12 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos)
           tab->table->pos_in_table_list->jtbm_subselect->is_jtbm_const_tab)
   {
     /* Row will not be found */
-    DBUG_RETURN(-1);
+    int res;
+    if (tab->table->pos_in_table_list->jtbm_subselect->jtbm_const_row_found)
+      res= 0;
+    else
+      res= -1;
+    DBUG_RETURN(res);
   }
   else if (tab->type == JT_SYSTEM)
   {
@@ -21455,11 +21350,20 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str,
   }
   else if (jtbm_subselect)
   {
-    str->append(STRING_WITH_LEN(" <materialize> ("));
-    subselect_hash_sj_engine *hash_engine;
-    hash_engine= (subselect_hash_sj_engine*)jtbm_subselect->engine;
-    hash_engine->materialize_engine->print(str, query_type);
-    str->append(')');
+    if (jtbm_subselect->is_jtbm_const_tab)
+    {
+      str->append(STRING_WITH_LEN(" <materialize> ("));
+      jtbm_subselect->engine->print(str, query_type);
+      str->append(')');
+    }
+    else
+    {
+      str->append(STRING_WITH_LEN(" <materialize> ("));
+      subselect_hash_sj_engine *hash_engine;
+      hash_engine= (subselect_hash_sj_engine*)jtbm_subselect->engine;
+      hash_engine->materialize_engine->print(str, query_type);
+      str->append(')');
+    }
   }
   else
   {