MDEV-21201 No records produced in information_schema query, depending on projection

Reimplement MDEV-14275 Improving memory utilization for information schema

Postpone temp table instantiation until after setup_fields().

Replace all unused (not marked in read_set) columns in an I_S table
with CHAR(0). This can drastically reduce the footprint of a MEMORY
table (a TABLE_CATALOG alone is 1538 bytes per row).

This does not change the engine. If the table was decided to be Aria
(because of, say, blobs) then after optimization it'll stay Aria
even if all blobs were removed.

Note 1: when transforming table structure, share->blob_fields is
preserved, otherwise Aria might switch from DYNAMIC to STATIC row format
and expect a special field for a deleted mark, which create_tmp_tabe
didn't provide.

Note 2: optimizer was doing handler::info() (to know the number of rows)
before the temp table is populated. That didn't make much sense. Now
it's done before the table is even instantiated. Preserve the old
behavior and report 0 rows.

This reverts e2664ee8362 and a8458a2345e

8 files changed, 99 insertions, 128 deletions

diff --git a/sql/set_var.cc b/sql/set_var.cc
index eb2b9234db3..58b6b392449 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -1068,7 +1068,6 @@ static void store_var(Field *field, sys_var *var, enum_var_type scope,
 int fill_sysvars(THD *thd, TABLE_LIST *tables, COND *cond)
 {
   char name_buffer[NAME_CHAR_LEN];
-  enum_check_fields save_count_cuted_fields= thd->count_cuted_fields;
   bool res= 1;
   CHARSET_INFO *scs= system_charset_info;
   StringBuffer<STRING_BUFFER_USUAL_SIZE> strbuf(scs);
@@ -1078,7 +1077,6 @@ int fill_sysvars(THD *thd, TABLE_LIST *tables, COND *cond)
   DBUG_ASSERT(tables->table->in_use == thd);
 
   cond= make_cond_for_info_schema(thd, cond, tables);
-  thd->count_cuted_fields= CHECK_FIELD_WARN;
   mysql_prlock_rdlock(&LOCK_system_variables_hash);
 
   for (uint i= 0; i < system_variable_hash.records; i++)
@@ -1243,7 +1241,6 @@ int fill_sysvars(THD *thd, TABLE_LIST *tables, COND *cond)
   res= 0;
 end:
   mysql_prlock_unlock(&LOCK_system_variables_hash);
-  thd->count_cuted_fields= save_count_cuted_fields;
   return res;
 }
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 4f64dbfbbf9..b5483c671de 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3312,21 +3312,21 @@ bool LEX::can_not_use_merged()
   }
 }
 
-/*
-  Detect that we need only table structure of derived table/view
+/**
+  Detect that we need only table structure of derived table/view.
 
-  SYNOPSIS
-    only_view_structure()
+  Also used by I_S tables (@see create_schema_table) to detect that
+  they need a full table structure and cannot optimize unused columns away
 
-  RETURN
-    TRUE yes, we need only structure
-    FALSE no, we need data
+  @retval TRUE yes, we need only structure
+  @retval FALSE no, we need data
 */
 
 bool LEX::only_view_structure()
 {
   switch (sql_command) {
   case SQLCOM_SHOW_CREATE:
+  case SQLCOM_CHECKSUM:
   case SQLCOM_SHOW_TABLES:
   case SQLCOM_SHOW_FIELDS:
   case SQLCOM_REVOKE_ALL:
@@ -3334,6 +3334,8 @@ bool LEX::only_view_structure()
   case SQLCOM_GRANT:
   case SQLCOM_CREATE_VIEW:
     return TRUE;
+  case SQLCOM_CREATE_TABLE:
+    return create_info.like();
   default:
     return FALSE;
   }
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 88e6d01304f..1b382ce0136 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -8905,8 +8905,6 @@ void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List<String> *using_fields,
                       SELECT_LEX *lex)
 {
   b->natural_join= a;
-  a->part_of_natural_join= TRUE;
-  b->part_of_natural_join= TRUE;
   lex->prev_join_using= using_fields;
 }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 461c7b18363..37755387dda 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2468,6 +2468,10 @@ int JOIN::optimize_stage2()
     (select_options & (SELECT_DESCRIBE | SELECT_NO_JOIN_CACHE)) |
     (select_lex->ftfunc_list->elements ?  SELECT_NO_JOIN_CACHE : 0);
 
