[MDEV-8653] Persistent virtual fields don't insert with auto_incremented fields Created: 2015-08-20  Updated: 2017-01-12  Resolved: 2017-01-12

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 5.3.12, 5.5, 10.0, 10.1
Fix Version/s: 10.2.3

Type: Bug Priority: Minor
Reporter: Terry Cullen Assignee: Sergei Golubchik
Resolution: Fixed Votes: 2
Labels: virtual_columns
Environment:

Installed via yum on Redhat


Issue Links:
Duplicate
is duplicated by MDEV-6809 Persistent columns don't with with au... Closed
is duplicated by MDEV-9116 Persistent virtual column cannot cope... Closed

 Description    

MariaDB [testdb]> CREATE TABLE `cats` (
 
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
    ->   `name` varchar(255),
 
    ->   `full_nm` varchar(255) AS (TRIM(CONCAT(name, ' - ', id))) PERSISTENT,
 
    ->   `display_nm` varchar(255) AS (TRIM(CONCAT(name, ' - ', id))) VIRTUAL,
 
    ->     PRIMARY KEY (`id`)
 
    ->   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
MariaDB [testdb]> insert into cats (`name`) values ('freddy'), ('sammy'), ('spot'), ('boots');
 
Query OK, 4 rows affected (0.00 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [testdb]> select * from cats;
 
+----+--------+------------+------------+
 
| id | name   | full_nm    | display_nm |
 
+----+--------+------------+------------+
 
|  1 | freddy | freddy - 0 | freddy - 1 |
 
|  2 | sammy  | sammy - 0  | sammy - 2  |
 
|  3 | spot   | spot - 0   | spot - 3   |
 
|  4 | boots  | boots - 0  | boots - 4  |
 
+----+--------+------------+------------+
 
4 rows in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2015-08-20 ]

Thanks for the report.
As a workaround, you can perform an operation on the table (any void operation, just to get virtual column values updated, e.g. ALTER TABLE cats FORCE or UPDATE cats SET name = name, etc.).

igor,

If it's a known limitation, please reassign it back to me or directly to Ian Gilfillan to update the documentation.

Comment by Martin Rieger [ 2015-11-12 ]

There is another hitch to this. An above recommended workaround is to simply update the auto_increment column with its unchanged value. Now, if the table also contains a Timestamp column with the "on update current_timestamp" attribute, the timestamp gets updated every time (not only the first time) such a pseudo update statement is executed, even though no value is changed. On the first update one might think that is OK because the persistent computed column gets changed, but on later such updates nothing is changed at all and yet the timestamp gets updated.

Comment by Sergei Golubchik [ 2017-01-12 ]

This is "fixed" in 10.2, in a sense that 10.2 does not allow virtual columns to refer to auto-increment fields. This is what MySQL does too.

Technically, it's possible to make auto-increment and virtual columns to work together, but only by completely reimplementing auto-increment code. It's doable, but a very big high-risk change, certainly not a bug fix.

Generated at Thu Feb 08 07:28:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.