[MDEV-22579] No error when inserting DEFAULT(non_virtual_column) into a virtual column Created: 2020-05-15  Updated: 2020-05-18  Resolved: 2020-05-16

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.5.4, 10.1.46, 10.2.33, 10.3.24, 10.4.14

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-21995 Server crashes in Item_field::real_ty... Closed
relates to MDEV-22560 Crash on a table value constructor wi... Closed
relates to MDEV-22591 Debug build crashes on EXECUTE IMMEDI... Closed
relates to MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFA... Closed

 Description   

I create a table with a virtual column:

SET sql_mode=STRICT_ALL_TABLES;
 
CREATE OR REPLACE TABLE t1 (
 
 a INT NOT NULL DEFAULT 10,
 
 b INT AS (a+1) VIRTUAL
 
) ENGINE=MyISAM;

Now I insert an explicit value to the virtual column, and it returns an error as expected:

INSERT INTO t1 (b) VALUES (10);


ERROR 1906 (HY000): The value specified for generated column 'b' in table 't1' has been ignored

Now I insert a value into the virtual column, but using DEFAULT(a) instead of an explicit value:

INSERT INTO t1 (b) VALUES (DEFAULT(a));


Query OK, 1 row affected (0.001 sec)

It inserted the record without the error. Looks wrong. Expect to get the same error.

 Comments   
Comment by Alexander Barkov [ 2020-05-15 ]

Additionally, starting from 10.2, the following statement erroneously returns the error:

EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING DEFAULT;


ERROR 1906 (HY000): The value specified for generated column 'b' in table 't1' has been ignored

but if I use DEFAULT directly (instead of a binding it):

INSERT INTO t1 (b) VALUES(DEFAULT);

it works without errors.

The variant which binds DEFAULT as a parameter should also work without errors.

Generated at Thu Feb 08 09:15:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.