Bug #1945 Crashing bug with bad User Variables in UPDATE ... ORDER BY ...
Submitted: 25 Nov 2003 6:10 Modified: 28 Nov 2003 3:22
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.17 OS:Linux (Linux)
Assigned to: Sergei Golubchik

[25 Nov 2003 6:10] Alexander Keremidarski
Description:
Using User variable is used in expression within ORDER BY clause of UPDATE causes mysqld to crash with following backtrace

(gdb) bt
#0  my_strcasecmp (s=0x0, t=0x8452b60 "b") at mf_casecnv.c:210
#1  0x08141f6c in find_item_in_list(Item*, List<Item>&) (find=0x8452b68, items=@0x841e5a8) at sql_base.cc:1839
#2  0x081561f0 in find_order_in_list (thd=0x84391d8, tables=0x841e5a8, order=0x8452ba8, fields=@0x409414f8, all_fields=@0x409414f8) at sql_select.cc:6720
#3  0x081562d6 in setup_order(THD*, st_table_list*, List<Item>&, List<Item>&, st_order*) (thd=0x84391d8, tables=0x40941508, fields=@0x409414f8, all_fields=@0x409414f8, order=0x8452ba8) at sql_select.cc:6748
#4  0x0815ee1f in mysql_update(THD*, st_table_list*, List<Item>&, List<Item>&, Item*, st_order*, unsigned long, enum_duplicates) (thd=0x84391d8, table_list=0x8452948, fields=@0x8439384, values=@0x8439490, conds=0x0, order=0x8452b40, limit=4294967295, handle_duplicates=DUP_ERROR) at sql_update.cc:92
#5  0x08127ac1 in mysql_execute_command() () at sql_parse.cc:1920
#6  0x0812aa4c in mysql_parse(THD*, char*, unsigned) (thd=0x84391d8, inBuf=0x8439308 "\001", length=44) at sql_parse.cc:2980
#7  0x0812526c in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x84391d8, packet=0x844a881 "UPDATE var_crash SET a=0 ORDER BY (a=@id), b", packet_length=44) at sql_parse.cc:1074
#8  0x08124c13 in do_command(THD*) (thd=0x84391d8) at sql_parse.cc:945
#9  0x08124187 in handle_one_connection (arg=0x841e5a8) at sql_parse.cc:726
#10 0x45255484 in start_thread () from /lib/tls/libpthread.so.0
#11 0x450a9147 in clone () from /lib/tls/libc.so.6

The problem seems to be that (a=@id) does not have a name field set as per Sergei (Golubchik not Yakubovich :) )

According to Serge Yakubovich who originaly reported this mysqld under FreeBSD does not crash just don't return expected result.

It is interesting to mention that crash happens only in case when ORDER BY clause contains column name reference without being into expression. See How-to-repeat section.

How to repeat:
CREATE TABLE var_crash(a INT, b INT);
SET @id=0;
 
UPDATE var_crash SET a=0 ORDER BY (a=@id), b; -- crash happens here

# However these statements work

UPDATE var_crash SET a=0 ORDER BY a=@id, 1;
UPDATE var_crash SET a=0 ORDER BY a=@id, a+1;
UPDATE var_crash SET a=0 ORDER BY a=@id, a!=@id;
UPDATE var_crash SET a=0 ORDER BY a=@id, 1, a+1, a!=@id;

# While this query crashes mysqld
UPDATE var_crash SET a=0 ORDER BY a=@id, 1, a+1, a!=@id, a;
[28 Nov 2003 3:22] Sergei Golubchik
fixed in 4.0.17