Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22819

Wrong result or Assertion `ix > 0' failed in read_to_buffer upon select with GROUP BY and GROUP_CONCAT

    XMLWordPrintable

    Details

      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
      

        Attachments

          Activity

            People

            Assignee:
            varun Varun Gupta
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: