Note: I'm not sure whether it's just a problem of the small sort_buffer_size value, or something more generic. Simply scaling up the data and buffer makes the failure disappear, but it's not a guarantee, the test case seems to be very sensitive to specific data lengths.
#8 0x0000557f6c6383fe in read_to_buffer (fromfile=0x7f4da8679130, buffpek=0x7f4d8c074560, param=0x7f4da8679080, packed_format=true) at /data/src/10.5/sql/filesort.cc:1721
#9 0x0000557f6c638e40 in merge_buffers (param=0x7f4da8679080, from_file=0x7f4da8679130, to_file=0x7f4d8c06fb40, sort_buffer=..., lastbuff=0x7f4d8c074368, Fb=0x7f4d8c074368, Tb=0x7f4d8c074560, flag=1) at /data/src/10.5/sql/filesort.cc:1965
#10 0x0000557f6c6393cf in merge_index (param=0x7f4da8679080, sort_buffer=..., buffpek=0x7f4d8c074368, maxbuffer=9, tempfile=0x7f4da8679130, outfile=0x7f4d8c06fb40) at /data/src/10.5/sql/filesort.cc:2074
#11 0x0000557f6c634238 in filesort (thd=0x7f4d8c000b18, table=0x7f4d8c0f9818, filesort=0x7f4d8c018358, tracker=0x7f4d8c018a48, join=0x7f4d8c015828, first_table_bit=1) at /data/src/10.5/sql/filesort.cc:419
#12 0x0000557f6c374651 in create_sort_index (thd=0x7f4d8c000b18, join=0x7f4d8c015828, tab=0x7f4d8c016bb8, fsort=0x7f4d8c018358) at /data/src/10.5/sql/sql_select.cc:23884
#13 0x0000557f6c36e6ba in st_join_table::sort_table (this=0x7f4d8c016bb8) at /data/src/10.5/sql/sql_select.cc:21613
#14 0x0000557f6c36e295 in join_init_read_record (tab=0x7f4d8c016bb8) at /data/src/10.5/sql/sql_select.cc:21552
#15 0x0000557f6c36c02d in sub_select (join=0x7f4d8c015828, join_tab=0x7f4d8c016bb8, end_of_records=false) at /data/src/10.5/sql/sql_select.cc:20626
#16 0x0000557f6c36b4ee in do_select (join=0x7f4d8c015828, procedure=0x0) at /data/src/10.5/sql/sql_select.cc:20163
#17 0x0000557f6c33f281 in JOIN::exec_inner (this=0x7f4d8c015828) at /data/src/10.5/sql/sql_select.cc:4475
#18 0x0000557f6c33e3ad in JOIN::exec (this=0x7f4d8c015828) at /data/src/10.5/sql/sql_select.cc:4256
#19 0x0000557f6c33fade in mysql_select (thd=0x7f4d8c000b18, tables=0x7f4d8c014670, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7f4d8c014ea8, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f4d8c015800, unit=0x7f4d8c004b30, select_lex=0x7f4d8c013a20) at /data/src/10.5/sql/sql_select.cc:4680
#20 0x0000557f6c32f59a in handle_select (thd=0x7f4d8c000b18, lex=0x7f4d8c004a68, result=0x7f4d8c015800, setup_tables_done_option=0) at /data/src/10.5/sql/sql_select.cc:429
#21 0x0000557f6c2f4bef in execute_sqlcom_select (thd=0x7f4d8c000b18, all_tables=0x7f4d8c014670) at /data/src/10.5/sql/sql_parse.cc:6208
#22 0x0000557f6c2ebf23 in mysql_execute_command (thd=0x7f4d8c000b18) at /data/src/10.5/sql/sql_parse.cc:3939
#23 0x0000557f6c2f9a3b in mysql_parse (thd=0x7f4d8c000b18, rawbuf=0x7f4d8c013960 "SELECT a, GROUP_CONCAT(b) FROM t1 GROUP BY a", length=44, parser_state=0x7f4da867a520, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:7992
#24 0x0000557f6c2e5dc1 in dispatch_command (command=COM_QUERY, thd=0x7f4d8c000b18, packet=0x7f4d8c1b06e9 "", packet_length=44, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:1875
#25 0x0000557f6c2e44f9 in do_command (thd=0x7f4d8c000b18) at /data/src/10.5/sql/sql_parse.cc:1356
#26 0x0000557f6c488e8f in do_handle_one_connection (connect=0x557f6f950358, put_in_cache=true) at /data/src/10.5/sql/sql_connect.cc:1411
#27 0x0000557f6c488bf7 in handle_one_connection (arg=0x557f6f950358) at /data/src/10.5/sql/sql_connect.cc:1313
#28 0x0000557f6c9c01a0 in pfs_spawn_thread (arg=0x557f6f93c4d8) at /data/src/10.5/storage/perfschema/pfs.cc:2201
#29 0x00007f4daf2644a4 in start_thread (arg=0x7f4da867b700) at pthread_create.c:456
#30 0x00007f4dad398d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
Reproducible with at least MyISAM, InnoDB, Aria.
Not reproducible on 10.4 – it doesn't crash and returns what appears to be a correct result (see below).
Non-debug build of 10.5 doesn't crash, but it produces a different result comparing to 10.4. Below is a slightly different test case which shows it. The data and query structure are the same, only the select list contains items which are easier to read and compare.
SELECT SUBSTR(a,1,1), LENGTH(a), GROUP_CONCAT(b), COUNT(*) FROM t1 GROUPBY a;
# Cleanup
DROPTABLE t1;
Result on 10.5
SUBSTR(a,1,1) LENGTH(a) GROUP_CONCAT(b) COUNT(*)
a 1000 a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a 16
c 258 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16
c 1000 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16
f 966 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 16
f 1000 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 15
k 468 k,k,k,k,k,k,k,k,k,k,k,k,k,k,k,k 16
o 648 o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o 16
p 1000 p,p,p,p,p,p,p,p,p,p,p,p,p,p,p,p 16
t 414 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16
t 932 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16
x 1 NULL 64
y 746 y,y,y,y,y,y,y,y,y,y,y,y,y,y,y 15
z 298 z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z 16
Result on 10.4
SUBSTR(a,1,1) LENGTH(a) GROUP_CONCAT(b) COUNT(*)
a 1000 a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a 16
c 258 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16
c 1000 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16
f 966 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 16
f 1000 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 16
k 468 k,k,k,k,k,k,k,k,k,k,k,k,k,k,k,k 16
o 648 o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o 16
p 1000 p,p,p,p,p,p,p,p,p,p,p,p,p,p,p,p 16
t 414 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16
t 932 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16
x 1 NULL 64
y 746 y,y,y,y,y,y,y,y,y,y,y,y,y,y,y,y 16
z 298 z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z 16
Note different counts and GROUP_CONCAT results for f/1000 and y/746.
Plan is the same on both versions:
Plan
EXPLAIN EXTENDED
SELECT SUBSTR(a,1,1), LENGTH(a), GROUP_CONCAT(b), COUNT(*) FROM t1 GROUPBY a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALLNULLNULLNULLNULL 256 100.00 Using filesort
Warnings:
Note 1003 select substr(`test`.`t1`.`a`,1,1) AS `SUBSTR(a,1,1)`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)`,group_concat(`test`.`t1`.`b` separator ',') AS `GROUP_CONCAT(b)`,count(0) AS `COUNT(*)` from `test`.`t1` groupby `test`.`t1`.`a`
The failure appeared in 10.5 tree with this commit:
Bug
Major