MySQL Bugs: #8216: server crash, with ifnull, subquery or rand(), aggregate, where and having

Bug #8216	server crash, with ifnull, subquery or rand(), aggregate, where and having
Submitted:	31 Jan 2005 18:03	Modified:	9 Feb 2005 11:10
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.9	OS:
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
The query in the how to repeat crashes a mysql 4.1.9 server. (just in case of: freebsd from ports )

the subquery returns more than one row, however in the query below, this is not recognized by the server.
It does only happen if all the components are present, if you remove any part of the having, the where, the ifnull, the the server returns the correct error.

How to repeat:
create table sub1 (a integer, b integer); insert into sub1 values (1,2),(1,3);

 select a, sum(1) r1, ifnull((select null from sub1 i where i.a=o.a),1) r2 from sub1 o where a = 1  group  by a having r1=1 and r2=1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
-

#it also happens, with 0 row subquery

select a, sum(1) r1, ifnull((select null from sub1 i where i.a<>o.a),1) r2 from sub1 o where a = 1  group  by a having r1=1 and r2=1;

#
# with a subquery that should be fine, sum() in the subquery should  return 1 row

select a, sum(1) r1, ifnull((select sum(1) from sub1 i where i.a=o.a),1) r2 from sub1 o where a = 1  group  by a having r1=1 and r2=1;
# with group
select a, sum(1) r1, ifnull((select sum(1) from sub1 i where i.a=o.a group by a),1) r2 from sub1 o where a = 1  group  by a having r1=1 and r2=1;
# group by const
select a, sum(1) r1, ifnull((select sum(1) from sub1 i where i.a=o.a group by 'X'),1) r2 from sub1 o where a = 1  group  by a having r1=1 and r2=1;

Hi!

Crashes with 4.1.10 on Linux, with both a MyISAM and InnoDB table. Stack trace below.

Regards,

Heikki

mysql> create table sub1 (a integer, b integer); insert into sub1 values (1,2),(
1,3);
Query OK, 0 rows affected (0.01 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql>  select a, sum(1) r1, ifnull((select null from sub1 i where i.a=o.a),1) r
2 from
    -> sub1 o where a = 1  group  by a having r1=1 and r2=1;

(gdb) run
Starting program: /home/heikki/mysql-4.1/sql/mysqld
[New Thread 16384 (LWP 15043)]
InnoDB: !!!!!!!!!!!!!! UNIV_DEBUG switched on !!!!!!!!!!!!!!!
[New Thread 32769 (LWP 15045)]
[New Thread 16386 (LWP 15046)]
[New Thread 32771 (LWP 15047)]
[New Thread 49156 (LWP 15048)]
[New Thread 65541 (LWP 15049)]
050131 19:36:51  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050131 19:36:51  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1323287644.
InnoDB: Doing recovery: scanned up to log sequence number 0 1323289167
050131 19:36:51  InnoDB: Starting an apply batch of log records to the database.
..
InnoDB: Progress in percents: 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 79, file name ./binlog.000001
050131 19:36:52  InnoDB: Flushing modified pages from the buffer pool...
[New Thread 81926 (LWP 15050)]
[New Thread 98311 (LWP 15051)]
[New Thread 114696 (LWP 15052)]
050131 19:36:55  InnoDB: Started; log sequence number 0 1323289167
[New Thread 131081 (LWP 15053)]
050131 19:36:56 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
/home/heikki/mysql-4.1/sql/mysqld: ready for connections.
Version: '4.1.10-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  Sourc
e distribution
[New Thread 147466 (LWP 15054)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 147466 (LWP 15054)]
0x083df898 in my_strntod_8bit (cs=0x8576da0, str=0x0, length=0, end=0x0,
    err=0x8bccc9c) at ctype-simple.c:789
789       if (length == INT_MAX32 || str[length] == 0)
Current language:  auto; currently c
(gdb) bt
#0  0x083df898 in my_strntod_8bit (cs=0x8576da0, str=0x0, length=0, end=0x0,
    err=0x8bccc9c) at ctype-simple.c:789
