[MDEV-3844] INSERT with no fields names on a table with Virtual Columns raises an error Created: 2012-11-09  Updated: 2012-11-17  Resolved: 2012-11-17

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Elena Stepanova
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

One can INSERT a row into a table without specifying the fields names. But if the table contains Virtual Columns, you don't include those values in the statement. But then, MariaDB says that column count doesn't match.

MariaDB [test]> DROP VIEW IF EXISTS `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [test]> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.46 sec)

MariaDB [test]> CREATE TABLE `t1` (
-> `a` INTEGER UNSIGNED NULL DEFAULT NULL,
-> `b` INTEGER UNSIGNED GENERATED ALWAYS AS (`a` + 1) PERSISTENT
-> )
-> ENGINE = Aria
-> ROW_FORMAT = PAGE;
Query OK, 0 rows affected (0.18 sec)

MariaDB [test]> INSERT INTO `t1` VALUES (1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

 Comments   
Comment by Elena Stepanova [ 2012-11-09 ]

Hi Federico,

This behavior is consistent with other cases when a column has a default value or is calculated automatically, e.g. is an auto-increment column. In case of virtual columns, if you want to insert without specifying field names, you can use the 'default' keyword:

INSERT INTO `t1` VALUES (1, default);

I suppose NULL should also work.

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