+  if (select_lex->options & OPTION_SCHEMA_TABLE &&
+       optimize_schema_tables_reads(this))
+    DBUG_RETURN(1);
+
   if (make_join_readinfo(this, select_opts_for_readinfo, no_jbuf_after))
     DBUG_RETURN(1);
 
@@ -2644,10 +2648,6 @@ int JOIN::optimize_stage2()
     having_is_correlated= MY_TEST(having->used_tables() & OUTER_REF_TABLE_BIT);
   tmp_having= having;
 
-  if ((select_lex->options & OPTION_SCHEMA_TABLE) &&
-       optimize_schema_tables_reads(this))
-    DBUG_RETURN(TRUE);
-
   if (unlikely(thd->is_error()))
     DBUG_RETURN(TRUE);
 
@@ -17138,15 +17138,11 @@ Field *Item::create_field_for_schema(THD *thd, TABLE *table)
   {
     Field *field;
     if (max_length > MAX_FIELD_VARCHARLENGTH)
-      field= new (thd->mem_root) Field_blob(max_length, maybe_null, &name,
-                                            collation.collation);
-    else if (max_length > 0)
-      field= new (thd->mem_root) Field_varstring(max_length, maybe_null, &name,
-                                                 table->s,
-                                                 collation.collation);
-    else
-      field= new Field_null((uchar*) 0, 0, Field::NONE, &name,
+      field= new Field_blob(max_length, maybe_null, &name,
                             collation.collation);
+    else
+      field= new Field_varstring(max_length, maybe_null, &name,
+                                 table->s, collation.collation);
     if (field)
       field->init(table);
     return field;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 2a569204908..6bd8c9b5c27 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3700,8 +3700,6 @@ static bool show_status_array(THD *thd, const char *wild,
   CHARSET_INFO *charset= system_charset_info;
   DBUG_ENTER("show_status_array");
 
-  thd->count_cuted_fields= CHECK_FIELD_WARN;
-
   prefix_end=strnmov(name_buffer, prefix, sizeof(name_buffer)-1);
   if (*prefix)
     *prefix_end++= '_';
@@ -3801,6 +3799,8 @@ static bool show_status_array(THD *thd, const char *wild,
         pos= get_one_variable(thd, var, scope, show_type, status_var,
                               &charset, buff, &length);
 
+        if (table->field[1]->field_length)
+          thd->count_cuted_fields= CHECK_FIELD_WARN;
         table->field[1]->store(pos, (uint32) length, charset);
         thd->count_cuted_fields= CHECK_FIELD_IGNORE;
         table->field[1]->set_notnull();
@@ -8108,51 +8108,6 @@ ST_SCHEMA_TABLE *get_schema_table(enum enum_schema_tables schema_table_idx)
   return &schema_tables[schema_table_idx];
 }
 
-static void
-mark_all_fields_used_in_query(THD *thd,
-                              ST_FIELD_INFO *schema_fields,
-                              MY_BITMAP *bitmap,
-                              Item *all_items)
-{
-  Item *item;
-  DBUG_ENTER("mark_all_fields_used_in_query");
-
-  /* If not SELECT command, return all columns */
-  if (thd->lex->sql_command != SQLCOM_SELECT &&
-      thd->lex->sql_command != SQLCOM_SET_OPTION)
-  {
-    bitmap_set_all(bitmap);
-    DBUG_VOID_RETURN;
-  }
-
-  for (item= all_items ; item ; item= item->next)
-  {
-    if (item->type() == Item::FIELD_ITEM)
-    {
-      ST_FIELD_INFO *fields= schema_fields;
-      uint count;
-      Item_field *item_field= (Item_field*) item;
-
-      /* item_field can be '*' as this function is called before fix_fields */
-      if (item_field->field_name.str == star_clex_str.str)
-      {
-        bitmap_set_all(bitmap);
-        break;
-      }
-      for (count=0; fields->field_name; fields++, count++)
-      {
-        if (!my_strcasecmp(system_charset_info, fields->field_name,
-                           item_field->field_name.str))
-        {
-          bitmap_set_bit(bitmap, count);
-          break;
-        }
-      }
-    }
-  }
-  DBUG_VOID_RETURN;
-}
-
 /**
   Create information_schema table using schema_table data.
 
@@ -8176,37 +8131,19 @@ mark_all_fields_used_in_query(THD *thd,
 
 TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list)
 {
-  uint field_count;
-  Item *item, *all_items;
+  uint field_count= 0;
+  Item *item;
   TABLE *table;
   List<Item> field_list;
   ST_SCHEMA_TABLE *schema_table= table_list->schema_table;
   ST_FIELD_INFO *fields_info= schema_table->fields_info;
-  ST_FIELD_INFO *fields;
   CHARSET_INFO *cs= system_charset_info;
   MEM_ROOT *mem_root= thd->mem_root;
-  MY_BITMAP bitmap;
-  my_bitmap_map *buf;
+  bool need_all_fieds= table_list->schema_table_reformed || // SHOW command
+                       thd->lex->only_view_structure(); // need table structure
   DBUG_ENTER("create_schema_table");
 
-  for (field_count= 0, fields= fields_info; fields->field_name; fields++)
-    field_count++;
-  if (!(buf= (my_bitmap_map*) thd->alloc(bitmap_buffer_size(field_count))))
-    DBUG_RETURN(NULL);
-  my_bitmap_init(&bitmap, buf, field_count, 0);
-
-  if (!thd->stmt_arena->is_conventional() &&
-      thd->mem_root != thd->stmt_arena->mem_root)
-    all_items= thd->stmt_arena->free_list;
-  else
-    all_items= thd->free_list;
-
-  if (table_list->part_of_natural_join)
-    bitmap_set_all(&bitmap);
-  else
-    mark_all_fields_used_in_query(thd, fields_info, &bitmap, all_items);
-
-  for (field_count=0; fields_info->field_name; fields_info++)
+  for (; fields_info->field_name; fields_info++)
   {
     size_t field_name_length= strlen(fields_info->field_name);
     switch (fields_info->field_type) {
@@ -8283,44 +8220,19 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list)
     case MYSQL_TYPE_MEDIUM_BLOB:
     case MYSQL_TYPE_LONG_BLOB:
     case MYSQL_TYPE_BLOB:
-      if (bitmap_is_set(&bitmap, field_count))
-      {
-        if (!(item= new (mem_root)
-              Item_blob(thd, fields_info->field_name,
-                        fields_info->field_length)))
-        {
-          DBUG_RETURN(0);
-        }
-      }
-      else
-      {
-        if (!(item= new (mem_root)
-              Item_empty_string(thd, "", 0, cs)))
-        {
-          DBUG_RETURN(0);
-        }
-        item->set_name(thd, fields_info->field_name,
-                       field_name_length, cs);
-      }
+      if (!(item= new (mem_root) Item_blob(thd, fields_info->field_name,
+                                           fields_info->field_length)))
+        DBUG_RETURN(0);
       break;
     default:
-    {
-      bool show_field;
       /* Don't let unimplemented types pass through. Could be a grave error. */
       DBUG_ASSERT(fields_info->field_type == MYSQL_TYPE_STRING);
-
-      show_field= bitmap_is_set(&bitmap, field_count);
-      if (!(item= new (mem_root)
-            Item_empty_string(thd, "",
-                              show_field ? fields_info->field_length : 0, cs)))
-      {
+      if (!(item= new (mem_root) Item_empty_string(thd, "",
+                                               fields_info->field_length, cs)))
         DBUG_RETURN(0);
-      }
-      item->set_name(thd, fields_info->field_name,
-                     field_name_length, cs);
+      item->set_name(thd, fields_info->field_name, field_name_length, cs);
       break;
     }
-    }
     field_list.push_back(item, thd->mem_root);
     item->maybe_null= (fields_info->field_flags & MY_I_S_MAYBE_NULL);
     field_count++;
@@ -8335,7 +8247,7 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list)
   if (!(table= create_tmp_table(thd, tmp_table_param, field_list, (ORDER*) 0, 0,
                  0, (select_lex->options | thd->variables.option_bits |
                  TMP_TABLE_ALL_COLUMNS), HA_POS_ERROR,
-                 &table_list->alias, false, keep_row_order)))
+                 &table_list->alias, !need_all_fieds, keep_row_order)))
     DBUG_RETURN(0);
   my_bitmap_map* bitmaps=
     (my_bitmap_map*) thd->alloc(bitmap_buffer_size(field_count));