#1  0x080eea37 in Item_copy_string::val() (this=0x8576da0) at sql_string.h:81
#2  0x080eb795 in Item::val_result() (this=0x8bccc9c) at item.h:222
#3  0x080ede79 in Item_ref::val() (this=0x8bd3900) at item.h:868
#4  0x0810af68 in Arg_comparator::compare_real() (this=0x8bd3a20)
    at item_cmpfunc.cc:396
#5  0x0810ba2e in Item_func_eq::val_int() (this=0x8bccc9c) at item_cmpfunc.h:63
#6  0x080faa20 in Item_int_func::val_str(String*) (this=0x8bd39b8,
    str=0x8bd4c94) at item_func.cc:525
#7  0x080e6bfc in Item_copy_string::copy() (this=0x8bd4c90) at item.cc:1268
#8  0x081b8852 in copy_fields(TMP_TABLE_PARAM*) (param=0x8bd4450)
    at sql_select.cc:8608
#9  0x081b46b6 in end_send_group (join=0x8bd3bd8, join_tab=0x8bd4c40,
    end_of_records=false) at sql_select.cc:6510
#10 0x081b30ad in sub_select (join=0x8bd3bd8, join_tab=0x8bd4b00,
    end_of_records=160) at sql_select.cc:5808
#11 0x081b2cb8 in do_select (join=0x8bd3bd8, fields=0x8bd4b00, table=0x0,
    procedure=0x8bccc9c) at sql_select.cc:5692
#12 0x081a84f2 in JOIN::exec() (this=0x8bd3bd8) at sql_select.cc:1479
#13 0x081a895e in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Ite
m>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, sel
ect_result*, st_select_lex_unit*, st_select_lex*) (thd=0x8bd54a0,
    rref_pointer_array=0x8bd56b0, tables=0x8bd3510, wild_num=0,
    fields=@0x8bccc9c, conds=0x8bd3630, og_num=1, order=0x0, group=0x8bd3758,
    having=0x8bd3a70, proc_param=0x0, select_options=8669696,
    result=0x8bd3bc8, unit=0x8bd54e8, select_lex=0x8bd55c8)
    at sql_select.cc:1600
#14 0x081a4b77 in handle_select(THD*, st_lex*, select_result*) (thd=0x8bd54a0,
    lex=0x8bd54dc, result=0x8bd3bc8) at sql_select.cc:182
#15 0x0817ba13 in mysql_execute_command(THD*) (thd=0x8bd54a0)
    at sql_parse.cc:2017
#16 0x0818103b in mysql_parse(THD*, char*, unsigned) (thd=0x8bd54a0,
    inBuf=0x8bd2cf0 "select a, sum(1) r1, ifnull((select null from sub1 i where
i.a=o.a),1) r2 from\nsub1 o where a = 1  group  by a having r1=1 and r2=1",
    length=146625756) at sql_parse.cc:4091
#17 0x0817a382 in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8bd54a0, packet=0x8bcecb9 "", packet_length=132)
    at sql_parse.cc:1474
#18 0x08179ca1 in do_command(THD*) (thd=0x8bd54a0) at sql_parse.cc:1288
#19 0x0817910f in handle_one_connection (arg=0x8bccc9c) at sql_parse.cc:1020
#20 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#21 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#22 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)

problem is not subquery dependent, following query fail in the same way:
select a, sum(1) r1, ifnull(rand(),1) r2 from sub1 o where a = 1  group  by a having r1=1 and r2=1;

Bug also appear in 4.0 but becasue the bug fix is not trivial, we will fix it in 4.1 and only document it in 4.0

ChangeSet
  1.2170 05/02/07 18:13:57 monty@mysql.com +8 -0
  Fixed bug in HAVING when refering to RAND() through alias
  (BUG 8216)
looks good

Fix will be in 4.1.11