MDEV-28918 Implicit cast from INET6 UNSIGNED works differently on UPDATE vs ALTER

Now INSERT, UPDATE, ALTER statements involving incompatible data type pairs, e.g.:

    UPDATE TABLE t1 SET col_inet6=col_int;
    INSERT INTO t1 (col_inet6) SELECT col_in FROM t2;
    ALTER TABLE t1 MODIFY col_inet6 INT;

consistently return an error at the statement preparation time:

    ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET'

and abort the statement before starting interating rows.

This error is the same with what is raised for queries like:
    SELECT col_inet6 FROM t1 UNION SELECT col_int FROM t2;
    SELECT COALESCE(col_inet6, col_int) FROM t1;

Before this change the error was caught only during the execution time,
when a Field_xxx::store_xxx() was called for the very firts row.
The behavior was not consistent between various statements and could do different things:
- abort the statement
- set a column to the data type default value (e.g. '::' for INET6)
- set a column to NULL

A typical old error was:

    ERROR 22007: Incorrect inet6 value: '1' for column `test`.`t1`.`a` at row 1

EXCEPTION:

Note, there is an exception: a multi-row INSERT..VALUES, e.g.:
    INSERT INTO t1 (col_a,col_b) VALUES (a1,b1),(a2,b2);
checks assignment compability at the preparation time for the very first row only:
    (col_a,col_b) vs (a1,b1)

Other rows are still checked at the execution time and return the old warnings
or errors in case of a failure. This is done because catching all rows at the
preparation time would change behavior significantly. So it still works
according to the STRICT_XXX_TABLES sql_mode flags and the table transaction ability.

This is too late to change this behavior in 10.7.
There is no a firm decision yet if a multi-row INSERT..VALUES
behavior will change in later versions.

9 files changed, 189 insertions, 3 deletions

diff --git a/sql/field.cc b/sql/field.cc
index 348947329f7..5abf05fed2e 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -964,6 +964,32 @@ Type_handler::aggregate_for_result_traditional(const Type_handler *a,
 }
 
 
+bool Field::check_assignability_from(const Type_handler *from) const
+{
+  /*
+    Using type_handler_for_item_field() here to get the data type handler
+    on both sides. This is needed to make sure aggregation for Field
+    works the same way with how Item_field aggregates for UNION or CASE,
+    so these statements:
+      SELECT a FROM t1 UNION SELECT b FROM t1; // Item_field vs Item_field
+      UPDATE t1 SET a=b;                       // Field      vs Item_field
+    either both return "Illegal parameter data types" or both pass
+    the data type compatibility test.
+    For MariaDB standard data types, using type_handler_for_item_field()
+    turns ENUM/SET into just CHAR.
+  */
+  Type_handler_hybrid_field_type th(type_handler()->
+                                      type_handler_for_item_field());
+  if (th.aggregate_for_result(from->type_handler_for_item_field()))
+  {
+    my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0),
+             type_handler()->name().ptr(), from->name().ptr(), "SET");
+    return true;
+  }
+  return false;
+}
+
+
 /*
   Test if the given string contains important data:
   not spaces for character string,
diff --git a/sql/field.h b/sql/field.h
index 81e10307c89..2198066d962 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -905,6 +905,12 @@ public:
 
   bool is_unsigned() const { return flags & UNSIGNED_FLAG; }
 
+  bool check_assignability_from(const Type_handler *from) const;
+  bool check_assignability_from(const Field *from) const
+  {
+    return check_assignability_from(from->type_handler());
+  }
+
   /**
     Convenience definition of a copy function returned by
     Field::get_copy_func()
diff --git a/sql/item.cc b/sql/item.cc
index 1f6d585efd7..ecc9f8e6bfe 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4509,6 +4509,22 @@ bool Item_param::is_evaluable_expression() const
 }
 
 
+bool Item_param::check_assignability_to(const Field *to) const
+{
+  switch (state) {
+  case SHORT_DATA_VALUE:
+  case LONG_DATA_VALUE:
+  case NULL_VALUE:
+    return to->check_assignability_from(type_handler());
+  case NO_VALUE:
+  case IGNORE_VALUE:
+  case DEFAULT_VALUE:
+    break;
+  }
+  return false;
+}
+
+
 bool Item_param::can_return_value() const
 {
   // There's no "default". See comments in Item_param::save_in_field().
diff --git a/sql/item.h b/sql/item.h
index c3251259b43..117a252c025 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1838,6 +1838,16 @@ public:
   */
   virtual bool is_evaluable_expression() const { return true; }
 
