Merge 5.3->5.5.

10 files changed, 100 insertions, 21 deletions

diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index 89ac499b6c0..37aee93fe82 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -117,6 +117,11 @@ static void do_outer_field_to_null_str(Copy_field *copy)
 int
 set_field_to_null(Field *field)
 {
+  if (field->table->null_catch_flags & CHECK_ROW_FOR_NULLS_TO_REJECT)
+  {
+    field->table->null_catch_flags|= REJECT_ROW_DUE_TO_NULL_FIELDS;
+    return -1;
+  }
   if (field->real_maybe_null())
   {
     field->set_null();
@@ -160,6 +165,11 @@ set_field_to_null(Field *field)
 int
 set_field_to_null_with_conversions(Field *field, bool no_conversions)
 {
+  if (field->table->null_catch_flags & CHECK_ROW_FOR_NULLS_TO_REJECT)
+  {
+    field->table->null_catch_flags|= REJECT_ROW_DUE_TO_NULL_FIELDS;
+    return -1;
+  }
   if (field->real_maybe_null())
   {
     field->set_null();
diff --git a/sql/item.h b/sql/item.h
index 4af2038aa55..cf13f501c69 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1158,7 +1158,6 @@ public:
   virtual bool update_table_bitmaps_processor(uchar *arg) { return 0; }
   virtual bool view_used_tables_processor(uchar *arg) { return 0; }
   virtual bool eval_not_null_tables(uchar *opt_arg) { return 0; }
-  virtual bool clear_sum_processor(uchar *opt_arg) { return 0; }
   virtual bool is_subquery_processor (uchar *opt_arg) { return 0; }
   virtual bool limit_index_condition_pushdown_processor(uchar *opt_arg)
   { 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 50625286348..f2d7d69e173 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -4194,6 +4194,11 @@ bool subselect_hash_sj_engine::init(List<Item> *tmp_columns, uint subquery_id)
   memcpy(name, buf, len+1);
 
   result_sink->get_tmp_table_param()->materialized_subquery= true;
+  if (item->substype() == Item_subselect::IN_SUBS && 
+      ((Item_in_subselect*)item)->is_jtbm_merged)
+  {
+    result_sink->get_tmp_table_param()->force_not_null_cols= true;
+  }
   if (result_sink->create_result_table(thd, tmp_columns, TRUE,
                                        tmp_create_options,
 				       name, TRUE, TRUE))
diff --git a/sql/item_sum.h b/sql/item_sum.h
index ed07e3cb2b5..c8dce60c7d4 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -547,7 +547,6 @@ public:
   {
     return trace_unsupported_by_check_vcol_func_processor(func_name()); 
   }
-  bool clear_sum_processor(uchar *arg) { clear(); return 0; }
 };
 
 
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 72c2f0442cf..dcb5181acc3 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -268,8 +268,8 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
          Subquery !contains {GROUP BY, ORDER BY [LIMIT],
          aggregate functions}) && subquery predicate is not under "NOT IN"))
 
-    (*) The subquery must be part of a SELECT statement. The current
-         condition also excludes multi-table update statements.
+    (*) The subquery must be part of a SELECT or CREATE TABLE ... SELECT statement.
+        The current condition also excludes multi-table update statements.
   A note about prepared statements: we want the if-branch to be taken on
   PREPARE and each EXECUTE. The rewrites are only done once, but we need 
   select_lex->sj_subselects list to be populated for every EXECUTE. 
@@ -278,7 +278,8 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
   if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION) &&      // 0
         !child_select->is_part_of_union() &&                          // 1
         parent_unit->first_select()->leaf_tables.elements &&          // 2
-        thd->lex->sql_command == SQLCOM_SELECT &&                     // *
+        (thd->lex->sql_command == SQLCOM_SELECT ||                     // *
+         thd->lex->sql_command == SQLCOM_CREATE_TABLE) &&              // *
         child_select->outer_select()->leaf_tables.elements &&           // 2A
         subquery_types_allow_materialization(in_subs) &&
         (in_subs->is_top_level_item() ||                               //3
@@ -3184,6 +3185,7 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab)
     sjm->sjm_table_cols.push_back(*p_item);
 
   sjm->sjm_table_param.field_count= subq_select->item_list.elements;
