[MDEV-11580] Order By with IFNULL and SELECT crashes server Created: 2016-12-15  Updated: 2016-12-15  Resolved: 2016-12-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.17, 10.1.18, 10.1.19
Fix Version/s: 5.5.54, 10.0.29, 10.1.20, 10.2.3

Type: Bug Priority: Critical
Reporter: Bas Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 7.2.1, InnoDB


 Description   

We know this is normally not how to order by on a column but because our software generated this query we experienced crashes and could reproduce it.

Reproduction

create table testcrash (id int not null primary key, first_name varchar(255), last_name varchar(255));
 
 
 
select testcrash.first_name, testcrash.last_name,
 
ifnull((SELECT count(id) from testcrash where id = 0), 0) as data
 
 from testcrash 
group by id
 
order by IFNULL((SELECT count(id) from testcrash where id = 0), 0)
 
LIMIT 0,25;



 Comments   
Comment by Elena Stepanova [ 2016-12-15 ]

Thanks for the report.
This crash was fixed by the patch for MDEV-10776. The bugfix will be released in 10.1.20.

#3  <signal handler called>
 
#4  0x00007fdb3e444afb in Item_subselect::const_item (this=0x7fdb32c46c48) at /data/src/10.1-bug/sql/item_subselect.cc:910
 
#5  0x00007fdb3e15aff8 in st_select_lex::optimize_unflattened_subqueries (this=0x7fdb34bfd1b8, const_only=true) at /data/src/10.1-bug/sql/sql_lex.cc:3719
 
#6  0x00007fdb3e2f2f5d in JOIN::optimize_constant_subqueries (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/opt_subselect.cc:5090
 
#7  0x00007fdb3e1a4d36 in JOIN::optimize_inner (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/sql_select.cc:1133
 
#8  0x00007fdb3e1a4926 in JOIN::optimize (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/sql_select.cc:1040
 
#9  0x00007fdb3e1acf62 in mysql_select (thd=0x7fdb34bf9070, rref_pointer_array=0x7fdb34bfd430, tables=0x7fdb32c44f00, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x7fdb32c46ff0, group=0x7fdb32c45600, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fdb32c471d8, unit=0x7fdb34bfcab8, select_lex=0x7fdb34bfd1b8) at /data/src/10.1-bug/sql/sql_select.cc:3424
 
#10 0x00007fdb3e1a2ae6 in handle_select (thd=0x7fdb34bf9070, lex=0x7fdb34bfc9f0, result=0x7fdb32c471d8, setup_tables_done_option=0) at /data/src/10.1-bug/sql/sql_select.cc:384
 
#11 0x00007fdb3e172b2d in execute_sqlcom_select (thd=0x7fdb34bf9070, all_tables=0x7fdb32c44f00) at /data/src/10.1-bug/sql/sql_parse.cc:5896
 
#12 0x00007fdb3e168a4f in mysql_execute_command (thd=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_parse.cc:2968
 
#13 0x00007fdb3e176286 in mysql_parse (thd=0x7fdb34bf9070, rawbuf=0x7fdb32c43088 "select testcrash.first_name, testcrash.last_name,\nifnull((SELECT count(id) from testcrash where id = 0), 0) as data\nfrom testcrash \ngroup by id\norder by IFNULL((SELECT count(id) from testcrash where i"..., length=221, parser_state=0x7fdb3f0865e0) at /data/src/10.1-bug/sql/sql_parse.cc:7319
 
#14 0x00007fdb3e164c59 in dispatch_command (command=COM_QUERY, thd=0x7fdb34bf9070, packet=0x7fdb3653e071 "select testcrash.first_name, testcrash.last_name,\nifnull((SELECT count(id) from testcrash where id = 0), 0) as data\nfrom testcrash \ngroup by id\norder by IFNULL((SELECT count(id) from testcrash where i"..., packet_length=221) at /data/src/10.1-bug/sql/sql_parse.cc:1487
 
#15 0x00007fdb3e163990 in do_command (thd=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_parse.cc:1108
 
#16 0x00007fdb3e299ed5 in do_handle_one_connection (thd_arg=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_connect.cc:1350
 
#17 0x00007fdb3e299c39 in handle_one_connection (arg=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_connect.cc:1262
 
#18 0x00007fdb3e57caf8 in pfs_spawn_thread (arg=0x7fdb3b027ef0) at /data/src/10.1-bug/storage/perfschema/pfs.cc:1860
 
#19 0x00007fdb3d8610a4 in start_thread (arg=0x7fdb3f087b00) at pthread_create.c:309
 
#20 0x00007fdb3ba1987d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Generated at Thu Feb 08 07:51:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.