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

CHECK constaint refers to old table name after temporary table rename

    XMLWordPrintable

    Details

      Description

      Starting in 10.5.10, when a temporary table is renamed, any CHECK constraints implicitly refer to the old table name. The below test script worked until upgrading from 10.5.9 to 10.5.10. If the `TEMPORARY` keyword is removed it works without issue.

      # Ensure state is stable
      DROP TEMPORARY TABLE IF EXISTS `t1`;
      DROP TEMPORARY TABLE IF EXISTS `t2`;
       
      # Run test
      CREATE TEMPORARY TABLE `t1` (
        `i` INT AUTO_INCREMENT,
        `s` VARCHAR(16) CHECK (CHAR_LENGTH(`s`) > 5),
        PRIMARY KEY (`i`)
      ) ENGINE=INNODB;
       
      RENAME TABLE `t1` TO `t2`;
       
      ALTER TABLE `t2` AUTO_INCREMENT = 2; # Error: Unknown column '`test`.`t1`.`s`' in 'CHECK'
       
      SHOW CREATE TABLE `t2`;
      /*
      CREATE TEMPORARY TABLE `t2` (
        `i` int(11) NOT NULL AUTO_INCREMENT,
        `s` varchar(16) DEFAULT NULL CHECK (char_length(`s`) > 5),
        PRIMARY KEY (`i`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      */
       
      # Cleanup
      DROP TEMPORARY TABLE IF EXISTS `t1`;
      DROP TEMPORARY TABLE IF EXISTS `t2`;
      

      The above code is just how the error was found - the ALTER can be replaced by an insert or any statement where the CHECK is loaded.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              rmhumphries Robert Humphries
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration