Server crash or assertion failure with join_cache_level=5

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;

#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

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

commit ce7ffe61d836fe9f0cfc1087f058bc40d66e5cfb

Author: Igor Babaev

Date:   Tue May 2 23:17:07 2023 -0700

    MDEV-26301 Split optimization refills temporary table too many times