Fixing a few data type related problems: MDEV-12875, MDEV-12886, MDEV-12916

This is a joint patch fixing the following problems:

MDEV-12875 Wrong VIEW column data type for COALESCE(int_column)
MDEV-12886 Different default for INT and BIGINT column in a VIEW for a SELECT with ROLLUP
MDEV-12916 Wrong column data type for an INT field of a cursor-anchored ROW variable

All above problem happened because the global function ::create_tmp_field()
called the top-level Item::create_tmp_field(), which made some tranformation
for INT-result data types. For example, INT(11) became BIGINT(11), because 11
is a corner case and it's not known if it fits or does not fit into INT range,
so Item::create_tmp_field() converted it to BIGINT(11) for safety.

The main idea of this patch is to avoid such tranformations.

1. Fixing Item::create_tmp_field() not to have a special case for INT_RESULT.

   Item::create_tmp_field() is changed not to have a special case
   for INT_RESULT (which earlier made a decision based on Item's max_length).
   It now calls tmp_table_field_from_field_type() for INT_RESULT,
   therefore preserves the original data type (e.g. INT, YEAR) without
   conversion to BIGINT.

   This change is valid, because a number of recent fixes
   (e.g. in Item_func_int, Item_hybrid_func, Item_int, Item_splocal)
   guarantee that item->type_handler() now properly returns
   type_handler_long vs type_handler_longlong. So no adjustment by length
   is needed any more for Items returning INT_RESULT.

   After this change, Item::create_tmp_field() calls
   tmp_table_field_from_field_type() for all XXX_RESULT, except REAL_RESULT.

2. Fixing Item::create_tmp_field() not to have a special case for REAL_RESULT.

   Note, the reason for a special case for REAL_RESULT is to have a special
   constructor for Field_double(), forcing Field_real::not_fixed to be set
   to true.

   Taking into account that only Item_sum descendants actually need a special
   constructor call Field_double(not_fixed=true), not too loose precision
   when mixing individual rows to the aggregate result:
   - renaming Item::create_tmp_field() to Item_sum::create_tmp_field().
   - changing Item::create_tmp_field() just to call
     tmp_table_field_from_field_type() for all XXX_RESULT types.

   A special case for REAL_RESULT in Item::create_tmp_field() is now gone.
   Item::create_tmp_field() is now symmetric for all XXX_RESULT types,
   and now just calls tmp_table_field_from_field_type().

3. Fixing Item_func::create_field_for_create_select() not to have
   a special case for STRING_RESULT.

   After changes #1 and #2, the code in
   Item_func::create_field_for_create_select(), testing result_type(),
   becomes useless, because: now Item::create_tmp_field() and
   tmp_table_field_from_field_type() do exactly the same thing for all
   XXX_RESULT types for Item_func descendants:
   a. It calls tmp_table_field_from_field_type for STRING_RESULT directly.
   b. For other XXX_RESULT, it goes through Item::create_tmp_field(),
      which calls the global function ::create_tmp_field(),
      which calls item->create_tmp_field() for FUNC_ITEM,
      which calls tmp_table_field_from_field_type() again.

   So removing the virtual implementation of
   Item_func::create_field_for_create_select().
   The inherited Item::create_field_for_create_select() now perfectly
   does the job, as it also calls tmp_table_field_from_field_type()
   for FUNC_ITEM, independently from XXX_RESULT type.

4. Taking into account #1 and #2, as well as some recent changes,
   removing virtual implementations:
   - Item_hybrid_func::create_tmp_field()
   - Item_hybrid_func::create_field_for_create_select()
   - Item_int_func::create_tmp_field()
   - Item_int_func::create_field_for_create_select()
   - Item_temporal_func::create_field_for_create_select()
   The derived versions from Item now perfectly work.

5. Moving a piece of code from create_tmp_field_from_item()
   to a new function create_tmp_field_from_item_finalize(),
   to reuse it in two places (see #6).

6. Changing the code responsible for BIT->INT/BIGIN tranformation
   (which is called for the cases when the created table, e.g. HEAP,
    does not fully support BIT) not to call create_tmp_field_from_item(),
   because the latter now calls tmp_table_field_from_field_type() instead
   of create_tmp_field() and thefore cannot do BIT transformation.
   So rewriting this code using a sequence of these calls:
   - item->type_handler_long_or_longlong()
   - handler->make_and_init_table_field()
   - create_tmp_field_from_item_finalize()

7. Miscelaneous changes:
   - Moving type_handler_long_or_longlong() from "protected" to "public",
     as it's now needed in the global function create_tmp_field().

8. The above changes fixed MDEV-12875, MDEV-12886, MDEV-12916.
   So adding tests for these bugs.

1 files changed, 1 insertions, 4 deletions

diff --git a/sql/item_sum.h b/sql/item_sum.h
index 14a2fd0c0ec..65306ab6f48 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -508,10 +508,7 @@ public:
   }
   virtual void make_unique() { force_copy_fields= TRUE; }
   Item *get_tmp_table_item(THD *thd);
-  Field *create_tmp_field(bool group, TABLE *table)
-  {
-    return Item::create_tmp_field(group, table);
-  }
+  Field *create_tmp_field(bool group, TABLE *table);
   virtual bool collect_outer_ref_processor(void *param);
   bool init_sum_func_check(THD *thd);
   bool check_sum_func(THD *thd, Item **ref);