Bug #44306 Assertion fail on duplicate key error in 'INSERT ... SELECT' statements
Submitted: 16 Apr 2009 6:58 Modified: 26 Jun 2009 2:11
Reporter: Yuan WANG Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.1.33, 5.1, 6.0 bzr OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Tags: regression
Triage: Triaged: D1 (Critical)

[16 Apr 2009 6:58] Yuan WANG
Description:
If 'INSERT INTO ... SELECT ...' statements failed, assertion error may occur.

How to repeat:
Execute the following SQL:

CREATE TABLE `t1` (
  `ID` int(11) NOT NULL,
  `NO` int(11) NOT NULL DEFAULT '0',
  `SEQ` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `t1$NO` (`SEQ`,`NO`)
) ENGINE=MyISAM;
insert into t1 values(0, 1, 1);
INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 2);

Then mysqld will crash, with the following stacktrace.

Program received signal SIGABRT, Aborted.
[Switching to Thread 0x44250950 (LWP 27290)]
0x00002b13e918ded5 in raise () from /lib/libc.so.6
(gdb) bt
#0  0x00002b13e918ded5 in raise () from /lib/libc.so.6
#1  0x00002b13e918f3f3 in abort () from /lib/libc.so.6
#2  0x00002b13e9186dc9 in __assert_fail () from /lib/libc.so.6
#3  0x00000000006f4820 in Diagnostics_area::set_ok_status (this=0x465d1a0, thd=0x465bb70, affected_rows_arg=0, last_insert_id_arg=0, 
    message_arg=0x4424df10 "Records: 1  Duplicates: 0  Warnings: 0") at sql_class.cc:436
#4  0x0000000000617c12 in my_ok (thd=0x465bb70, affected_rows=0, id=0, message=0x4424df10 "Records: 1  Duplicates: 0  Warnings: 0") at sql_class.h:2255
#5  0x00000000007be4b1 in select_insert::send_eof (this=0x46bcc70) at sql_insert.cc:3201
#6  0x00000000007b7223 in return_zero_rows (join=0x46bcd30, result=0x46bcc70, tables=0x46ba9c0, fields=@0x465db10, send_row=true, select_options=3490073088, 
    info=0xda4369 "No matching min/max row", having=0x0) at sql_select.cc:7083
#7  0x00000000007b7931 in JOIN::exec (this=0x46bcd30) at sql_select.cc:1725
#8  0x00000000007b3b4f in mysql_select (thd=0x465bb70, rref_pointer_array=0x465dbf8, tables=0x46ba9c0, wild_num=0, fields=@0x465db10, conds=0x46baf60, og_num=0, order=0x0, group=0x0, having=0x0, 
    proc_param=0x0, select_options=3489942016, result=0x46bcc70, unit=0x465d5c8, select_lex=0x465da00) at sql_select.cc:2378
#9  0x00000000007b9b1f in handle_select (thd=0x465bb70, lex=0x465d528, result=0x46bcc70, setup_tables_done_option=1073741824) at sql_select.cc:268
#10 0x0000000000723366 in mysql_execute_command (thd=0x465bb70) at sql_parse.cc:3142
#11 0x000000000072902c in mysql_parse (thd=0x465bb70, inBuf=0x46b9840 "INSERT INTO t1 (SEQ, NO) SELECT \"1\" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 2)", 
    length=109, found_semicolon=0x4424fef0) at sql_parse.cc:5831
#12 0x0000000000729e5a in dispatch_command (command=COM_QUERY, thd=0x465bb70, 
    packet=0x46a3b41 "INSERT INTO t1 (SEQ, NO) SELECT \"1\" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 2)", packet_length=109) at sql_parse.cc:1216
