Fixed LP bug #939009.

The result of materialization of the right part of an IN subquery predicate
is placed into a temporary table. Each row of the materialized table is
distinct. A unique key over all fields of the temporary table is defined and
created. It allows to perform key look-ups into the table.
The table created for a materialized subquery can be accessed by key as
any other table. The function best_access-path search for the best access
to join a table to a given partial join. With some where conditions this
function considers a possibility of a ref_or_null access. If such access
employs the unique key on the temporary table then when estimating
the cost this access the function tries to use the array rec_per_key. Yet,
such array is not built for this unique key. This causes a crash of the server.

Rows returned by the subquery that contain nulls don't have to be placed
into temporary table, as they cannot be match any row produced by the
left part of the subquery predicate. So all fields of the temporary table
can be defined as non-nullable. In this case any ref_or_null access
to the temporary table does not make any sense and it does not make sense
to estimate such an access.

The fix makes sure that the temporary table for a materialized IN subquery
is defined with columns that are all non-nullable. The also ensures that 
any row with nulls returned by the subquery is not placed into the
temporary table.  

8 files changed, 80 insertions, 10 deletions

diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index ed910c4ef70..c99eb62eb57 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_subselect.cc b/sql/item_subselect.cc
index 52a2c2a7d8b..b40467eb21c 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -4180,6 +4180,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/opt_subselect.cc b/sql/opt_subselect.cc
index d01ef381806..a1d69ba759f 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3167,6 +3167,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 a0ed1cdc83f..9a72b647261 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3195,6 +3195,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)
@@ -3236,6 +3241,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 c5f500a6b18..20ca9bd47c5 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -3081,6 +3081,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
@@ -3104,7 +3106,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 f2720513b6f..f893abc6665 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3769,15 +3769,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;
-	  /*
-            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;
+          if (old->field->maybe_null())
+	  {
+	    old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
+	    /*
+              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
 	{
@@ -13577,6 +13580,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);
@@ -13656,6 +13660,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++;
@@ -13731,6 +13740,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 599dc993483..5c235fd1778 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -57,9 +57,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 8de71658493..5be5f4b481c 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -665,6 +665,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 st_table {
   st_table() {}                               /* Remove gcc warning */
 
@@ -807,6 +810,26 @@ struct st_table {
     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