MySQL Bugs: #48421: Failure of executing insert into table select ... from .... group by ..

Bug #48421	Failure of executing insert into table select ... from .... group by ..
Submitted:	29 Oct 2009 19:38	Modified:	11 Feb 2018 11:03
Reporter:	Bing Lu	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	insert into..select..group by.. auto_increment

Description:
I have created a table with an AUTO_INCREMENT column.  I insert rows into that table using a single session. But I found out that the auto_increment column
skips on the generated values.

How to repeat:
CREATE TABLE t (name VARCHAR(2), 
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)
engine=innodb;
INSERT INTO t (name) VALUES ('x');
INSERT INTO t (name) VALUES ('y');
INSERT INTO t (name) (SELECT s.name FROM t s WHERE s.name='x' GROUP BY s.name);
INSERT INTO t (name) (SELECT s.name FROM t s WHERE s.name='x' GROUP BY s.name);
INSERT INTO t (name) (SELECT s.name FROM t s WHERE s.name='x' GROUP BY s.name);
INSERT INTO t (name) (SELECT s.name FROM t s WHERE s.name='x' GROUP BY s.name);
SELECT * FROM t; 

Then I get the following
name    id
x    1
y    2
x    3
x    4
x    5
x    7
x    8
x    9
x    10
x    14
x    15
x    16
x    17
x    18
x    19
x    20
x    21

I noticed that id jumps from 5 to 7, and then 10 to 14.

There never was a promise that auto_increment would guarantee continuous values (without 'holes').

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
.. only says 
"The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:"

Check the server variable 'innodb_autoinc_lock_mode' here
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_m...
and here:
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Peter
(a non-MySQL person who earlier reported the same 'not-a-bug' as a 'bug')

I am using version 5.1.30, and I have set innodb_autoinc_lock_mode = 1.
I still observe the issue I reported. Isn't this a bug?

Another problem I noticed here is that the following statement
  "SELECT s.name FROM t s WHERE s.name='x' GROUP BY s.name"
returns a single row.

But the following statement
 "INSERT INTO t (name) (SELECT s.name FROM t s WHERE s.name='x' GROUP BY s.name);"
results in many rows inserted into table t.

Why the same select statement results in different number of rows when
running stand alone from when embedded in the insert statement

After I have set innodb_autoinc_lock_mode = 0, I don't observe the skipping values in the id column. But the problem of extra rows inserted into t still exists.

Please, check if your problem with different number of rows inserted is repeatable with recent MySQL 5.1.40.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Posted by developer:
 
Closing since bug has been suspended for 8 years.
The number of rows inserted seems correct on 8.0.