Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30016

Virtual columns do not support autoincrement columns

Details

    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.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          ralf.gebhardt Ralf Gebhardt added a comment -

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

          ralf.gebhardt Ralf Gebhardt added a comment - mokraemer , please check https://mariadb.com/kb/en/auto_increment/#check-constraints-default-values-and-virtual-columns
          mokraemer Marc added a comment - - edited

          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)

          mokraemer Marc added a comment - - edited 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)

          People

            serg Sergei Golubchik
            mokraemer Marc
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.