Backporting MDEV-29159 from 10.7 to 10.6bb-10.6-bar-assign

MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability

1. Store assignment failures on incompatible data types now raise errors if:
- STRICT_ALL_TABLES or STRICT_TRANS_TABLES sql_mode is used, and
- IGNORE is not used

Otherwise, only a warning is raised and the statement continues.

2. Changing the error/warning test as follows:

-ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET'
+ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `db`.`t`.`col`

so in case of a big table it's easier to see which column has the problem.
The new error text is also applied to SP variables.

1 files changed, 12 insertions, 7 deletions

diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index f73ef97b875..a5944751a4f 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -767,7 +767,7 @@ bool mysql_insert(THD *thd, TABLE_LIST *table_list,
   value_count= values->elements;
 
   if ((res= mysql_prepare_insert(thd, table_list, fields, values,
-                                 update_fields, update_values, duplic,
+                                 update_fields, update_values, duplic, ignore,
                                  &unused_conds, FALSE)))
   {
     retval= thd->is_error();
@@ -838,7 +838,8 @@ bool mysql_insert(THD *thd, TABLE_LIST *table_list,
     behaviour for non-transactional tables.
   */
   if (values->elements &&
-      table_list->table->check_assignability_opt_fields(fields, *values))
+      table_list->table->check_assignability_opt_fields(fields, *values,
+                                                        ignore))
     goto abort;
 
   while ((values= its++))
@@ -1630,7 +1631,8 @@ static void prepare_for_positional_update(TABLE *table, TABLE_LIST *tables)
 int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
                          List<Item> &fields, List_item *values,
                          List<Item> &update_fields, List<Item> &update_values,
-                         enum_duplicates duplic, COND **where,
+                         enum_duplicates duplic, bool ignore,
+                         COND **where,
                          bool select_insert)
 {
   SELECT_LEX *select_lex= thd->lex->first_select_lex();
@@ -1705,7 +1707,8 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
                ON DUPLICATE KEY UPDATE col=expr [, col=expr];
            */
            TABLE::check_assignability_explicit_fields(update_fields,
-                                                      update_values);
+                                                      update_values,
+                                                      ignore);
 
       select_lex->no_wrap_view_item= FALSE;
     }
@@ -3804,7 +3807,7 @@ int mysql_insert_select_prepare(THD *thd, select_result *sel_res)
 
   if ((res= mysql_prepare_insert(thd, lex->query_tables, lex->field_list, 0,
                                  lex->update_list, lex->value_list,
-                                 lex->duplicates,
+                                 lex->duplicates, lex->ignore,
                                  &select_lex->where, TRUE)))
     DBUG_RETURN(res);
 
@@ -3905,7 +3908,8 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
           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);
+     res= table_list->table->check_assignability_opt_fields(*fields, values,
+                                                            lex->ignore);
   }
 
   if (!res && fields->elements)
@@ -3968,7 +3972,8 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
                       ON DUPLICATE KEY UPDATE col=expr [, col=expr]
                 */
                 TABLE::check_assignability_explicit_fields(*info.update_fields,
-                                                           *info.update_values);
+                                                           *info.update_values,
+                                                           lex->ignore);
     if (!res)
     {
       /*