last_insert_id() returns 0 when the value is explicitly added incompatible with MySQL 8.0

[yahonda]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Startup tiup playground

$ tiup playground

2. Connect to the TiDB

$ mysql --comments --host 127.0.0.1 --port 4000 -u root

3. Execute these statements

use test;
CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
);
insert into t values(12);
select last_insert_id();

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

last_insert_id() should return 13 as MySQL 8.0.32 does.

mysql> CREATE TABLE `t` (
    ->   `a` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`a`)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into t values(12);
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               13 |
+------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

mysql>

3. What did you see instead (Required)

last_insert_id() returns 0.

mysql> CREATE TABLE `t` (
    ->   `a` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`a`)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql>  insert into t values(12);
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql>

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.6.0
Edition: Community
Git Commit Hash: f4ca0821fb96a2bdd37d2fb97eb26c07fc58d4e4
Git Branch: heads/refs/tags/v6.6.0
UTC Build Time: 2023-02-17 14:39:52
GoVersion: go1.19.5
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

mysql>

[yahonda]

Looks like this behavior changed between MySQL 5.7 and MySQL 8.0.
MySQL 5.7.41 behaves as same as TiDB 6.6.0.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.41    |
+-----------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE `t` (
    ->   `a` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`a`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(12);
Query OK, 1 row affected (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql>

[yahonda]

So far TiDB is "TiDB is highly compatible with the MySQL 5.7 protocol and the common features and syntax of MySQL 5.7" I think this is not a bug, rather than a feature request for MySQL 8.0 Compatibility

https://docs.pingcap.com/tidb/dev/mysql-compatibility

TiDB is highly compatible with the MySQL 5.7 protocol and the common features and syntax of MySQL 5.7