MySQL Bugs: #35877: Update .. WHERE with function, constraint violation, crash

Bug #35877	Update .. WHERE with function, constraint violation, crash
Submitted:	7 Apr 2008 12:11	Modified:	12 Nov 2009 20:54
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any

Description:
Slightly edited protocol:
SET SESSION sql_mode = '';
CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)
ENGINE = MyISAM;
CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)
ENGINE = MyISAM;
INSERT INTO t1_aux VALUES (1,1);
COMMIT;
CREATE FUNCTION f1_two_inserts() returns INTEGER
BEGIN
INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
RETURN 1;
END//
UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts();
ERROR HY000: Lost connection to MySQL server during query

1. Using InnoDB instead of MyISAM does not prevent
   the crash.
2. The crash does not happen in case of
   mysql-5.0-main ChangeSet@1.2610, 2008-04-01

My environment:
- mysql-5.1-main ChangeSet@1.2561, 2008-04-01
  BUILD/compile-pentium-debug-max
- mysql-6.0-main ChangeSet@1.2626, 2008-04-03
  BUILD/compile-pentium-debug-max
- Intel Core2Duo (64 Bit)
- SuSE Linux 10.3 (64 Bit)

How to repeat:
Please use the attached testscript.

testscript

Attachment: ml002.test (application/octet-stream, text), 993 bytes.

Stack back trace

Attachment: stacktrace (application/octet-stream, text), 4.05 KiB.

IMHO using a function within a WHERE qualification of
an UPDATE + the function itself modifies data is a very
rare case. The only case I can in the moment imagine
is that somebody has a DWH application. The updated table
is used for collecting some temporary results ("used on the
way to get the final report") and the function is
- much more complicated
- has to serve two purposes
  - bring some aggregate tables up to date
  - process aggregate tables for getting the return value
--> I4
I also guess that many users could find a different
solution - the function within the WHERE qualification-
does not modify data - which fits to their application
needs. --> W3

Backtrace:
(gdb) bt
#0  0xffffe410 in __kernel_vsyscall ()
#1  0xb7d56875 in raise () from /lib/tls/i686/cmov/libc.so.6
#2  0xb7d58201 in abort () from /lib/tls/i686/cmov/libc.so.6
#3  0xb7d4fb6e in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
#4  0x08246815 in Diagnostics_area::set_ok_status (this=0x8dd6678, 
    thd=0x8dd58e0, affected_rows_arg=39560943763462, last_insert_id_arg=0, 
    message_arg=0xb50de1f8 "Rows matched: 0  Changed: 0  Warnings: 0")
    at sql_class.cc:397
#5  0x082fa9da in mysql_update (thd=0x8dd58e0, table_list=0x8e217d0, 
    fields=@0x8dd6cdc, values=@0x8dd6f28, conds=0x8e1a410, order_num=0, 
    order=0x0, limit=13046331721333608305, handle_duplicates=DUP_ERROR, 
    ignore=false) at sql_class.h:2146
#6  0x0826e719 in mysql_execute_command (thd=0x8dd58e0) at sql_parse.cc:2827
#7  0x0827479c in mysql_parse (thd=0x8dd58e0, 
    inBuf=0x8e207b0 "UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()", 
    length=52, found_semicolon=0xb50deeec) at sql_parse.cc:5617
#8  0x08275b49 in dispatch_command (command=COM_QUERY, thd=0x8dd58e0, 
    packet=0x8e0e0f1 "UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()", 
    packet_length=52) at sql_parse.cc:1121
#9  0x08276cf2 in do_command (thd=0x8dd58e0) at sql_parse.cc:781
#10 0x08265098 in handle_one_connection (arg=0x8dd58e0) at sql_connect.cc:1106
#11 0xb7eeb46b in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#12 0xb7dff6de in clone () from /lib/tls/i686/cmov/libc.so.6

The assert was added as part of Bug#12713

Analysis of this bug:
The f1_two_inserts function tries to insert NULL into a not null column and fails.
This error isn't correctly propagated to the caller statement and causes
failed assertion at setting return code of whole statement for the second time.
See also bug#27563.

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/74172

2777 Jon Olav Hauglid	2009-05-15
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
      
      Unable to reproduce crash with current version of mysql-6.0-runtime.
      Test case for MyISAM/InnoDB based on the bug rapport added to sp.test.

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/75058

2781 Jon Olav Hauglid	2009-05-27
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
      
      Unable to reproduce crash with current version of mysql-6.0-runtime.
      Test case for MyISAM/InnoDB based on the bug rapport added to sp.test.

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/79502

2833 Jon Olav Hauglid	2009-07-29
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
            
      Unable to reproduce crash with current version of mysql-azalea.
      Test case for MyISAM/InnoDB based on the bug rapport added to 
      sp_trans.test.

Pushed to azalea-bugfixing.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:iggy@mysql.com-20090731204544-7nio1afvg0dmzs7g) (merge vers: 5.4.4-alpha) (pib:11)

Bug no longer present apparently; changes to test case only. No changelog entry needed.

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/86554

2910 Jon Olav Hauglid	2009-10-12
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
      
      Unable to reproduce crash with current version of the 5.5.0 codebase.
      Test case for MyISAM/InnoDB based on the bug rapport added to 
      sp_trans.test.
      
      Backport of revno: 2617.65.9.

Pushed to mysql-next-mr-runtime (version 5.5.0).

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)

Changes to test case only. No changelog entry needed.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)

Changes to test case only. No changelog entry needed.