[MDEV-31194] Server crash or assertion failure with join_cache_level=5 Created: 2023-05-04  Updated: 2023-05-09  Resolved: 2023-05-05

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: N/A
Fix Version/s: 11.1.1, 10.11.3, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: regression


 Description    

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (3),(4);
 
  
CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria;
 
INSERT INTO t2 VALUES (1),(2);
 
 
 
SET OPTIMIZER_SWITCH= 'derived_with_keys=off';
 
SET JOIN_CACHE_LEVEL= 4;
 
 
 
SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a;
 
 
 
# Cleanup
 
DROP TABLE t1, t2;

bb-10.4-release 7973ffde0fed non-debug

 
#2  <signal handler called>
 
#3  0x0000558ff1103bae in ha_heap::index_flags (this=0x7f6aa403eea0, inx=<optimized out>, part=<optimized out>, all_parts=true) at /data/src/10.4-bug-2/storage/heap/ha_heap.h:58
 
#4  0x0000558ff0dde954 in push_index_cond (tab=tab@entry=0x7f6aa4041d58, keyno=64) at /data/src/10.4-bug-2/sql/opt_index_cond_pushdown.cc:354
 
#5  0x0000558ff0d1e14c in make_join_readinfo (no_jbuf_after=<optimized out>, options=<optimized out>, join=0x7f6aa4014508) at /data/src/10.4-bug-2/sql/sql_select.cc:13407
 
#6  JOIN::optimize_stage2 (this=this@entry=0x7f6aa4014508) at /data/src/10.4-bug-2/sql/sql_select.cc:2887
 
#7  0x0000558ff0d212f3 in JOIN::optimize_inner (this=this@entry=0x7f6aa4014508) at /data/src/10.4-bug-2/sql/sql_select.cc:2373
 
#8  0x0000558ff0d234dd in JOIN::optimize (this=this@entry=0x7f6aa4014508) at /data/src/10.4-bug-2/sql/sql_select.cc:1690
 
#9  0x0000558ff0d23f81 in mysql_select (thd=thd@entry=0x7f6aa4000c58, tables=0x7f6aa4010820, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f6aa40144e0, unit=0x7f6aa40049d0, select_lex=0x7f6aa4010208) at /data/src/10.4-bug-2/sql/sql_select.cc:4797
 
#10 0x0000558ff0d249c3 in handle_select (thd=thd@entry=0x7f6aa4000c58, lex=lex@entry=0x7f6aa4004910, result=result@entry=0x7f6aa40144e0, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/10.4-bug-2/sql/sql_select.cc:442
 
#11 0x0000558ff0cb955c in execute_sqlcom_select (thd=thd@entry=0x7f6aa4000c58, all_tables=0x7f6aa4010820) at /data/src/10.4-bug-2/sql/sql_parse.cc:6463
 
#12 0x0000558ff0cc5a1b in mysql_execute_command (thd=thd@entry=0x7f6aa4000c58) at /data/src/10.4-bug-2/sql/sql_parse.cc:3966
 
#13 0x0000558ff0cc7290 in mysql_parse (thd=thd@entry=0x7f6aa4000c58, rawbuf=<optimized out>, length=84, parser_state=parser_state@entry=0x7f6abb5c9540, is_com_multi=is_com_multi@entry=false, is_next_command=<optimized out>) at /data/src/10.4-bug-2/sql/sql_parse.cc:7998
 
#14 0x0000558ff0cc8f55 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f6aa4000c58, packet=packet@entry=0x7f6aa4007d59 "SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a", packet_length=packet_length@entry=84, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.4-bug-2/sql/sql_parse.cc:1857
 
#15 0x0000558ff0ccaa58 in do_command (thd=0x7f6aa4000c58) at /data/src/10.4-bug-2/sql/sql_parse.cc:1378
 
#16 0x0000558ff0daf984 in do_handle_one_connection (connect=connect@entry=0x558ff47565b8) at /data/src/10.4-bug-2/sql/sql_connect.cc:1420
 
#17 0x0000558ff0dafad4 in handle_one_connection (arg=arg@entry=0x558ff47565b8) at /data/src/10.4-bug-2/sql/sql_connect.cc:1324
 
#18 0x0000558ff10ce010 in pfs_spawn_thread (arg=0x558ff46afaa8) at /data/src/10.4-bug-2/storage/perfschema/pfs.cc:1869
 
#19 0x00007f6ac5687fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#20 0x00007f6ac57085bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

