[MDEV-30016] Virtual columns do not support autoincrement columns Created: 2022-11-15  Updated: 2022-12-02  Resolved: 2022-12-02

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Minor
Reporter: Marc Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None


 Description   

MariaDB 10.5.17:
adding a auto-increment coulmn to a virtual (computed column) results in error 1901:

ALTER TABLE `rdShopOrder` ADD `Bestellnummer` CHAR(18) AS (CONCAT_WS("-",YEAR(Datum),LPAD(OrganisationID,4,"0"),LPAD(ID,8,"0")) ) VIRTUAL
 
Error (1901): Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `ID`

according to the documentation this is true for stored columns, as I can imagine, but virtual columns should support this behaviour.

 Comments   
Comment by Ralf Gebhardt [ 2022-11-17 ]

mokraemer, please check https://mariadb.com/kb/en/auto_increment/#check-constraints-default-values-and-virtual-columns

Comment by Marc [ 2022-11-17 ]

Sure - I know that comment. But I'd state this is bug, not permitting auto-increment columns in VIRTUAL columns.

On
https://mariadb.com/kb/en/generated-columns/
is stated: "VIRTUAL: This type's value is not stored at all. Instead, the value is generated dynamically when the table is queried. This type is the default."

so, a virtual column is the same, as adding the sql portion to the query part. If this is true, there is NO reason at all, to forbid auto-increment columns in virtual columns.

MDEV-11117 shows only problems with constraints (and maybe persistent columns)

Generated at Thu Feb 08 10:13:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.