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

Incorrect sequence values in INSERT SELECT

    XMLWordPrintable

Details

    Description

      The sequence used as the default value in the table ('t1' in my example) produces incorrect values when the data is inserted from INSERT SELECT.

      Example:

      SELECT VERSION();
       
      DROP TABLE IF EXISTS `t1`;
      DROP SEQUENCE IF EXISTS `s1`;
       
      CREATE SEQUENCE IF NOT EXISTS `s1`;
       
      CREATE TABLE IF NOT EXISTS `t1` (
        `a` BIGINT UNSIGNED NOT NULL PRIMARY KEY
            DEFAULT (NEXT VALUE FOR `s1`),
        `b` CHAR(1) NOT NULL
      ) ENGINE=InnoDB;
       
      SHOW CREATE TABLE `s1`;
       
      INSERT INTO `t1` (`b`) VALUES ('a');
      INSERT INTO `t1` (`b`) VALUES ('b'), ('c');
      INSERT INTO `t1` (`b`) VALUES ('d');
      INSERT INTO `t1` (`b`)
      SELECT `c`
      FROM (
        SELECT 'e' `c`
        UNION
        SELECT 'f' `c`
        UNION
        SELECT 'g' `c`
      ) `der`;
        
      SELECT
        `a`, `b`
      FROM
        `t1`;
      

      Result:

      VERSION()
      10.6.9-MariaDB-1:10.6.9+maria~deb11
      Table Create Table
      s1 CREATE TABLE `s1` (
      `next_not_cached_value` bigint(21) NOT NULL,
      `minimum_value` bigint(21) NOT NULL,
      `maximum_value` bigint(21) NOT NULL,
      `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
      `increment` bigint(21) NOT NULL COMMENT 'increment value',
      `cache_size` bigint(21) unsigned NOT NULL,
      `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
      `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
      ) ENGINE=InnoDB SEQUENCE=1
      a b
      1 a
      2 b
      3 c
      4 d
      6 e
      8 f
      10 g

      See dbfiddle.

      Related bug: MDEV-17037.

      Attachments

        Issue Links

          Activity

            People

              danblack Daniel Black
              wchiquito William Chiquito
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.