bb-10.4-release 7973ffde0fede debug

 
mysqld: /data/src/10.4-bug/sql/sql_bitmap.h:81: uint Bitmap<width>::bit_index(uint) const [with unsigned int width = 64; uint = unsigned int]: Assertion `n < width' failed.
 
230505  2:54:44 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007f581c653df2 in __GI___assert_fail (assertion=0x5646978183c1 "n < width", file=0x5646978183d0 "/data/src/10.4-bug/sql/sql_bitmap.h", line=81, function=0x5646978183f8 "uint Bitmap<width>::bit_index(uint) const [with unsigned int width = 64; uint = unsigned int]") at ./assert/assert.c:101
 
#10 0x000056469690c61a in Bitmap<64u>::bit_index (this=0x7f580406bca0, n=64) at /data/src/10.4-bug/sql/sql_bitmap.h:81
 
#11 0x0000564696a482ef in Bitmap<64u>::is_set (this=0x7f580406bca0, n=64) at /data/src/10.4-bug/sql/sql_bitmap.h:126
 
#12 0x0000564696a0fc86 in make_join_readinfo (join=0x7f5804018bf0, options=0, no_jbuf_after=2) at /data/src/10.4-bug/sql/sql_select.cc:13404
 
#13 0x00005646969eff13 in JOIN::optimize_stage2 (this=0x7f5804018bf0) at /data/src/10.4-bug/sql/sql_select.cc:2887
 
#14 0x00005646969ee044 in JOIN::optimize_inner (this=0x7f5804018bf0) at /data/src/10.4-bug/sql/sql_select.cc:2373
 
#15 0x00005646969eb784 in JOIN::optimize (this=0x7f5804018bf0) at /data/src/10.4-bug/sql/sql_select.cc:1690
 
#16 0x00005646969f729c in mysql_select (thd=0x7f5804000da0, tables=0x7f5804014f08, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f5804018bc8, unit=0x7f5804004cd8, select_lex=0x7f58040148f0) at /data/src/10.4-bug/sql/sql_select.cc:4797
 
#17 0x00005646969e610b in handle_select (thd=0x7f5804000da0, lex=0x7f5804004c18, result=0x7f5804018bc8, setup_tables_done_option=0) at /data/src/10.4-bug/sql/sql_select.cc:442
 
#18 0x00005646969a8e9e in execute_sqlcom_select (thd=0x7f5804000da0, all_tables=0x7f5804014f08) at /data/src/10.4-bug/sql/sql_parse.cc:6463
 
#19 0x000056469699f3a0 in mysql_execute_command (thd=0x7f5804000da0) at /data/src/10.4-bug/sql/sql_parse.cc:3966
 
#20 0x00005646969ad013 in mysql_parse (thd=0x7f5804000da0, rawbuf=0x7f58040147e8 "SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a", length=84, parser_state=0x7f58166c83c0, is_com_multi=false, is_next_command=false) at /data/src/10.4-bug/sql/sql_parse.cc:7998
 
#21 0x0000564696998a44 in dispatch_command (command=COM_QUERY, thd=0x7f5804000da0, packet=0x7f580400ac41 "SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a", packet_length=84, is_com_multi=false, is_next_command=false) at /data/src/10.4-bug/sql/sql_parse.cc:1857
 
#22 0x00005646969972ab in do_command (thd=0x7f5804000da0) at /data/src/10.4-bug/sql/sql_parse.cc:1378
 
#23 0x0000564696b37c60 in do_handle_one_connection (connect=0x56469a242a30) at /data/src/10.4-bug/sql/sql_connect.cc:1420
 
#24 0x0000564696b379c8 in handle_one_connection (arg=0x56469a242a30) at /data/src/10.4-bug/sql/sql_connect.cc:1324
 
#25 0x0000564697089638 in pfs_spawn_thread (arg=0x56469a288960) at /data/src/10.4-bug/storage/perfschema/pfs.cc:1869
 
#26 0x00007f581c6a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#27 0x00007f581c7285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

The failure started happening on bb-10.4-release after this commit:

commit ce7ffe61d836fe9f0cfc1087f058bc40d66e5cfb
 
Author: Igor Babaev
 
Date:   Tue May 2 23:17:07 2023 -0700
 
 
 
    MDEV-26301 Split optimization refills temporary table too many times



 Comments   
Comment by Sergei Petrunia [ 2023-05-05 ] 

commit 2594da7a33580bf03590502a011679c878487d0c (HEAD -> bb-10.4-release, origin/bb-10.4-release)
 
Author: Sergei Petrunia <sergey@mariadb.com>
 
Date:   Fri May 5 11:16:23 2023 +0300
 
 
 
    MDEV-31194: Server crash or assertion failure with join_cache_level=4
 
    
    The problem, introduced in patch for MDEV-26301:

Generated at Thu Feb 08 10:21:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.