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

incorrect sequence values in INSERT from SELECT query

    Details

      Description

      Sequence used as default value in table ('hash' in my example) produces every second value when data are inserted into table 'hash' as select from other_table.

      My example:

      CREATE TABLE hash (
        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        ufileid BIGINT(20) UNSIGNED DEFAULT nextval(seq_ufileid),
        hash VARCHAR(64) DEFAULT NULL,
        PRIMARY KEY (id)
      );
      

      CREATE TABLE `seq_ufileid` (
        `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,
        `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;
      

      CREATE TABLE other_table (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `hash` varchar(255) COLLATE utf8_polish_ci NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci ROW_FORMAT=DYNAMIC;
      

      ----------------------------------------------------------------------------------------------------
      Execution of the statement:

      INSERT INTO hash (hash) SELECT hash FROM other_table;
      SELECT ufileid, hash FROM hash ORDER BY ufileid DESC LIMIT 3;
      

      returns:

      7828	line3
      7827	line2
      7826	line1
      

      the sequence works correctly when INSERT INTO query is used:

      INSERT INTO hash(hash, hash_type) VALUES('line1');
      INSERT INTO hash(hash, hash_type) VALUES('line2');
      INSERT INTO hash(hash, hash_type) VALUES('line3');
      SELECT ufileid, hash FROM hash ORDER BY ufileid DESC LIMIT 3;
      

      7831	line3
      7830	line2
      7829	line1
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              jjowsiak@gmail.com Jan Owsiak
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: