Bug #40745 Error during WHERE clause calculation in UPDATE leads to an assertion failure
Submitted: 14 Nov 2008 14:41 Modified: 9 Dec 2008 0:27
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1/6.0.8-debug OS:Linux
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: regression
Triage: Triaged: D1 (Critical)

[14 Nov 2008 14:41] Dmitry Lenev
Description:
Error during WHERE clause calculation in UPDATE which happens then one tries to use recursive function in this clause leads to an assertion failure. Here is the stack-trace for this assertion:

#0  0xffffe410 in __kernel_vsyscall ()
#1  0xb7ce28a0 in raise () from /lib/libc.so.6
#2  0xb7ce3fc3 in abort () from /lib/libc.so.6
#3  0xb7cdbfeb in __assert_fail () from /lib/libc.so.6
#4  0x082e0c9c in Diagnostics_area::set_ok_status (this=0x96748d8, 
    thd=0x9673b28, affected_rows_arg=0, last_insert_id_arg=0, 
    message_arg=0xaf912f5c "Rows matched: 0  Changed: 0  Warnings: 0")
    at sql_class.cc:412
#5  0x0821eb13 in my_ok (thd=0x9673b28, affected_rows=0, id=0, 
    message=0xaf912f5c "Rows matched: 0  Changed: 0  Warnings: 0")
    at sql_class.h:2423
#6  0x083b84e9 in mysql_update (thd=0x9673b28, table_list=0x96cf5d0, 
    fields=@0x96750e4, values=@0x967530c, conds=0x96cfc08, order_num=0, 
    order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, 
    ignore=false) at sql_update.cc:822
#7  0x0830d2cd in mysql_execute_command (thd=0x9673b28) at sql_parse.cc:2868
#8  0x08313129 in mysql_parse (thd=0x9673b28, 
    inBuf=0x96cf2f0 "update t1 set i= 3 where f1(3)", length=30, 
    found_semicolon=0xaf913f30) at sql_parse.cc:5587
#9  0x08314139 in dispatch_command (command=COM_QUERY, thd=0x9673b28, 
    packet=0x96c0759 "update t1 set i= 3 where f1(3)", packet_length=30)
    at sql_parse.cc:1002
#10 0x0831546d in do_command (thd=0x9673b28) at sql_parse.cc:689
#11 0x0830272d in handle_one_connection (arg=0x9673b28) at sql_connect.cc:1153
#12 0xb7f032ab in start_thread () from /lib/libpthread.so.0
#13 0xb7d7858e in clone () from /lib/libc.so.6

How to repeat:
# Script for mysqltest utility which demonstrates problem
create table t1 (i int);
insert into t1 values (1);
delimiter |;
create function f1 (i int) returns int
begin
  if i <> 0 then
    return f1(i-1) + 1;
  else
    return 0;
  end if;
end|
delimiter ;|
# The statement below is expected to produce error but instead
# aborts server on assertion
--error ER_SP_NO_RECURSION
update t1 set i= 3 where f1(3);
[14 Nov 2008 15:18] Miguel Solorzano
Thank you for the bug report. Repeatable on 6.0.9(bzr)/5.1(bzr) and not repeatable on 5.1.22 released version on Windows.

081114 12:50:51 [Note] 6.0/libexec/mysqld: ready for connections.
Version: '6.0.9-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: sql_class.cc:415: void Diagnostics_area::set_ok_status(THD*, ha_rows, ulonglong, const char*): Assertion `! is_set()' failed.
081114 12:53:04 - mysqld got signal 6 ;

<cut>

6.0/libexec/mysqld(my_print_stacktrace+0x32)[0xdbde42]
6.0/libexec/mysqld(handle_segfault+0x2a6)[0x7389a7]
/lib/libpthread.so.0[0x7f512d3300f0]
/lib/libc.so.6(gsignal+0x35)[0x7f512bfcefd5]
/lib/libc.so.6(abort+0x183)[0x7f512bfd0b43]
/lib/libc.so.6(__assert_fail+0xe9)[0x7f512bfc7d49]
6.0/libexec/mysqld(_ZN16Diagnostics_area13set_ok_statusEP3THDyyPKc+0x5a)[0x71f3a0]
6.0/libexec/mysqld(_Z5my_okP3THDyyPKc+0x3b)[0x64f220]
6.0/libexec/mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_ordery15enum_duplicatesb+0x1fe6)[0x801ba1]
6.0/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x2b22)[0x74d823]
6.0/libexec/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x273)[0x7531c3]
6.0/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xa8c)[0x753de3]
6.0/libexec/mysqld(_Z10do_commandP3THD+0x224)[0x755378]
6.0/libexec/mysqld(handle_one_connection+0x11c)[0x7427c3]
/lib/libpthread.so.0[0x7f512d3283ea]
/lib/libc.so.6(clone+0x6d)[0x7f512c082c6d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2b962d0 = update t1 set i= 3 where f1(3)
thd->thread_id=1
thd->killed=NOT_KILLED
[26 Nov 2008 19:33] 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/59972

2708 Gleb Shchepa	2008-11-26
      Bug #40745: Error during WHERE clause calculation in UPDATE
                  leads to an assertion failure
      
      Any run-time error in stored function (like recursive function
      call or update of table that is already updating by statement
      which invoked this stored function etc.) that was used in some
      expression of the single-table UPDATE statement caused an
      assertion failure.
      Multiple-table UPDATE (as well as INSERT and both single- and
      multiple-table DELETE) are not affected.
[27 Nov 2008 18:22] 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/60098

2708 Gleb Shchepa	2008-11-27
      Bug #40745: Error during WHERE clause calculation in UPDATE
                  leads to an assertion failure
      
      Any run-time error in stored function (like recursive function
      call or update of table that is already updating by statement
      which invoked this stored function etc.) that was used in some
      expression of the single-table UPDATE statement caused an
      assertion failure.
      Multiple-table UPDATE (as well as INSERT and both single- and
      multiple-table DELETE) are not affected.
[28 Nov 2008 17:38] 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/60207

2721 Gleb Shchepa	2008-11-28
      Bug #40745: Error during WHERE clause calculation in UPDATE
                  leads to an assertion failure
      
      Any run-time error in stored function (like recursive function
      call or update of table that is already updating by statement
      which invoked this stored function etc.) that was used in some
      expression of the single-table UPDATE statement caused an
      assertion failure.
      Multiple-table UPDATE (as well as INSERT and both single- and
      multiple-table DELETE) are not affected.
[8 Dec 2008 10:21] Bugs System
Pushed into 5.1.31  (revid:gshchepa@mysql.com-20081128163607-ctt0eed8a578jmii) (version source revid:ingo.struewing@sun.com-20081128205552-dx30wv0qxr4exozn) (pib:5)
[8 Dec 2008 11:31] Bugs System
Pushed into 6.0.9-alpha  (revid:gshchepa@mysql.com-20081128163607-ctt0eed8a578jmii) (version source revid:ingo.struewing@sun.com-20081129113057-4ytvc66vqu2tifmj) (pib:5)
[9 Dec 2008 0:27] Paul Dubois
Noted in 5.1.31, 6.0.9 changelogs.

For single-table UPDATE statements, an assertion failure resulted
from a runtime error in a stored function (such as a recursive
function call or an attempt to update the same table as in the UPDATE
statement).
[19 Jan 2009 11:30] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:07] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)