[MDEV-6809] Persistent columns don't with with autoincrement Created: 2014-09-29  Updated: 2017-01-12  Resolved: 2017-01-12

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

Type: Bug Priority: Minor
Reporter: Tim Bray Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: virtual_columns

Issue Links:
Duplicate
duplicates MDEV-8653 Persistent virtual fields don't inser... Closed

 Description   

Persistent columns don't work if they are based on an auto increment column.

CREATE TABLE `orgs` (
 
  `oid` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `name` varchar(60) NOT NULL DEFAULT '',
 
  `oid2` int(11) unsigned as (`oid`) PERSISTENT,
 
  PRIMARY KEY (`oid`));
 
 
 
insert into orgs ( name) values ('test 1'), ('test 2');
 

MariaDB [tim_test]> select * from orgs;
 
+-----+--------+------+
 
| oid | name   | oid2 |
 
+-----+--------+------+
 
|   1 | test 1 |    0 |
 
|   2 | test 2 |    0 |
 
+-----+--------+------+

I would expect the oid2 column to mirror the oid field.

It works on update.

update orgs set oid=5 where oid=2;
 

MariaDB [tim_test]> select * from orgs;
 
+-----+--------+------+
 
| oid | name   | oid2 |
 
+-----+--------+------+
 
|   1 | test 1 |    0 |
 
|   5 | test 2 |    5 |
 
+-----+--------+------+



 Comments   
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:14:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.