bug: After insert into select, the value of AUTO_INCREMENT is wrong

[davidshiz]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

mysql> insert into t1(id, uid, name) values(1, 0, ' ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(uid, name) values(0, ' ');
Query OK, 1 row affected (0.00 sec)

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=TIANMU AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t2(uid, name) select uid, name from t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=TIANMU AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Expected behavior

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------                                                             ----------------------------------------------------------------------------------------------------                                                             -+
| Table | Create Table                                                                                                                                                                                                                                                                                                             |
+-------+-------------------------------------------------------------------------------------------                                                             ----------------------------------------------------------------------------------------------------                                                             -+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------                                                             ----------------------------------------------------------------------------------------------------                                                             -+
1 row in set (0.00 sec)

How To Reproduce

create table t1 (
id int not null auto_increment,
name char(1) not null,
uid int not null,
primary key (id));
create table t2 (
id int not null auto_increment,
name char(1) not null,
uid int not null,
primary key (id));
insert into t1(id, uid, name) values(1, 0, ' ');
insert into t1(uid, name) values(0, ' ');
insert into t2(uid, name) select uid, name from t1;

Environment

root@ub01:/stonedb57/install/bin# ./mysqld --version
./mysqld  Ver 5.7.36-StoneDB-v1.0.3 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: d0665f9dc
        Last commit time: Date:   Wed Apr 12 10:52:39 2023 +0800
        Build time: Date: Wed Apr 12 19:37:30 CST 2023
root@ub01:/stonedb57/install/bin#

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

[RingsC]

Behavior of Tiamu is similar with that of MyISAM , therefore, so it may not be considered a bug.

[davidshiz]

in this scenario, result set error

create table t1 (
  id int not null auto_increment,
  name char(1) not null,
  uid int not null,
  primary key (id));

create table t2 (
  id int not null auto_increment,
  name char(1) not null,
  uid int not null,
  primary key (id));

insert into t1(id, uid, name) values(1, 0, ' ');
insert into t1(uid, name) values(0, ' ');

insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;

tianmu

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=TIANMU AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from t2;
+----+------+-----+
| id | name | uid |
+----+------+-----+
|  1 |      |   0 |
|  2 |      |   0 |
|  3 |      |   0 |
|  4 |      |   0 |
|  5 |      |   0 |
|  6 |      |   0 |
+----+------+-----+
6 rows in set (0.00 sec)

innodb

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(1) NOT NULL,
  `uid` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from t2;
+----+------+-----+
| id | name | uid |
+----+------+-----+
|  1 |      |   0 |
|  2 |      |   0 |
|  4 |      |   0 |
|  5 |      |   0 |
|  6 |      |   0 |
|  7 |      |   0 |
+----+------+-----+
6 rows in set (0.00 sec)