+  sjm->sjm_table_param.force_not_null_cols= TRUE;
 
   if (!(sjm->table= create_tmp_table(thd, &sjm->sjm_table_param, 
                                      sjm->sjm_table_cols, (ORDER*) 0, 
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 40f8bf4b611..f970f6a4f26 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3434,6 +3434,11 @@ int select_materialize_with_stats::send_data(List<Item> &items)
 
   if ((res= select_union::send_data(items)))
     return res;
+  if (table->null_catch_flags & REJECT_ROW_DUE_TO_NULL_FIELDS)
+  {
+    table->null_catch_flags&= ~REJECT_ROW_DUE_TO_NULL_FIELDS;
+    return 0;
+  }
   /* Skip duplicate rows. */
   if (write_err == HA_ERR_FOUND_DUPP_KEY ||
       write_err == HA_ERR_FOUND_DUPP_UNIQUE)
@@ -3475,6 +3480,7 @@ void TMP_TABLE_PARAM::init()
   precomputed_group_by= 0;
   bit_fields_as_long= 0;
   materialized_subquery= 0;
+  force_not_null_cols= 0;
   skip_create_table= 0;
   DBUG_VOID_RETURN;
 }
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 52473f16b0e..5c35bc37623 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -3458,6 +3458,8 @@ public:
   bool schema_table;
   /* TRUE if the temp table is created for subquery materialization. */
   bool materialized_subquery;
+  /* TRUE if all columns of the table are guaranteed to be non-nullable */
+  bool force_not_null_cols;
   /*
     True if GROUP BY and its aggregate functions are already computed
     by a table access method (e.g. by loose index scan). In this case
@@ -3481,7 +3483,8 @@ public:
   TMP_TABLE_PARAM()
     :copy_field(0), group_parts(0),
      group_length(0), group_null_parts(0), convert_blob_length(0),
-    schema_table(0), materialized_subquery(0), precomputed_group_by(0),
+    schema_table(0), materialized_subquery(0), force_not_null_cols(0),
+    precomputed_group_by(0),
     force_copy_fields(0), bit_fields_as_long(0), skip_create_table(0)
   {}
   ~TMP_TABLE_PARAM()
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4c02f19926f..472f05dfb47 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -126,7 +126,7 @@ static int return_zero_rows(JOIN *join, select_result *res,
                             List<TABLE_LIST> &tables,
                             List<Item> &fields, bool send_row,
                             ulonglong select_options, const char *info,
-                            Item *having);
+                            Item *having, List<Item> &all_fields);
 static COND *build_equal_items(THD *thd, COND *cond,
                                COND_EQUAL *inherited,
                                List<TABLE_LIST> *join_list,
@@ -2201,6 +2201,15 @@ JOIN::exec()
       !exec_const_cond->val_int())
     zero_result_cause= "Impossible WHERE noticed after reading const tables";
 
+  /* 
+    We've called exec_const_cond->val_int(). This may have caused an error.
+  */
+  if (thd->is_error())
+  {
+    error= thd->is_error();
+    DBUG_VOID_RETURN;
+  }
+
   if (zero_result_cause)
   {
     (void) return_zero_rows(this, result, select_lex->leaf_tables,
@@ -2208,7 +2217,7 @@ JOIN::exec()
 			    send_row_on_empty_set(),
 			    select_options,
 			    zero_result_cause,
-			    having ? having : tmp_having);
+			    having ? having : tmp_having, all_fields);
     DBUG_VOID_RETURN;
   }
 
@@ -3784,15 +3793,18 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
 	{
 	  /* field = expression OR field IS NULL */
 	  old->level= and_level;
-	  old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
+          if (old->field->maybe_null())
+	  {
+	    old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
+            /* The referred expression can be NULL: */ 
+            old->null_rejecting= 0;
+	  }
 	  /*
             Remember the NOT NULL value unless the value does not depend
             on other tables.
           */
 	  if (!old->val->used_tables() && old->val->is_null())
 	    old->val= new_fields->val;
-          /* The referred expression can be NULL: */ 
-          old->null_rejecting= 0;
 	}
 	else
 	{
@@ -10566,7 +10578,7 @@ ORDER *simple_remove_const(ORDER *order, COND *where)
 static int
 return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables,
 		 List<Item> &fields, bool send_row, ulonglong select_options,
-		 const char *info, Item *having)
+		 const char *info, Item *having, List<Item> &all_fields)
 {
   DBUG_ENTER("return_zero_rows");
 
@@ -10596,9 +10608,15 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables,
       if (!table->is_jtbm())
         mark_as_null_row(table->table);		// All fields are NULL
     }
