A similar problem can be observed upon ALTER which, for example, adds a virtual column of a too short length. With certain ALTER algorithms it succeeds without warnings and truncates the values.

Example:

--source include/have_innodb.inc

create table t (a int) engine=InnoDB;

insert into t values (1),(150);

alter table t add b tinyint as (a), algorithm=nocopy;

select * from t;

drop table t;

alter table t add b tinyint as (a), algorithm=nocopy;

select * from t;

a	b

1	1

150	127

With ALTER algorithm COPY it fails as expected:

alter table t add b tinyint as (a), algorithm=copy;

bug.t 'innodb'                           [ fail ]

        Test ended at 2021-09-19 16:21:23

CURRENT_TEST: bug.t

mysqltest: At line 5: query 'alter table t add b tinyint as (a), algorithm=copy' failed: ER_WARN_DATA_OUT_OF_RANGE (1264): Out of range value for column 'b' at row 2

Hi @Nikita Malyavin, I'm Masashi, a community contributor wannabe for MariaDB, and now trying to solve this issue with @nayuta-yanagisawa.
I have an open question about this issue, please let me about your thoughts when you get a chance

Basically we want to compare 1. column maximal length defined in DDL and 2. computed value length of a virtual column, and if 2 exceeds 1, we raise error or warning according to strict mode.
However, since virtual columns are currently computed only when the column is read (e.g. SELECT), we cannot directly validate the data length of a virtual column when a row is inserted.
So what I'm considering now is to compute virtual column value not only on SELECT but also on INSERT. We can modify control flow around this line: https://github.com/MariaDB/server/blob/6ec17142dcfb1e9d9f41211ed1b6d82e062d1541/sql/table.cc#L8854 , and then compare and validate the length.

The question is, do you think the above approach reasonable? After this fix, we'll have additional computation overhead on every INSERT query. Is it acceptable?

Thanks!

nikitamalyavin ^^