#13 0x000000000072b1a0 in do_command (thd=0x465bb70) at sql_parse.cc:857
#14 0x0000000000717bbc in handle_one_connection (arg=0x465bb70) at sql_connect.cc:1115
#15 0x00002b13e7f33fc7 in start_thread () from /lib/libpthread.so.0
#16 0x00002b13e922b5ad in clone () from /lib/libc.so.6
#17 0x0000000000000000 in ?? ()
[16 Apr 2009 8:08] Sveta Smirnova
Thank you for the report.

Verified as described.
[16 Apr 2009 8:09] Sveta Smirnova
Version 5.0 is not affected.
[21 Apr 2009 12:45] Bugs System
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/72582

2861 Martin Hansson	2009-04-21
      Bug#44306: Assertion fail on duplicate key error in 
      'INSERT ... SELECT' statements
      
      The code that produces result rows expected that a duplicate row
      error could not occur in INSERT ... SELECT statements with 
      unfulfilled WHERE conditions. This may happen, however, if the 
      SELECT list contains only aggregate functions.
      Fixed by checking if there is an error before trying to set 
      status to 'ok'.
     @ mysql-test/r/insert_select.result
        Bug#44306: Test result
     @ mysql-test/t/insert_select.test
        Bug#44306: Test case
     @ sql/sql_insert.cc
        Bug#44306: Fix
[27 Apr 2009 15:56] Bugs System
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/72843

2861 Martin Hansson	2009-04-27
      Bug#44306: Assertion fail on duplicate key error in 
      'INSERT ... SELECT' statements
            
      The code that produces result rows expected that a duplicate row
      error could not occur in INSERT ... SELECT statements with 
      unfulfilled WHERE conditions. This may happen, however, if the 
      SELECT list contains only aggregate functions.
      Fixed by checking if an error occured before trying to send EOF
      to the client.
     @ mysql-test/r/insert_select.result
        Bug#44306: Test result
     @ mysql-test/t/insert_select.test
        Bug#44306: Test case
     @ sql/sql_select.cc
        Bug#44306: Fix
[4 May 2009 12:45] Bugs System
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/73304

2877 Martin Hansson	2009-05-04
      Bug#44306: Assertion fail on duplicate key error in 
      'INSERT ... SELECT' statements
                  
      The code that produces result rows expected that a duplicate row
      error could not occur in INSERT ... SELECT statements with 
      unfulfilled WHERE conditions. This may happen, however, if the 
      SELECT list contains only aggregate functions.
      Fixed by checking if an error occured before trying to send EOF
      to the client.
     @ mysql-test/r/insert_select.result
        Bug#44306: Test result
     @ mysql-test/t/insert_select.test
        Bug#44306: Test case
     @ sql/sql_select.cc
        Bug#44306: Fix
[4 May 2009 12:56] Bugs System
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/73305

3254 Martin Hansson	2009-05-04 [merge]
      Bug#44306: Assertion fail on duplicate key error in 
      'INSERT ... SELECT' statements
      
      Merge
[4 May 2009 12:57] Bugs System
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/73306

2892 Martin Hansson	2009-05-04 [merge]
      Bug#44306: Assertion fail on duplicate key error in 
      'INSERT ... SELECT' statements
      
      Merge
[28 May 2009 8:18] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:mats@sun.com-20090511132802-nnkiyb2huih1tklz) (merge vers: 5.1.35) (pib:6)
[1 Jun 2009 20:20] Paul Dubois
Noted in 5.1.36 changelog.

Assertion failure could occur for duplicate-key errors in INSERT INTO
... SELECT statements. 

Setting report to NDI pending push into 6.0.x.
[2 Jun 2009 8:07] Martin Hansson
Correct. The failed assertion appeared instead of the error message.
[17 Jun 2009 19:25] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:mhansson@mysql.com-20090505082504-f9goof5x1eyrmlyi) (merge vers: 6.0.12-alpha) (pib:11)
[26 Jun 2009 2:11] Paul Dubois
Noted in 5.4.4 changelog.
[12 Aug 2009 22:35] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:53] Paul Dubois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 20:26] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.