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.

1 files changed, 40 insertions, 2 deletions

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)
     {
       /*