Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5
Description
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.
CREATE TABLE t1 (a VARCHAR(1000), b CHAR(1)); |
|
|
INSERT INTO t1 VALUES |
(REPEAT('a',1000),'a'),(REPEAT('t',932),'t'),('x',NULL),('x',NULL), |
(REPEAT('z',298),'z'),(REPEAT('p',1000),'p'),(REPEAT('k',468),'k'), |
(REPEAT('c',1000),'c'),(REPEAT('o',648),'o'),('x',NULL),('x',NULL), |
(REPEAT('c',258),'c'),(REPEAT('t',414),'t'),(REPEAT('f',966),'f'), |
(REPEAT('y',746),'y'),(REPEAT('f',1000),'f'); |
|
|
insert into t1 select * from t1; |
insert into t1 select * from t1; |
insert into t1 select * from t1; |
insert into t1 select * from t1; |
|
|
SET sort_buffer_size= 16384; |
|
|
SELECT a, GROUP_CONCAT(b) FROM t1 GROUP BY a; |
|
|
# Cleanup
|
DROP TABLE t1; |
|
10.5 6877ef9a |
mariadbd: /data/src/10.5/sql/filesort.cc:1721: ulong read_to_buffer(IO_CACHE*, Merge_chunk*, Sort_param*, bool): Assertion `ix > 0' failed.
|
200606 15:49:55 [ERROR] mysqld got signal 6 ;
|
|
|
#7 0x00007f4dad2dbf12 in __GI___assert_fail (assertion=0x557f6d262fce "ix > 0", file=0x557f6d262958 "/data/src/10.5/sql/filesort.cc", line=1721, function=0x557f6d263c80 <read_to_buffer(st_io_cache*, Merge_chunk*, Sort_param*, bool)::__PRETTY_FUNCTION__> "ulong read_to_buffer(IO_CACHE*, Merge_chunk*, Sort_param*, bool)") at assert.c:101
|
#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.
CREATE TABLE t1 (a VARCHAR(1000), b CHAR(1)); |
|
|
INSERT INTO t1 VALUES |
(REPEAT('a',1000),'a'),(REPEAT('t',932),'t'),('x',NULL),('x',NULL), |
(REPEAT('z',298),'z'),(REPEAT('p',1000),'p'),(REPEAT('k',468),'k'), |
(REPEAT('c',1000),'c'),(REPEAT('o',648),'o'),('x',NULL),('x',NULL), |
(REPEAT('c',258),'c'),(REPEAT('t',414),'t'),(REPEAT('f',966),'f'), |
(REPEAT('y',746),'y'),(REPEAT('f',1000),'f'); |
|
|
insert into t1 select * from t1; |
insert into t1 select * from t1; |
insert into t1 select * from t1; |
insert into t1 select * from t1; |
|
|
SET sort_buffer_size= 16384; |
|
|
SELECT SUBSTR(a,1,1), LENGTH(a), GROUP_CONCAT(b), COUNT(*) FROM t1 GROUP BY a; |
|
|
# Cleanup
|
DROP TABLE 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 GROUP BY a; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t1 ALL NULL NULL NULL NULL 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` group by `test`.`t1`.`a` |
The failure appeared in 10.5 tree with this commit:
commit b753ac066bc26acda9deb707a31c112f1bbf9ec2
|
Author: Varun Gupta <varun.gupta@mariadb.com>
|
Date: Tue Mar 10 04:56:38 2020 +0530
|
|
|
MDEV-21580: Allow packed sort keys in sort buffer
|