MARIADB sequences - incrementing by 2

Viewed 51

I have the following MARIADB code. It's supposed to demonstrate:

  1. Constructing tables using sequences for incrementing the ID.
  2. Using a temporary table+join to INSERT data into a table, while incrementing the ID.

Procedure:

  1. Sequence S1 and table T1 are created. T1_ID is incremented with S1
  2. Sequence S2 and table T2 are created. T2_ID is incremented with S2
  3. Table T1 is filled with data. All is fine.
  4. Temporary table TEMP_T2 is created and filled with data. No ID in this table. Column T1_NAME is a cross reference to SHORT_NAME in table T1.
  5. The T1_ID is introduced into table TEMP_T2 with a join. The result of this SELECT is inserted into T2. Here, the sequence S2 should auto-increment T2_ID.

For some reason, at the end, T2 looks like this:

T2_ID|T1_ID|NAME|
-----+-----+----+
    2|    1|y   |
    4|    2|x   |
    6|    2|z   |

Why was T2_ID double-incremented?

Thanks!


USE DB1;

SET FOREIGN_KEY_CHECKS = 0;


DROP SEQUENCE IF EXISTS `S2`;
DROP SEQUENCE IF EXISTS `S1`;
DROP TABLE IF EXISTS `T2`;
DROP TABLE IF EXISTS `T1`;

-- Create sequence S1 and able T1
CREATE SEQUENCE `S1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`S1`, 1, 0);

CREATE TABLE `T1` (
  `T1_ID` tinyint(4) NOT NULL DEFAULT nextval(`S1`),
  `SHORT_NAME` varchar(10) NOT NULL,
  PRIMARY KEY (`T1_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Create sequence T2 and table T2
CREATE SEQUENCE `S2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`S2`, 1, 0);

CREATE TABLE `T2` (
  `T2_ID` int(11) NOT NULL DEFAULT nextval(`S2`),
  `T1_ID` int(11) DEFAULT NULL, 
  `NAME` varchar(100) DEFAULT NULL COLLATE 'utf8mb3_bin',
  PRIMARY KEY (`T2_ID`),
  UNIQUE KEY `T2_NAME_UN` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


-- Load data into T1
DELETE FROM T1;

INSERT INTO T1(SHORT_NAME) VALUES
     ('a'),
     ('b'),
     ('c');

SELECT * FROM T1;

-- Create temporary file for joining with T1
DROP TABLE IF EXISTS `TEMP_T2`;
CREATE TEMPORARY TABLE `TEMP_T2` (
  `T1_NAME` varchar(10) DEFAULT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  UNIQUE KEY `T2_NAME_UN` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


DELETE FROM TEMP_T2 ;

-- Insert data into the temporary table
INSERT INTO TEMP_T2(T1_NAME,NAME) VALUES
     ('b','x'),
     ('a','y'),
     ('b','z');

SELECT * FROM TEMP_T2;
  
# Do a join with TEMP_T2 x T1 and insert into T2
INSERT INTO T2(T1_ID,NAME)
SELECT 
t1.T1_ID ,
t2.NAME
FROM TEMP_T2 AS t2
INNER JOIN T1 AS t1
  ON t2.T1_NAME =t1.SHORT_NAME ;

SELECT * FROM T2;

2 Answers

I've found this as a reported existing bug MDEV-29540 in INSERT ... SELECT as it pertains to sequences in default values of columns.

Thanks for the responses.

  1. I'm using SEQUENCE rather than AUTO_INCREMENT because I was told that it is the more modern way. It also enables retrieving the last ID of any specific table.
  2. It's strange that this should be a bug. It seems like really basic functionality. But so it is...
Related