+  virtual bool check_assignability_to(const Field *to) const
+  {
+    /*
+      "this" must be neither DEFAULT/IGNORE,
+      nor Item_param bound to DEFAULT/IGNORE.
+    */
+    DBUG_ASSERT(is_evaluable_expression());
+    return to->check_assignability_from(type_handler());
+  }
+
   /**
    * Check whether the item is a parameter  ('?') of stored routine.
    * Default implementation returns false. Method is overridden in the class
@@ -4091,6 +4101,7 @@ class Item_param :public Item_basic_value,
   const String *value_query_val_str(THD *thd, String* str) const;
   Item *value_clone_item(THD *thd);
   bool is_evaluable_expression() const override;
+  bool check_assignability_to(const Field *field) const override;
   bool can_return_value() const;
 
 public:
@@ -6771,6 +6782,10 @@ public:
   {
     str->append(STRING_WITH_LEN("default"));
   }
+  bool check_assignability_to(const Field *to) const override
+  {
+    return false;
+  }
   int save_in_field(Field *field_arg, bool) override
   {
     return field_arg->save_in_field_default_value(false);
@@ -6804,6 +6819,10 @@ public:
   {
     str->append(STRING_WITH_LEN("ignore"));
   }
+  bool check_assignability_to(const Field *to) const override
+  {
+    return false;
+  }
   int save_in_field(Field *field_arg, bool) override
   {
     return field_arg->save_in_field_ignore_value(false);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index d22a5670495..73bc914746d 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -829,6 +829,19 @@ bool mysql_insert(THD *thd, TABLE_LIST *table_list,
   context->resolve_in_table_list_only(table_list);
   switch_to_nullable_trigger_fields(*values, table);
 
+  /*
+    Check assignability for the leftmost () in VALUES:
+      INSERT INTO t1 (a,b) VALUES (1,2), (3,4);
+    This checks if the values (1,2) can be assigned to fields (a,b).
+    The further values, e.g. (3,4) are not checked - they will be
+    checked during the execution time (when processing actual rows).
+    This is to preserve the "insert until the very first error"-style
+    behaviour for non-transactional tables.
+  */
+  if (values->elements &&
+      table_list->table->check_assignability_opt_fields(fields, *values))
+    goto abort;
+
   while ((values= its++))
   {
     thd->get_stmt_da()->inc_current_row_for_warning();
@@ -1688,7 +1701,15 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
     {
       select_lex->no_wrap_view_item= TRUE;
       res= check_update_fields(thd, context->table_list, update_fields,
-                               update_values, false, &map);
+                               update_values, false, &map) ||
+           /*
+             Check that all col=expr pairs are compatible for assignment in
+             INSERT INTO t1 VALUES (...)
+               ON DUPLICATE KEY UPDATE col=expr [, col=expr];
+           */
+           TABLE::check_assignability_explicit_fields(update_fields,
+                                                      update_values);
+
       select_lex->no_wrap_view_item= FALSE;
     }
 
@@ -3883,6 +3904,16 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
         check_insert_fields(thd, table_list, *fields, values,
                             !insert_into_view, 1, &map));
 
+  if (!res)
+  {
+     /*
+        Check that all colN=exprN pairs are compatible for assignment, e.g.:
+          INSERT INTO t1 (col1, col2) VALUES (expr1, expr2);
+          INSERT INTO t1 SET col1=expr1, col2=expr2;
+     */
+     res= table_list->table->check_assignability_opt_fields(*fields, values);
+  }
+
   if (!res && fields->elements)
   {
     Abort_on_warning_instant_set aws(thd,
@@ -3936,7 +3967,14 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
     }
 
     res= res || setup_fields(thd, Ref_ptr_array(), *info.update_values,
-                             MARK_COLUMNS_READ, 0, NULL, 0);
+                             MARK_COLUMNS_READ, 0, NULL, 0) ||
+                /*
+                  Check that all col=expr pairs are compatible for assignment in
+                    INSERT INTO t1 SELECT ... FROM t2
+                      ON DUPLICATE KEY UPDATE col=expr [, col=expr]
+                */
+                TABLE::check_assignability_explicit_fields(*info.update_fields,
+                                                           *info.update_values);
     if (!res)
     {
       /*
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 929335f973e..e99aefcdf1f 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -11088,6 +11088,8 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to,
       if (!(*ptr)->vcol_info)
       {
         bitmap_set_bit(from->read_set, def->field->field_index);
+        if ((*ptr)->check_assignability_from(def->field))
+          goto err;
         (copy_end++)->set(*ptr,def->field,0);
       }
     }
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 4da967bb115..d5b5ac5eef4 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -523,6 +523,9 @@ int mysql_update(THD *thd,
     DBUG_RETURN(1);				/* purecov: inspected */
   }
 
