[MDEV-24582] INSERT silently truncates too long value for a virtual column without warnings or errors - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.5
Component/s: Data Manipulation - Insert, Virtual Columns
Labels:
None

Description

CREATE TABLE t1 (a VARCHAR(3), v VARCHAR(3) AS (CONCAT('x-',a)) VIRTUAL);

INSERT INTO t1 (a) VALUES ('foo');

SELECT * FROM t1;

10.2 ab271ee7
INSERT INTO t1 (a) VALUES ('foo');
SELECT * FROM t1;
a v
foo x-f

Reproducible on all of 5.5-10.6.

The expected result would be a warning with non-strict SQL mode and an error with the strict mode. MySQL does exactly that:

MySQL 5.7 3701bd36be with sql-mode=''
INSERT INTO t1 (a) VALUES ('foo');
Warnings:
Warning 1265 Data truncated for column 'v' at row 1
SELECT * FROM t1;
a v
foo x-f

MySQL 5.7 3701bd36be with sql-mode='STRICT_ALL_TABLES'
mysqltest: At line 2: query 'INSERT INTO t1 (a) VALUES ('foo')' failed: 1406: Data too long for column 'v' at row 1

Attachments

Issue Links

relates to

MDEV-24583 ASAN use-after-poison in create_tmp_table or assertion failures after failed REPLACE into table with virtual/stored column

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2021-09-19 13:22

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;

10.7 225564993
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

Elena Stepanova added a comment - 2021-09-19 13:22 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; 10.7 225564993 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

Masashi Tomooka added a comment - 2022-06-03 03:13

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!

Masashi Tomooka added a comment - 2022-06-03 03:13 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!

Nayuta Yanagisawa (Inactive) added a comment - 2022-06-06 12:35

nikitamalyavin ^^

Nayuta Yanagisawa (Inactive) added a comment - 2022-06-06 12:35 nikitamalyavin ^^

People

Assignee:: Nikita Malyavin

Reporter:: Elena Stepanova

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2021-01-13 18:24

Updated:: 2024-09-10 15:06

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server