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

Incorrect sequence values in INSERT SELECT

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

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error in recent releases

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

          Web Link SO user with same problem

          Activity

            Ascending order - Click to sort in descending order
            wchiquito William Chiquito created issue -
            danblack Daniel Black made changes -
            Field Original Value New Value
            danblack Daniel Black made changes -
            Fix Version/s 10.3 [ 22126 ]
            danblack Daniel Black made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            danblack Daniel Black made changes -
            Assignee Daniel Black [ danblack ]
            danblack Daniel Black made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            danblack Daniel Black made changes -
            Assignee Daniel Black [ danblack ] Michael Widenius [ monty ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            wchiquito William Chiquito made changes -
            wchiquito William Chiquito made changes -
            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*:
            {code:sql}
            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`;
            {code}

            *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|(x)
            |8|f|(x)
            |10|g|(x)

            See [dbfiddle|https://dbfiddle.uk/sLlUhNLQ].

            Related bug: [MDEV-17037|https://jira.mariadb.org/browse/MDEV-17037].             		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*:
            {code:sql}
            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`;
            {code}

            *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|(x)
            |8|f|(x)
            |10|g|(x)

            See [dbfiddle|https://dbfiddle.uk/sLlUhNLQ].
            danblack Daniel Black made changes -
            wchiquito William Chiquito made changes -
            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*:
            {code:sql}
            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`;
            {code}

            *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|(x)
            |8|f|(x)
            |10|g|(x)

            See [dbfiddle|https://dbfiddle.uk/sLlUhNLQ].             		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*:
            {code:sql}
            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`;
            {code}

            *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|(x)
            |8|f|(x)
            |10|g|(x)

            See [dbfiddle|https://dbfiddle.uk/sLlUhNLQ].

            Related bug: [MDEV-17037|https://jira.mariadb.org/browse/MDEV-17037].
            cvicentiu Vicențiu Ciorbaru made changes -
            Assignee Michael Widenius [ monty ] Vicențiu Ciorbaru [ cvicentiu ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Assignee Vicențiu Ciorbaru [ cvicentiu ] Daniel Black [ danblack ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            danblack Daniel Black made changes -
            Fix Version/s 10.3.37 [ 28404 ]
            Fix Version/s 10.4.27 [ 28405 ]
            Fix Version/s 10.5.18 [ 28421 ]
            Fix Version/s 10.6.11 [ 28441 ]
            Fix Version/s 10.7.7 [ 28442 ]
            Fix Version/s 10.8.6 [ 28443 ]
            Fix Version/s 10.9.4 [ 28444 ]
            Fix Version/s 10.10.2 [ 28410 ]
            Fix Version/s 10.11.1 [ 28454 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -

            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.