Auto increment goes wild when inserting data from sequence storage engine into a InnoDB table

When inserting data into a InnoDB table with an auto increment column with an INSERT INTO SELECT statement and the select clause contains a sequence storage engine the auto increment value is increased with the next power of two instead with the number of inserted rows.

Create a table and insert some data from a sequence storage engine table.

drop table if exists MY_TABLE cascade;

create table MY_TABLE (

  col1 smallint not null auto_increment,

  col2 smallint not null,

  primary key (col1)

)

  engine = innodb;

insert into MY_TABLE(col2)

select seq

from seq_1_to_17;

Count number of rows and max value:

select count(*), max(col1) from MY_TABLE;

-- 17, 17

This fine. Now, what is the value of the auto increment?

select AUTO_INCREMENT

from information_schema.TABLES

where TABLE_NAME = 'MY_TABLE';

-- 32

– Not OK, expected 18 not the next power of two.

Lets try again with a different number of rows from the sequence storage engine.

drop table if exists MY_TABLE cascade;

create table MY_TABLE (

  col1 smallint not null auto_increment,

  col2 smallint not null,

  primary key (col1)

)

  engine = innodb;

insert into MY_TABLE(col2)

select seq

from seq_1_to_20000 seq;

select count(*), max(col1) from MY_TABLE;

-- 20000,20000

-- OK

select AUTO_INCREMENT

from information_schema.TABLES

where TABLE_NAME = 'MY_TABLE';

-- 32768

-- Not OK, expected 20,001 not the next power of two.

In the attached SQL script more examples.

Also, this effect appears when multiple tables of which one is a sequence engine table are joined.

When the target table is an Aria table the auto increment value behaves as expected. And, when running the queries on a MariaDB 10.6.18 server the auto increment value behaves as expected.