@@ -8751,6 +8663,67 @@ end:
 }
 
 
+static int optimize_schema_tables_memory_usage(TABLE_LIST *table_list)
+{
+  TABLE *table= table_list->table;
+  THD *thd=table->in_use;
+  if (!table->is_created())
+  {
+    TMP_TABLE_PARAM *p= table_list->schema_table_param;
+    TMP_ENGINE_COLUMNDEF *from_recinfo, *to_recinfo;
+    DBUG_ASSERT(table->s->keys == 0);
+    DBUG_ASSERT(table->s->uniques == 0);
+
+    // XXX HACK HACK HACK: in a stored function, RETURN (SELECT ...)
+    // enables warnings (in THD::sp_eval_expr) for the whole val_xxx/store pair,
+    // while the intention is to warn only for store(). Until this is
+    // fixed let's avoid data truncation warnings in I_S->fill_table()
+    if (thd->count_cuted_fields == CHECK_FIELD_IGNORE)
+    {
+
+    uchar *cur= table->field[0]->ptr;
+    /* first recinfo could be a NULL bitmap, not an actual Field */
+    from_recinfo= to_recinfo= p->start_recinfo + (cur != table->record[0]);
+    for (uint i=0; i < table->s->fields; i++, from_recinfo++)
+    {
+      Field *field= table->field[i];
+      DBUG_ASSERT(field->vcol_info == 0);
+      DBUG_ASSERT(from_recinfo->length= field->pack_length_in_rec());
+      if (bitmap_is_set(table->read_set, i))
+      {
+        field->move_field(cur);
+        *to_recinfo++= *from_recinfo;
+        cur+= from_recinfo->length;
+      }
+      else
+      {
+        field= new (thd->mem_root) Field_string(cur, 0, field->null_ptr,
+                              field->null_bit, Field::NONE,
+                              &field->field_name, field->dtcollation());
+        field->init(table);
+        field->field_index= i;
+        DBUG_ASSERT(field->pack_length_in_rec() == 0);
+        table->field[i]= field;
+      }
+    }
+    if ((table->s->reclength= (ulong)(cur - table->record[0])) == 0)
+    {
+      /* all fields were optimized away. Force a non-0-length row */
+      table->s->reclength= to_recinfo->length= 1;
+      to_recinfo++;
+    }
+    p->recinfo= to_recinfo;
+    } // XXX end of HACK HACK HACK
+
+    // TODO switch from Aria to Memory if all blobs were optimized away?
+    if (instantiate_tmp_table(table, p->keyinfo, p->start_recinfo, &p->recinfo,
+                 table_list->select_lex->options | thd->variables.option_bits))
+      return 1;
+  }
+  return 0;
+}
+
+
 /*
   This is the optimizer part of get_schema_tables_result().
 */
