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, 2 insertions, 2 deletions

diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result
index f941447a677..88cd55c0251 100644
--- a/mysql-test/main/gis.result
+++ b/mysql-test/main/gis.result
@@ -688,9 +688,9 @@ object_id	geometrytype(geo)	ISSIMPLE(GEO)	ASTEXT(centroid(geo))
 drop table t1;
 create table t1 (fl geometry not null);
 insert into t1 values (1);
-ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+ERROR HY000: Illegal parameter data types geometry and int for operation 'SET'
 insert into t1 values (1.11);
-ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+ERROR HY000: Illegal parameter data types geometry and decimal for operation 'SET'
 insert into t1 values ("qwerty");
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 insert into t1 values (pointfromtext('point(1,1)'));