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

Allow ALGORITHM=NOCOPY or INSTANT for changes of virtual column type

Details

    Description

      Changing the type of a virtual column will not affect the format of the clustered index. It may imply DROP INDEX and ADD INDEX for any indexes that are defined on the virtual column. If such indexes are to remain after the ALTER TABLE operation, then changing the virtual column type obviously cannot be an instantaneous operation.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-11424 Instant ALTER TABLE of failure-free record format changes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14046 Allow ALGORITHM=INPLACE for 10.1 tables that contain virtual columns

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14341 Allow LOCK=NONE in table-rebuilding ALTER when indexed virtual columns exist

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17468 Avoid table rebuild on operations on generated columns

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-5800 indexes on virtual (not materialized) columns

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15476 Inplace algorithm doesn't support changing virtual column datatype

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19214 Virtual column type cannot be converted from one to another - unhelpful error message

          • Major - Major loss of function.
          • Open

          Activity

            marko Marko Mäkelä added a comment -

            With ALGORITHM=COPY, changing the data type of a virtual column will cause each value to be validated. ALTER IGNORE TABLE would issue warnings for any invalid values, and without IGNORE the operation would be aborted.

            The type compatibility aspect of virtual columns is similar to constraints. By default, ADD CONSTRAINT requires validation as well, both for CHECK and FOREIGN KEY. Currently this validation is only performed by ALGORITHM=COPY; it would be much more efficient to perform the validation only (scan the table) and then change the metadata instantly.

            Theoretically, we could skip the validation if the type of the virtual column is changed to a more permissive one, or if NOT NULL is being added and the virtual column expression never returns NULL. But even in this case, it could be that the virtual column was inconsistently created (with ALTER IGNORE) earlier, and also the altered virtual column would fail validation.

            For more efficient algorithms, ALGORITHM=COPY is the specification that dictates what should be done. If ALTER IGNORE…ALGORITHM=COPY is issuing warnings for incompatible values, then it looks like we should not support instant operation (without validation) even with the IGNORE clause. We might want a separate WITHOUT VALIDATION clause.

            marko Marko Mäkelä added a comment - With ALGORITHM=COPY , changing the data type of a virtual column will cause each value to be validated. ALTER IGNORE TABLE would issue warnings for any invalid values, and without IGNORE the operation would be aborted. The type compatibility aspect of virtual columns is similar to constraints. By default, ADD CONSTRAINT requires validation as well, both for CHECK and FOREIGN KEY . Currently this validation is only performed by ALGORITHM=COPY ; it would be much more efficient to perform the validation only (scan the table) and then change the metadata instantly. Theoretically, we could skip the validation if the type of the virtual column is changed to a more permissive one, or if NOT NULL is being added and the virtual column expression never returns NULL . But even in this case, it could be that the virtual column was inconsistently created (with ALTER IGNORE ) earlier, and also the altered virtual column would fail validation. For more efficient algorithms, ALGORITHM=COPY is the specification that dictates what should be done. If ALTER IGNORE…ALGORITHM=COPY is issuing warnings for incompatible values, then it looks like we should not support instant operation (without validation) even with the IGNORE clause. We might want a separate WITHOUT VALIDATION clause.

            People

              thiru Thirunarayanan Balathandayuthapani
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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