@@ -8771,6 +8744,9 @@ bool optimize_schema_tables_reads(JOIN *join)
     TABLE_LIST *table_list= tab->table->pos_in_table_list;
     if (table_list->schema_table && thd->fill_information_schema_tables())
     {
+      if (optimize_schema_tables_memory_usage(table_list))
+        DBUG_RETURN(1);
+
       /* A value of 0 indicates a dummy implementation */
       if (table_list->schema_table->fill_table == 0)
         continue;
diff --git a/sql/table.h b/sql/table.h
index a42feba68c4..c7e7a39b46f 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2190,7 +2190,6 @@ struct TABLE_LIST
     parsing 'this' is a NATURAL/USING join iff (natural_join != NULL).
   */
   TABLE_LIST *natural_join;
-  bool part_of_natural_join;
   /*
     True if 'this' represents a nested join that is a NATURAL JOIN.
     For one of the operands of 'this', the member 'natural_join' points
diff --git a/storage/heap/ha_heap.cc b/storage/heap/ha_heap.cc
index 396887df1b1..a40f4b8a464 100644
--- a/storage/heap/ha_heap.cc
+++ b/storage/heap/ha_heap.cc
@@ -364,7 +364,7 @@ int ha_heap::info(uint flag)
   HEAPINFO hp_info;
 
   if (!table)
-    return 1;
+    return 0;
 
   (void) heap_info(file,&hp_info,flag);
 
diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc
index c0163473f3a..23d2becbb04 100644
--- a/storage/maria/ha_maria.cc
+++ b/storage/maria/ha_maria.cc
@@ -2440,6 +2440,9 @@ int ha_maria::info(uint flag)
   MARIA_INFO maria_info;
   char name_buff[FN_REFLEN];
 
+  if (!table)
+    return 0;
+
   (void) maria_status(file, &maria_info, flag);
   if (flag & HA_STATUS_VARIABLE)
   {