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
-
MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error in recent releases
- Closed
- links to