Bug #46374 crash, INSERT INTO t1 uses function, function modifies t1
Submitted: 24 Jul 2009 16:29 Modified: 7 Mar 2010 2:05
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S6 (Debug Builds)
Version:5.4 OS:Any
Assigned to: Magne Mæhre CPU Architecture:Any
Tags: crash, debug, FUNCTION, regression

[24 Jul 2009 16:29] Matthias Leich
Description:
My backtrace:
-------------
#0  0x00007fcc500b7ce6 in pthread_kill () from /lib64/libpthread.so.0
#0  0x00007fcc500b7ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b5466c in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006ecf81 in handle_segfault (sig=6) at mysqld.cc:2718
#3  <signal handler called>
#4  0x00007fcc4efb35c5 in raise () from /lib64/libc.so.6
#5  0x00007fcc4efb4bb3 in abort () from /lib64/libc.so.6
#6  0x00007fcc4efac1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000007bd528 in Diagnostics_area::set_ok_status (this=0x16cc4b8, thd=0x16c9b48, affected_rows_arg=0, last_insert_id_arg=0, message_arg=0x40b77060 "Records: 0  Duplicates: 0  Warnings: 0") at sql_error.cc:358
#8  0x00000000006005ce in my_ok (thd=0x16c9b48, affected_rows=0, id=0, message=0x40b77060 "Records: 0  Duplicates: 0  Warnings: 0") at sql_class.h:2490
#9  0x00000000007a82f6 in select_insert::send_eof (this=0x17adf88) at sql_insert.cc:3346
#10 0x00000000007889b7 in do_select (join=0x17dfaa0, fields=0x16cba28, table=0x0, procedure=0x0) at sql_select.cc:15859
#11 0x00000000007a2fe8 in JOIN::exec (this=0x17dfaa0) at sql_select.cc:2900
#12 0x000000000079d9bc in mysql_select (thd=0x16c9b48, rref_pointer_array=0x16cbb08, tables=0x1606f60, wild_num=1, fields=@0x16cba28, conds=0x16a8080, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=3489942016, result=0x17adf88, unit=0x16cb4b8, select_lex=0x16cb920) at sql_select.cc:3091
#13 0x00000000007a3306 in handle_select (thd=0x16c9b48, lex=0x16cb418, result=0x17adf88, setup_tables_done_option=1073741824) at sql_select.cc:306
#14 0x0000000000701e2d in mysql_execute_command (thd=0x16c9b48) at sql_parse.cc:3277
#15 0x000000000070743e in mysql_parse (thd=0x16c9b48, inBuf=0x16054c0 "INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5", length=78, found_semicolon=0x40b78f30) at sql_parse.cc:5942
#16 0x000000000070805c in dispatch_command (command=COM_QUERY, thd=0x16c9b48, packet=0x1601469 "INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5", packet_length=78) at sql_parse.cc:1061
#17 0x000000000070953a in do_command (thd=0x16c9b48) at sql_parse.cc:743
#18 0x00000000006f68b3 in handle_one_connection (arg=0x16c9b48) at sql_connect.cc:1158
#19 0x00007fcc500b3040 in start_thread () from /lib64/libpthread.so.0
#20 0x00007fcc4f05408d in clone () from /lib64/libc.so.6
#21 0x0000000000000000 in ?? ()

Simplified test:
----------------
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 ( f2 INTEGER, f3 INTEGER );
INSERT INTO t1 VALUES  ( 1, 1 );

delimiter |; CREATE FUNCTION func_1 () RETURNS INTEGER BEGIN INSERT INTO t1 SELECT * FROM t1 ; RETURN 1 ; END| delimiter ;|
# Here I get the crash.
INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5;

# Cleanup
DROP FUNCTION func_1;
DROP TABLE t1;

IMHO the fact that the function modifies the table t1
where the INSERT happens is essential. The SELECT
parts of the function/INSERT SELECT can work on any table.

This bug is IMHO serious even if it happens
only on debug builds because debug builds are good for
testing and using functions which do not modify tables
is a significant reduction of checked functionality.

In mysql-5.1-bugteam June 2009 compiled without
debug I get
   ERROR HY000:
   Can't update table 't1' in stored function
   /trigger because it is already used by
   statement which invoked this stored function/trigger.
which is IMHO acceptable.

My environment:
---------------
- MySQL Azalea (5.4) 2009-07-23
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above
[25 Jul 2009 9:05] Philip Stoev
5.1-bugteam debug-compiled does not hit this assertion. Therefore, this is a genuine regression.
[19 Aug 2009 11:24] Dmitry Lenev
Preliminary investigation has shown that in case of 5.4 we also report error which one gets in this case from 5.1. But on later stages of SELECT handling this error is ignored thus my_ok() is called and this causes assertion failure.
So IMO this bug falls into SELECT-execution realm rather than in the area of stored routines execution.
[25 Aug 2009 12:36] 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/81502

2877 Magne Mahre	2009-08-25
      Bug #46374 crash, INSERT INTO t1 uses function, function modifies t1
      
      An error occuring in the execution of a stored procedure, called
      from do_select is masked, since the error condition is not
      propagated back to the caller (join->conds->val_int() returns
      a result value, and not an error code)
      
      An explicit check was added to see if the thd error code has been
      set, and if so, we don't mark the execution as ok.
[9 Sep 2009 14:19] 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/82839

2813 Magne Mahre	2009-09-09
      Bug #46374 crash, INSERT INTO t1 uses function, function modifies t1
            
      An error occuring in the execution of a stored procedure, called
      from do_select is masked, since the error condition is not
      propagated back to the caller (join->conds->val_int() returns
      a result value, and not an error code)
            
      An explicit check was added to see if the thd error code has been
      set, and if so, the loop status is set to the error state.
[20 Sep 2009 14:44] 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/83809

2826 Magne Mahre	2009-09-20
      Bug #46374 crash, INSERT INTO t1 uses function, function modifies t1
                  
      An error occuring in the execution of a stored procedure, called
      from do_select is masked, since the error condition is not
      propagated back to the caller (join->conds->val_int() returns
      a result value, and not an error code)
                  
      An explicit check was added to see if the thd error code has been
      set, and if so, the loop status is set to the error state.
[30 Sep 2009 8:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)
[1 Oct 2009 0:46] Paul DuBois
Noted in 6.0.14 changelog.

If INSERT INTO tbl_name invoked a stored function that modified
tbl_name, the server crashed.

Setting report to NDI pending push into 5.4.x.
[27 Oct 2009 21:43] Konstantin Osipov
Repeatable in 5.1-bugteam, please re-triage, we'll push there.
[10 Dec 2009 15:23] 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/93543

3032 Magne Mahre	2009-12-10
      Bug#46374 crash, INSERT INTO t1 uses function, function modifies t1
                        
      An error occuring in the execution of a stored procedure, called
      from do_select is masked, since the error condition is not
      propagated back to the caller (join->conds->val_int() returns
      a result value, and not an error code)
                        
      An explicit check was added to see if the thd error code has been
      set, and if so, the loop status is set to the error state.
      
      Backport from 6.0-codebase (revid: 2617.68.31)
[16 Feb 2010 16:44] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:54] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091211103945-198h3pt8w7ypk20u) (pib:16)
[17 Feb 2010 0:50] Paul DuBois
Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 11:00] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 2:05] Paul DuBois
Noted in 5.5.3 changelog.