AUTO_INCREMENT behaviour is incorrect after set AUTO_ID_CACHE to 1

[tiancaiamao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

I'm testing #38442 and find this.

create table t1 (a int not null auto_increment,b int, primary key (a)) AUTO_ID_CACHE 1 auto_increment=3;
insert into t1 values (1,1),(NULL,3),(NULL,4);
select * from t1;

2. What did you expect to see? (Required)

mysql> select * from t1;                                                                                                                                                                                                                                                          
+---+------+                                                                                                                                                                                                                                                                      
| a | b    |                                                                                                                                                                                                                                                                      
+---+------+                                                                                                                                                                                                                                                         
| 1 |    1 |                                                                                                                                                                                                                                                                     
| 3 |    3 |                                                                                                                                                                                                                                                                     
| 4 |    4 |                                                                                                                                                                                                                                                                     
+---+------+                                                                                                                                                                                                                                             
3 rows in set (0.00 sec) 

3. What did you see instead (Required)

mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 4 |    3 |
| 5 |    4 |
+---+------+
3 rows in set (0.00 sec)

AUTO_ID_CACHE 1 makes the result different, that's not expected!

4. What is your TiDB version? (Required)

master

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
--------------------+
| tidb_version()

                    |
+----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
--------------------+
| Release Version: v6.4.0-alpha-155-gb9029f19b
Edition: Community
Git Commit Hash: b9029f19b8a18c52d0c9f91a2647798a51cd1e21
Git Branch: master
UTC Build Time: 2022-10-28 09:24:00
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
--------------------+
1 row in set (0.00 sec)

[tiancaiamao]

This one is fixed by #38809

A related issue is #982