-    if (having &&
-        !having->walk(&Item::clear_sum_processor, FALSE, NULL) &&
-        having->val_int() == 0)
+    List_iterator_fast<Item> it(all_fields);
+    Item *item;
+    /*
+      Inform all items (especially aggregating) to calculate HAVING correctly,
+      also we will need it for sending results.
+    */
+    while ((item= it++))
+      item->no_rows_in_result();
+    if (having && having->val_int() == 0)
       send_row=0;
   }
   if (!(result->send_result_set_metadata(fields,
@@ -10606,13 +10624,7 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables,
   {
     bool send_error= FALSE;
     if (send_row)
-    {
-      List_iterator_fast<Item> it(fields);
-      Item *item;
-      while ((item= it++))
-	item->no_rows_in_result();
       send_error= result->send_data(fields) > 0;
-    }
     if (!send_error)
       result->send_eof();				// Should be safe
   }
@@ -12400,6 +12412,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top,
           tbl->table->maybe_null= FALSE;
         tbl->join_list= table->join_list;
         repl_list.push_back(tbl);
+        tbl->dep_tables|= table->dep_tables;
       }
       li.replace(repl_list);
       /* Need to update the name resolution table chain when flattening joins */
@@ -13819,6 +13832,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
   table->merge_keys.init();
   table->intersect_keys.init();
   table->keys_in_use_for_query.init();
+  table->no_rows_with_nulls= param->force_not_null_cols;
 
   table->s= share;
   init_tmp_table_share(thd, share, "", 0, tmpname, tmpname);
@@ -13900,6 +13914,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
           thd->mem_root= mem_root_save;
           arg= sum_item->set_arg(i, thd, new Item_field(new_field));
           thd->mem_root= &table->mem_root;
+          if (param->force_not_null_cols)
+	  {
+            new_field->flags|= NOT_NULL_FLAG;
+            new_field->null_ptr= NULL;
+          }
 	  if (!(new_field->flags & NOT_NULL_FLAG))
           {
 	    null_count++;
@@ -13975,6 +13994,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
           agg_item->result_field= new_field;
       }
       tmp_from_field++;
+      if (param->force_not_null_cols)
+      {
+        new_field->flags|= NOT_NULL_FLAG;
+        new_field->null_ptr= NULL;
+      }
       reclength+=new_field->pack_length();
       if (!(new_field->flags & NOT_NULL_FLAG))
 	null_count++;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 9b6d3a8dda5..2118bc8c30d 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -59,9 +59,17 @@ int select_union::send_data(List<Item> &values)
     unit->offset_limit_cnt--;
     return 0;
   }
+  if (table->no_rows_with_nulls)
+    table->null_catch_flags= CHECK_ROW_FOR_NULLS_TO_REJECT;
   fill_record(thd, table->field, values, TRUE, FALSE);
   if (thd->is_error())
     return 1;
+  if (table->no_rows_with_nulls)
+  {
+    table->null_catch_flags&= ~CHECK_ROW_FOR_NULLS_TO_REJECT;
+    if (table->null_catch_flags)
+      return 0;
+  }
 
   if ((write_err= table->file->ha_write_tmp_row(table->record[0])))
   {
diff --git a/sql/table.h b/sql/table.h
index 77a7f8cf6a9..a227b42f71d 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -906,6 +906,9 @@ enum index_hint_type
   INDEX_HINT_FORCE
 };
 
+#define      CHECK_ROW_FOR_NULLS_TO_REJECT   (1 << 0)
+#define      REJECT_ROW_DUE_TO_NULL_FIELDS   (1 << 1)
+
 struct TABLE
 {
   TABLE() {}                               /* Remove gcc warning */
@@ -1054,6 +1057,26 @@ public:
     NULL, including columns declared as "not null" (see maybe_null).
   */
   bool null_row;
+  /*
+    No rows that contain null values can be placed into this table.
+    Currently this flag can be set to true only for a temporary table
+    that used to store the result of materialization of a subquery.
+  */
+  bool no_rows_with_nulls;
+  /*
+    This field can contain two bit flags: 
+      CHECK_ROW_FOR_NULLS_TO_REJECT
+      REJECT_ROW_DUE_TO_NULL_FIELDS
+    The first flag is set for the dynamic contexts where it is prohibited
+    to write any null into the table.
+    The second flag is set only if the first flag is set on.
+    The informs the outer scope that there was an attept to write null
+    into a field of the table in the context where it is prohibited.
+    This flag should be set off as soon as the first flag is set on.
+    Currently these flags are used only the tables tno_rows_with_nulls set
+    to true. 
+  */       
+  uint8 null_catch_flags;
 
   /*
     TODO: Each of the following flags take up 8 bits. They can just as easily