The idea of the patch for MDEV-28918 is that it consistently disallows cast between INT and INET6 at the statement preparation time.

The same thing happens in this scenario:

CREATE OR REPLACE TABLE t1 (a INT);

INSERT INTO t1 VALUES (ROW(1,2));

ERROR 1241 (21000): Operand should contain 1 column(s)

There is no conversion between INT and ROW. This statement aborts:

• at the preparation time, before starting iterating rows
• no matter what the current sql_mode is.

After MDEV-28918, exactly the same thing happens on conversion between INT and INET6. There is no such conversion. The statement also aborts at the preparation time, independently from sql_mode.

The GEOMETRY data type was also fixed to work the same way with ROW. This statement:

insert into t1 (col_geom) values (1);

now aborts at the preparation time.

There is no conversion from INT to GEOMETRY.

The patch for MDEV-28918 makes things more consistent across different data types, and provides more reasonable behavior:
There is no sense to start iterating rows if it's clear by the data type combination that the operation would fail anyway.

Before the patch such a mixture of INT and GEOMETRY failed, on the first row. It could only succeed on an empty table.

The idea of this report, on the other hand, is that it's an arbitrary limitation which doesn't solve anything other than a special case, and brings a lot of new inconsistencies and inconveniences instead.

To summarize the effect of the change on the users (even aside inconsistencies, as they are always subjective):

When users care about the exact result of the conversion and prefer getting an error and dealing with it, they use strict modes and normal INSERT / ALTER operations. Thus they are already getting an error, even without the change. After the patch, they'll continue getting an error, only instead of the old informative one with column names, values etc., they'll be getting the non-informative generic "illegal parameter data types".

When users don't care about the exact result and are willing to accept an implicit possibly lossy conversion in order to have the operation succeed, they use non-strict modes and/or IGNORE clauses. Now they can do so, get somewhat informative warnings and possibly act upon them post-factum.
After the patch, they won't be able to perform the operation regardless non-strict mode or IGNORE clause, they will be getting generic non-informative errors instead.

I failed to find a scenario where the change will actually be beneficial for the user.

So, I am assigning it to serg as an architect. I have written most if what I think about this change, and am not going to fight it any further, but if you really want to have it in the future, please write up a standard official answer which we will be giving the users who will reasonably complain about it and require an explanation why we did it, which should preferably be something better than "the idea of the limitation is that it limits".

in bb-10.7-bar-MDEV-29159

3ebbfd88a03 is ok to push

Pushed to 10.7.

Change summary:

1. 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.

2. Store assignment failures on incompatible data types now raise errors and abort the statement if:

• STRICT_ALL_TABLES or STRICT_TRANS_TABLES sql_mode is used, and
• IGNORE is not used

e.g.:

ERROR HY000: Cannot cast 'point' as 'double' in assignment of `test`.`t1`.`col1`

Otherwise, only a warning is raised and the statement continues, then a warning is issued per row.

Warning  4078  Cannot cast 'double' as 'inet6' in assignment of `test`.`t1`.`col1`

Warning  1292  Incorrect inet6 value: '0' for column `test`.`t1`.`col1` at row 1

Warning  1292  Incorrect inet6 value: '0' for column `test`.`t1`.`col1` at row 2

For the record (as we will likely face questions about it sooner or later), in layman's terms, the new limitation introduced by MDEV-29159 applies to column types, not to values. The operations are prohibited regardless of what is actually stored or not stored in the columns in question.

E.g. this is prohibited:

MariaDB [test]> create table t (a int);

Query OK, 0 rows affected (0.074 sec)

MariaDB [test]> alter table t modify a inet6;

ERROR 4078 (HY000): Cannot cast 'int' as 'inet6' in assignment of `test`.`t`.`a`

– a column cannot be converted between incompatible data types even if the table is empty.

And this is prohibited too:

MariaDB [test]> create table t1 (a int);

Query OK, 0 rows affected (0.033 sec)

MariaDB [test]> insert into t1 values (null),(null);

Query OK, 2 rows affected (0.012 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> create table t2 (b inet6);

Query OK, 0 rows affected (0.026 sec)

MariaDB [test]> insert into t2 select * from t1;

ERROR 4078 (HY000): Cannot cast 'int' as 'inet6' in assignment of `test`.`t2`.`b`

– insert/select between incompatible types fails even if all values in question are NULLs and the target column is NULL-able.

The patch above doesn't change this, but it ensures that IGNORE clause and non-strict SQL modes relax the limitation and make the operation possible when necessary (and improves the error message so it's easier to understand what is happening).

Also important to mention, as customary with any new restrictions in new versions, any affected operation will break replication from an old master to a new slave. User must be aware.