Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Incomplete
-
10.3.9
-
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
|
I don't see anything wrong with the result, you've got consequent values 7826, 7827, 7828. Have you pasted a wrong result set?