Bug #15584 Server can not tell clients about generated keys
Submitted: 8 Dec 2005 12:25 Modified: 18 Jun 2013 12:59
Reporter: David Newcomb (OCA) Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: D5 (Feature request)

[8 Dec 2005 12:25] David Newcomb
Description:
On a k,v table (described in #15445) if you do:
 replace into ('val1'),('val2'),('val3');
The generated keys are 1,2,3

If you do:
 replace into (null,'val_g1'), (3,'val3'),(2,'val2'),(null,'val_g2');
I would execpt the generated keys to be 4,3,2,5 to behave in the same way as an insert would. The specifed key numbers have been previously generated by the database so the clients would not make up future keys.

This way the client code would be the same for group inserts and group replaces.

How to repeat:
See bug #15445
[17 Jun 2013 14:44] Matthew Lord
Hi David!

Thank you for the feature request, and for your help in making MySQL even better!

Can you please clarify what you would like to see in this example:

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

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(500) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> replace into foo (value) values ('val1'),('val2'),('val3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+-------+----+
| name | value | id |
+------+-------+----+
| NULL | val1  |  1 |
| NULL | val2  |  2 |
| NULL | val3  |  3 |
+------+-------+----+
3 rows in set (0.00 sec)

mysql> replace into foo (id, value) values (null,'val_g1'), (3,'val3'),(2,'val2'),(null,'val_g2');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
+------+--------+----+
5 rows in set (0.00 sec)

Thanks!
[17 Jun 2013 16:40] David Newcomb
Did you mean to create the table with "AUTO_INCREMENT=8"? If you did then I would expect "replace into foo (value) values ('val1'),('val2'),('val3');" to create the rows:

mysql> select * from foo;
+------+-------+----+
| name | value | id |
+------+-------+----+
| NULL | val1  |  8 |
| NULL | val2  |  9 |
| NULL | val3  | 10 |
+------+-------+----+
3 rows in set (0.00 sec)

and the "replace into foo (id, value) values (null,'val_g1'), (3,'val3'),(2,'val2'),(null,'val_g2');" to produce contents of:

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  8 |
| NULL | val2   |  9 |
| NULL | val3   | 10 |
| NULL | val_g1 | 11 |
| NULL | val3   |  3 |
| NULL | val2   |  2 |
| NULL | val_g2 | 12 |
+------+--------+----+

The first 3 records added carry on from auto_increment = 8 so 8,9,10 are automatically assigned.
The next replace uses null, so assign next auto_increment number 11. "(3,'val3')" and "(2,'val2')" don't have existing keys and so will be treated as inserts then "(null,'val_g2')" will pick the next auto_increment number of 12.

I think the original feature request was to help with JDBC's Statement.getGeneratedKeys() which returns a ResultSet of generated keys so the application programmer can match bulk insert's (or bulk replace's) with their corresponding record ids. Otherwise the application programmer has to do the inserts individually and then read back the last_insert_id, which is a significantly heavier operation when you have to do lots of them.

I think currently the last_insert_id only returns the last insert id which is a bit useless if you have just inserted 25 new records as the ids might not be consecutive.
[17 Jun 2013 16:50] Matthew Lord
I'm sorry for the confusion. The SHOW CREATE TABLE output was actually from *after* I had run the other statements, that's where the auto increment value of 8 came from.

Here's what I get when running them again (this time they are ordered here just as I ran them):

ysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(500) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> replace into foo (value) values ('val1'),('val2'),('val3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
| NULL | val1   |  8 |
| NULL | val2   |  9 |
| NULL | val3   | 10 |
+------+--------+----+
8 rows in set (0.00 sec)

mysql> replace into foo (id, value) values (null,'val_g1'),
    -> (3,'val3'),(2,'val2'),(null,'val_g2');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
| NULL | val1   |  8 |
| NULL | val2   |  9 |
| NULL | val3   | 10 |
| NULL | val_g1 | 11 |
| NULL | val_g2 | 12 |
+------+--------+----+
10 rows in set (0.00 sec)

Can you help me understand which part of that you would like to see changed in 5.7? It seems that this behavior is now what you wanted, correct? If not, please let me know.

Thanks again!
[17 Jun 2013 17:30] David Newcomb
"Here's what I get when running them again (this time they are ordered here just as I ran them):"

I'm sorry that doesn't make any sense. According to your last post, you create a table AI is still 8, replace 3 records and now you have 8 records in the table.

Now I am even more confused. Perhaps you could create your session again from table create through to question.
[17 Jun 2013 17:36] Matthew Lord
OK :)

Here you go:

mysql> truncate table foo;
Query OK, 0 rows affected (0.09 sec)

mysql> show create table foo;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(500) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> replace into foo (value) values ('val1'),('val2'),('val3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+-------+----+
| name | value | id |
+------+-------+----+
| NULL | val1  |  1 |
| NULL | val2  |  2 |
| NULL | val3  |  3 |
+------+-------+----+
3 rows in set (0.00 sec)

mysql> replace into foo (id, value) values (null,'val_g1'),
    -> (3,'val3'),(2,'val2'),(null,'val_g2');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+--------+----+
| name | value  | id |
+------+--------+----+
| NULL | val1   |  1 |
| NULL | val2   |  2 |
| NULL | val3   |  3 |
| NULL | val_g1 |  4 |
| NULL | val_g2 |  5 |
+------+--------+----+
5 rows in set (0.00 sec)
[18 Jun 2013 12:59] David Newcomb
Yes that works as expected and I believe it has always worked like that.
But this bug report is *not* about that! This report is "Server can not tell clients about generated keys"

#15445 is marked as "Unsupported" and this Feature request is to ask that you support it.