Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.4.2
-
None
-
version: 11.4.2-MariaDB-log
version_source_revision: 3fca5ed772fb75e3e57c507edef2985f8eba5b12
OS: AlmaLinux 9.4
Description
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.