last_insert_id() doesn't work with auto_random

[kolbe]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table t(a bigint auto_random(3) primary key, b varchar(10));
insert into t () values ();
select last_insert_id();

2. What did you expect to see?

last_insert_id() should show the last-generated auto-random value.

3. What did you see instead?

MySQL [test]> insert into t () values ();
Query OK, 1 row affected (0.005 sec)

MySQL [test]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.000 sec)

MySQL [test]> select * from t;
+---------------------+------+
| a                   | b    |
+---------------------+------+
| 6917529027641081857 | NULL |
+---------------------+------+
1 row in set (0.001 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v4.0.0-beta-270-g5c68d537f
Git Commit Hash: 5c68d537f43ae2f2b6678a6ba70c3d03640e1500
Git Branch: master
UTC Build Time: 2020-03-03 02:17:41
GoVersion: go1.13.7
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[kolbe]

It appears that it's possible to have an auto_random and auto_increment column on the same table. In that case, last_insert_id() will give the value allocated to the auto_increment column.

Since auto_random is meant as a replacement for auto_increment to avoid hotspots, I think it would make sense for last_insert_id() to return the last auto-random value.

If there is serious concern about confusing behavior in the case of a table that has both auto_increment and auto_random columns, we'll have to consider introducing some configuration option, or maybe even DDL that would mark which column in the table should affect last_insert_id().

[mightyguava]

autoincrement and autorandom should probably be mutually exclusive at a table level

[bb7133]

It appears that it's possible to have an auto_random and auto_increment column on the same table. In that case, last_insert_id() will give the value allocated to the auto_increment column.

Since auto_random is meant as a replacement for auto_increment to avoid hotspots, I think it would make sense for last_insert_id() to return the last auto-random value.

If there is serious concern about confusing behavior in the case of a table that has both auto_increment and auto_random columns, we'll have to consider introducing some configuration option, or maybe even DDL that would mark which column in the table should affect last_insert_id().

autoincrement and autorandom should probably be mutually exclusive at a table level

@mightyguava You're right, this should be fixed too. Thank you @kolbe

[hooopo]

the same issue with sequence primary key:

mysql> CREATE SEQUENCE `orders_seq` start with 1000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `orders` (
    ->   `id` bigint(20) NOT NULL DEFAULT nextval(`orders_seq`),
    ->   `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
    ->   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into orders(name) values ('a');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.01 sec)

mysql> select lastval(orders_seq);
+---------------------+
| lastval(orders_seq) |
+---------------------+
|                1000 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from orders;
+------+------+
| id   | name |
+------+------+
| 1000 | a    |
+------+------+
1 row in set (0.00 sec)