+  if (table_list->table->check_assignability_explicit_fields(fields, values))
+    DBUG_RETURN(true);
+
   if (check_unique_table(thd, table_list))
     DBUG_RETURN(TRUE);
 
@@ -2082,7 +2085,8 @@ int multi_update::prepare(List<Item> &not_used_values,
   */
 
   int error= setup_fields(thd, Ref_ptr_array(),
-                          *values, MARK_COLUMNS_READ, 0, NULL, 0);
+                          *values, MARK_COLUMNS_READ, 0, NULL, 0) ||
+             TABLE::check_assignability_explicit_fields(*fields, *values);
 
   ti.rewind();
   while ((table_ref= ti++))
diff --git a/sql/table.cc b/sql/table.cc
index f6f293290cd..98b487ba991 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -9242,6 +9242,60 @@ bool TABLE::validate_default_values_of_unset_fields(THD *thd) const
 }
 
 
+/*
+  Check assignment compatibility of a value list against an explicitly
+  specified field list, e.g.
+    INSERT INTO t1 (a,b) VALUES (1,2);
+*/
+bool TABLE::check_assignability_explicit_fields(List<Item> fields,
+                                                List<Item> values)
+{
+  DBUG_ENTER("TABLE::check_assignability_explicit_fields");
+  DBUG_ASSERT(fields.elements == values.elements);
+
+  List_iterator<Item> fi(fields);
+  List_iterator<Item> vi(values);
+  Item *f, *value;
+  while ((f= fi++) && (value= vi++))
+  {
+    Item_field *item_field= f->field_for_view_update();
+    if (!item_field)
+    {
+      /*
+        A non-updatable field of a view found.
+        This scenario is caught later and an error is raised.
+        We could eventually move error reporting here. For now just continue.
+      */
+      continue;
+    }
+    if (value->check_assignability_to(item_field->field))
+      DBUG_RETURN(true);
+  }
+  DBUG_RETURN(false);
+}
+
+
+/*
+  Check assignment compatibility for a value list against
+  all visible fields of the table, e.g.
+    INSERT INTO t1 VALUES (1,2);
+*/
+bool TABLE::check_assignability_all_visible_fields(List<Item> &values) const
+{
+  DBUG_ENTER("TABLE::check_assignability_all_visible_fields");
+  DBUG_ASSERT(s->visible_fields == values.elements);
+
+  List_iterator<Item> vi(values);
+  for (uint i= 0; i < s->fields; i++)
+  {
+    if (!field[i]->invisible &&
+        (vi++)->check_assignability_to(field[i]))
+      DBUG_RETURN(true);
+  }
+  DBUG_RETURN(false);
+}
+
+
 bool TABLE::insert_all_rows_into_tmp_table(THD *thd,
                                            TABLE *tmp_table,
                                            TMP_TABLE_PARAM *tmp_table_param,
diff --git a/sql/table.h b/sql/table.h
index 1c09f463d9e..ddb0b183cf2 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1686,6 +1686,27 @@ public:
   Field **field_to_fill();
   bool validate_default_values_of_unset_fields(THD *thd) const;
 
+  // Check if the value list is assignable to the explicit field list
+  static bool check_assignability_explicit_fields(List<Item> fields,
+                                                  List<Item> values);
+  // Check if the value list is assignable to all visible fields
+  bool check_assignability_all_visible_fields(List<Item> &values) const;
+  /*
+    Check if the value list is assignable to:
+    - The explicit field list if fields.elements > 0, e.g.
+        INSERT INTO t1 (a,b) VALUES (1,2);
+    - All visible fields, if fields.elements==0, e.g.
+        INSERT INTO t1 VALUES (1,2);
+  */
+  bool check_assignability_opt_fields(List<Item> fields,
+                                      List<Item> values) const
+  {
+    DBUG_ASSERT(values.elements);
+    return fields.elements ?
+           check_assignability_explicit_fields(fields, values) :
+           check_assignability_all_visible_fields(values);
+  }
+
   bool insert_all_rows_into_tmp_table(THD *thd, 
                                       TABLE *tmp_table,
                                       TMP_TABLE_PARAM *tmp_table_param,