[MDEV-17037] incorrect sequence values in INSERT from SELECT query - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Incomplete
Affects Version/s: 10.3.9
Fix Version/s: N/A
Component/s: Data Manipulation - Insert, Sequences
Labels:
- need_feedback
Environment:
MS Windows 10 Pro
Intel Core i7-4610M

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

Ascending order - Click to sort in descending order

Jan Owsiak created issue - 2018-08-22 15:21

Jan Owsiak made changes - 2018-08-22 15:21

Field	Original Value	New Value
Description	Sequence used as default value in table ('hash' in my example) produces every second velue when data are inerted 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	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

Elena Stepanova made changes - 2018-09-02 17:33

Summary

incorret sequence values in INSERT from SELECT query

incorrect sequence values in INSERT from SELECT query

Elena Stepanova made changes - 2018-09-02 17:40

Epic/Theme

server

Elena Stepanova made changes - 2018-09-02 17:41

Labels

sequence_storage_engine

Elena Stepanova made changes - 2018-09-02 17:41

Component/s

Sequences [ 14009 ]

Elena Stepanova made changes - 2018-09-02 17:42

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

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:
{code:sql}
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)
);
{code}
{code:sql}
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;
{code}
{code:sql}
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;
{code}
----------------------------------------------------------------------------------------------------
Execution of the statement:
{code:sql}
INSERT INTO hash (hash) SELECT hash FROM other_table;
SELECT ufileid, hash FROM hash ORDER BY ufileid DESC LIMIT 3;
{code}
returns:
{noformat}
7828 line3
7827 line2
7826 line1
{noformat}

the sequence works correctly when INSERT INTO query is used:
{code:sql}
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;
{code}
{noformat}
7831 line3
7830 line2
7829 line1
{noformat}

Elena Stepanova added a comment - 2018-09-02 17:46

I don't see anything wrong with the result, you've got consequent values 7826, 7827, 7828. Have you pasted a wrong result set?

Elena Stepanova added a comment - 2018-09-02 17:46 I don't see anything wrong with the result, you've got consequent values 7826, 7827, 7828. Have you pasted a wrong result set?

Elena Stepanova made changes - 2018-09-02 17:46

Labels

need_feedback

Elena Stepanova made changes - 2018-09-30 23:20

Fix Version/s		N/A [ 14700 ]
Resolution		Incomplete [ 4 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 89033 ]

MariaDB v4 [ 154839 ]

William Chiquito made changes - 2022-09-16 05:28

Link

This issue relates to ~~MDEV-29540~~ [ ~~MDEV-29540~~ ]

Daniel Black made changes - 2022-09-16 05:32

Link

This issue relates to ~~MDEV-29540~~ [ ~~MDEV-29540~~ ]

People

Assignee:: Unassigned

Reporter:: Jan Owsiak

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-08-22 15:21

Updated:: 2022-09-16 05:32

Resolved:: 2018-09-30 23:20

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration