MySQL Bugs: #29095: Insert into a select from a join b using(c) order by b.auto

Bug #29095	Insert into a select from a join b using(c) order by b.auto_id desc limit 1 fail
Submitted:	14 Jun 2007 3:21	Modified:	4 Jul 2007 1:25
Reporter:	J D	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.44-BK, 5.0.27 community, 5.0.41 Linux	OS:	Any (Windows/Linux same...)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	insert, join, limit, order, SELECT

Description:
insert into `a` (count) select a.count+1 from `a` join `b` using(c) order by `b`.auto_id desc limit 1 fails to yeild correct results....

This does work on 4.0.18.
I did test this on a couple versions of 5.0, and we do not understand why this does not insert the values returned from the select, when the select is executed alone.

How to repeat:
create table a ( ID int(11) NOT NULL auto_increment
, count int(11)
, join_id int(11)
, PRIMARY KEY( ID )
);
create table b ( join_id int(11)
, mult int(11)
, key2 int(11)
, PRIMARY KEY( join_id )
, INDEX `key2key` (key2)
);
insert into b (mult,key2) values ( 10, 1 );

#select * from b; 

#THE_INSERT_STATEMENT
insert into a (count,join_id)
select (IFNULL(count,0) + ( mult )), join_id
from a
right join b using (join_id)
where key2=1
order by ID desc limit 1;

select * from a;

after running THE_INSERT_STATEMENT once, table a has 1 row which has a count of 30.
after running THE_INSERT_STATEMENT a second time, table a has 2 rows, the first is 10, the second is 20....

at the third time this statement is executed, 3 rows exist... ID=1,count=10; ID=2, count=20; ID=3,count=20 

The third is not incremented.

I'm ordering by ID descending with a limit of one, so the row selected should have been ID=2, count=20, adding 10, and inserting 30.....

If I remove the 'insert into a ()' from THE_INSERT_STATEMENT, after having a few rows, the result returned is `mult` more than `count` in the last ID.  This would be (30, 0) for this state... but when the insert happens, (30,0) is not inserted, but rather (20,0) for some reason is continuously inserted.

(Already noticed a typo - after the first execution there is 1 row with a count of 10)

Thank you for a problem report. Verified with latest 5.0.44-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table a ( ID int(11) NOT NULL auto_increment
    -> , count int(11)
    -> , join_id int(11)
    -> , PRIMARY KEY( ID )
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table b ( join_id int(11)
    -> , mult int(11)
    -> , key2 int(11)
    -> , PRIMARY KEY( join_id )
    -> , INDEX `key2key` (key2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into b (mult,key2) values ( 10, 1 );
Query OK, 1 row affected (0.00 sec)

mysql> select * from b;
+---------+------+------+
| join_id | mult | key2 |
+---------+------+------+
|       0 |   10 |    1 |
+---------+------+------+
1 row in set (0.00 sec)

mysql> insert into a (count,join_id)
    -> select (IFNULL(count,0) + ( mult )), join_id
    -> from a
    -> right join b using (join_id)
    -> where key2=1
    -> order by ID desc limit 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+----+-------+---------+
| ID | count | join_id |
+----+-------+---------+
|  1 |    10 |       0 |
+----+-------+---------+
1 row in set (0.00 sec)

mysql> insert into a (count,join_id) select (IFNULL(count,0) + ( mult )), join_
id from a right join b using (join_id) where key2=1 order by ID desc limit 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+----+-------+---------+
| ID | count | join_id |
+----+-------+---------+
|  1 |    10 |       0 |
|  2 |    20 |       0 |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> insert into a (count,join_id) select (IFNULL(count,0) + ( mult )), join_
id from a right join b using (join_id) where key2=1 order by ID desc limit 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+----+-------+---------+
| ID | count | join_id |
+----+-------+---------+
|  1 |    10 |       0 |
|  2 |    20 |       0 |
|  3 |    20 |       0 |
+----+-------+---------+
3 rows in set (0.00 sec)

mysql> select (IFNULL(count,0) + ( mult )), join_id from a right join b using (
join_id) where key2=1 order by ID desc limit 1;
+------------------------------+---------+
| (IFNULL(count,0) + ( mult )) | join_id |
+------------------------------+---------+
|                           30 |       0 |
+------------------------------+---------+
1 row in set (0.00 sec)

mysql> insert into a (count,join_id) select (IFNULL(count,0) + ( mult )), join_
id from a right join b using (join_id) where key2=1 order by ID desc limit 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select (IFNULL(count,0) + ( mult )), join_id from a right join b using (
join_id) where key2=1 order by ID desc limit 1;
+------------------------------+---------+
| (IFNULL(count,0) + ( mult )) | join_id |
+------------------------------+---------+
|                           30 |       0 |
+------------------------------+---------+
1 row in set (0.01 sec)

mysql> select * from a;
+----+-------+---------+
| ID | count | join_id |
+----+-------+---------+
|  1 |    10 |       0 |
|  2 |    20 |       0 |
|  3 |    20 |       0 |
|  4 |    20 |       0 |
+----+-------+---------+
4 rows in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29436

ChangeSet@1.2501, 2007-06-23 02:11:58+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29095.
  INSERT into table from SELECT from the same table
  with ORDER BY and LIMIT was inserting other data
  than sole SELECT ... ORDER BY ... LIMIT returns.
  
  When INSERT from SELECT from the same table was made
  via temporary table, the LIMIT value was pushed to
  TMP_TABLE_PARAM::end_write_records ignoring the fact
  that resulting data set have to be ordered.
  
  The `test(select_options & OPTION_BUFFER_RESULT)'
  condition has been removed from the calculation of
  pushed LIMIT value:
  
    /*                         
      Pushing LIMIT to the temporary table creation is not applicable
      when there is ORDER BY or GROUP BY or there is no GROUP BY, but
      there are aggregate functions, because in all these cases we need
      all result rows.
    */           
    ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order ||
                              test(select_options & OPTION_BUFFER_RESULT))...
  
  The OPTION_BUFFER_RESULT flag in this context usually identify,
  that temporary table mast be created, because the result of SELECT
  have to be inserted into the same table:
  
      /* Using same table for INSERT and SELECT */
      lex->current_select->options|= OPTION_BUFFER_RESULT;
      lex->current_select->join->select_options|= OPTION_BUFFER_RESULT;
  
  NOTE: Removal of the `test(select_options & OPTION_BUFFER_RESULT)'
  condition doesn't affect the case when SQL_BUFFER_RESULT is forced by
  client, because we don't have to push LIMIT value too when SELECT have
  ORDER BY clause (`order == 0' is false), but skip_sort_order is false.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29442

ChangeSet@1.2501, 2007-06-23 13:49:43+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29095.
  INSERT into table from SELECT from the same table
  with ORDER BY and LIMIT was inserting other data
  than sole SELECT ... ORDER BY ... LIMIT returns.
  
  One part of the patch for bug #9676 improperly pushed
  LIMIT to temporary table in a presence of GROUP BY condition.
  That part has been removed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29453

ChangeSet@1.2501, 2007-06-24 01:20:14+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29095.
  INSERT into table from SELECT from the same table
  with ORDER BY and LIMIT was inserting other data
  than sole SELECT ... ORDER BY ... LIMIT returns.
  
  One part of the patch for bug #9676 improperly pushed
  LIMIT to temporary table in the presence of the ORDER BY
  clause.
  That part has been removed.

Pushed into 5.1.21-beta

Pushed into 5.0.46

Noted in 5.0.46, 5.1.21 changelogs.

If an INSERT INTO ... SELECT statement inserted into the same table
that the SELECT retrieved from, and the SELECT included ORDER BY and
LIMIT clauses, different data was inserted than the data produced by
the SELECT executed by itself.

See bug: http://bugs.mysql.com/bug.php?id=36559.