NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt
WHERE id IN ( SELECT id LIKE 'x' FROM t1 )
Alice Sherepa
added a comment - - edited Test case from MDEV-32602 , crashes on 10.5-11.2, after e87440b commit
CREATE TABLE t1 ( id int NOT NULL , PRIMARY KEY (id));
INSERT INTO t1 VALUES
(-65632),(-65622),(-65570),(-65560),(-65553),(-65543),(-65504),(-65494),(-65491),(-65481),(-65442),(-65432),(-65425),(-65415),(-65363),(-65353),(-32864),(-32854),(-32802),(-32792),(-32785),(-32775),(-32736),(-32726),(-32723),(-32713),(-32674),(-32664),(-32657),(-32647),(-32595),(-32585),(-96),(-86),(-34),(-24),(-17),(-7),(32),(42),(45),(55),(94),(104),(111),(121),(173),(183),(2191719),(2191729),(2191781),(2191791),(2191798),(2191808),(2191847),(2191857),(2191860),(2191870),(2191909),(2191919),(2191926),(2191936),(2191988),(2191998),(2224487),(2224497),(2224549),(2224559),(2224566),(2224576),(2224615),(2224625),(2224628),(2224638),(2224677),(2224687),(2224694),(2224704),(2224756),(2224766),(2257255),(2257265),(2257317),(2257327),(2257334),(2257344),(2257383),(2257393),(2257396),(2257406),(2257445),(2257455),(2257462),(2257472),(2257524),(2257534);
INSERT INTO t1 SELECT id
FROM t1
NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt
WHERE id IN ( SELECT id LIKE 'x' FROM t1 ) ;
DROP TABLE t1;
Version: '10.5.23-MariaDB-debug-log'
231028 13:29:08 [ERROR] mysqld got signal 11 ;
Server version: 10.5.23-MariaDB-debug-log source revision: b5e43a1d3539c7254c298dc9f63a261281345d59
sql/signal_handler.cc:241(handle_fatal_signal)[0x5615c8385d90]
sigaction.c:0(__restore_rt)[0x7fc23cb66420]
sql/opt_split.cc:1049(st_join_table::choose_best_splitting(unsigned int, unsigned long long, POSITION const*, unsigned long long*))[0x5615c81c7a1e]
sql/sql_select.cc:7570(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x5615c7c4ccce]
sql/sql_select.cc:9886(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c59d6a]
sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]
sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]
sql/sql_select.cc:9054(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x5615c7c55ad5]
sql/sql_select.cc:8616(choose_plan(JOIN*, unsigned long long))[0x5615c7c537a1]
sql/sql_select.cc:5753(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5615c7c3e42f]
sql/sql_select.cc:2363(JOIN::optimize_inner())[0x5615c7c1b02c]
sql/sql_select.cc:1721(JOIN::optimize())[0x5615c7c14505]
sql/sql_select.cc:4845(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5615c7c351cc]
sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5615c7c06073]
sql/sql_parse.cc:4790(mysql_execute_command(THD*))[0x5615c7b5f885]
sql/sql_parse.cc:8120(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5615c7b76977]
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5615c7b4c3a5]
sql/sql_parse.cc:1375(do_command(THD*))[0x5615c7b48d0d]
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5615c7fa795b]
sql/sql_connect.cc:1320(handle_one_connection)[0x5615c7fa72bf]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5615c8c33aba]
nptl/pthread_create.c:478(start_thread)[0x7fc23cb5a609]
Query (0x62b0000852a8): INSERT INTO t1 SELECT id
FROM t1
NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt
WHERE id IN ( SELECT id LIKE 'x' FROM t1 )
not reproducible on current 10.5 2c60d43d7df134c3875b3760a5d54a-11.3, after 6cfd2ba397 commit Merge branch '10.4' into 10.5
but maybe not fixed - possible that query plan changed. Currently:
MariaDB [test]> explain extended SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 );
CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB;
UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
main.test [ 391 fail ]
Test ended at 2024-09-14 10:41:15
CURRENT_TEST: main.test
mysqltest: At line 8: query 'UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 0...
Server [mysqld.1 - pid: 2104200, winpid: 2104200, exit: 256] failed during test run
#6 0x0000557ff3bb52fb in make_join_statistics (join=join@entry=0x14d558018d18, tables_list=@0x14d558005998: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d558018cf8, last = 0x14d558018d08, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x14d558019010)at /test/10.5_dbg/sql/sql_select.cc:5810
#7 0x0000557ff3bbb7ad in JOIN::optimize_inner (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:2416
#8 0x0000557ff3bbbcfe in JOIN::optimize (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:1765
#9 0x0000557ff3bbbe01 in mysql_select (thd=thd@entry=0x14d558000d58, tables=tables@entry=0x14d558014320, fields=@0x14d5eabfdc90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x557ff51d4530 <end_of_list>, last = 0x14d5eabfdc90, elements = 0}, <No data fields>}, conds=conds@entry=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14d558018bf8, unit=0x14d558004f08, select_lex=0x14d558005740)at /test/10.5_dbg/sql/sql_select.cc:4902
#13 0x0000557ff3b4d39c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14d558000d58, packet=packet@entry=0x14d55819c9c9 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_class.h:238
#14 0x0000557ff3b4fa8d in do_command (thd=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:1376
#15 0x0000557ff3c88d12 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x557ff66a9c88, put_in_cache=put_in_cache@entry=true)at /test/10.5_dbg/sql/sql_connect.cc:1417
#16 0x0000557ff3c89042 in handle_one_connection (arg=arg@entry=0x557ff66a9c88)at /test/10.5_dbg/sql/sql_connect.cc:1319
#17 0x0000557ff40cb95a in pfs_spawn_thread (arg=0x557ff660ed28)at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014d5efe9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
#19 0x000014d5eff29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78
#7 0x000055b96cd07b6f in make_join_statistics (join=join@entry=0x146e94b311a0, tables_list=@0x146e940059e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146e94016dc8, last = 0x146e94b31190, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x146e94b314f8)at /test/server_opt/sql/sql_select.cc:6312
#8 0x000055b96cd0c700 in JOIN::optimize_inner (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:2691
#9 0x000055b96cd0ce4a in JOIN::optimize (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:1989
#10 0x000055b96cd0f07e in Sql_cmd_dml::execute_inner (this=this@entry=0x146e94016730, thd=thd@entry=0x146e94000c68)at /test/server_opt/sql/sql_select.cc:33844
#11 0x000055b96cd7e679 in Sql_cmd_update::execute_inner (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_update.cc:3109
#12 0x000055b96ccc5621 in Sql_cmd_dml::execute (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_select.cc:33793
#13 0x000055b96cc88b05 in mysql_execute_command (thd=thd@entry=0x146e94000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)at /test/server_opt/sql/sql_parse.cc:4403
#14 0x000055b96cc8d39e in mysql_parse (thd=0x146e94000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/server_opt/sql/sql_parse.cc:7876
#15 0x000055b96cc8fedd in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146e94000c68, packet=packet@entry=0x146e94008869 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, blocking=blocking@entry=true)at /test/server_opt/sql/sql_parse.cc:1991
#16 0x000055b96cc91b3d in do_command (thd=0x146e94000c68, blocking=blocking@entry=true) at /test/server_opt/sql/sql_parse.cc:1405
#17 0x000055b96cdc8d3f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55b970ec33a8, put_in_cache=put_in_cache@entry=true)at /test/server_opt/sql/sql_connect.cc:1448
#18 0x000055b96cdc90b5 in handle_one_connection (arg=arg@entry=0x55b970ec33a8)at /test/server_opt/sql/sql_connect.cc:1350
#19 0x000055b96d1907bf in pfs_spawn_thread (arg=0x55b970e67578)at /test/server_opt/storage/perfschema/pfs.cc:2198
#20 0x0000146edac9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
#21 0x0000146edad29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78
Reproducible on 10.5, 11.1, 11.2 and 11.7, on optimized and debug builds. All stacks seen thus far accross versions and build types:
Issue generally tends to repeat around 500 MTR rounds, though it varies a bit, and I have seen a 5000 rounds MTR run on 11.7 opt not crash, only to crash after 200 rounds on a repeat run.
Roel Van de Paar
added a comment -
--source include/have_innodb.inc
SET @@max_statement_time=0.0001;
--error 0, 1969
CREATE TABLE t (c1 DATETIME PRIMARY KEY ,c2 VARCHAR (40)) ENGINE=InnoDB;
--error 0, 1969
UPDATE t t INNER JOIN ( SELECT c1, MAX (c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
--error 0, 1969
UPDATE t t INNER JOIN ( SELECT c1, MAX (c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
--error 0, 1969
DROP TABLE t;
Sporadid. Run MTR with --repeat 10000. Example occurence:
CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug)
$ ./mtr --repeat 10000 test
....
main.test [ 390 pass ] 7
SET @@max_statement_time=0.0001;
CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB;
UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
main.test [ 391 fail ]
Test ended at 2024-09-14 10:41:15
CURRENT_TEST: main.test
mysqltest: At line 8: query 'UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 0...
Server [mysqld.1 - pid: 2104200, winpid: 2104200, exit: 256] failed during test run
Server log from this test:
----------SERVER LOG START-----------
----------SERVER LOG END-------------
- found 'core' (0/5)
Leads to:
CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug)
Core was generated by `/test/MD090924-mariadb-10.5.27-linux-x86_64-dbg/bin/mariadbd --defaults-group-s'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090
[Current thread is 1 (LWP 2104708)]
(gdb) bt
#0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090
#1 0x0000557ff3b896d1 in best_access_path (join=join@entry=0x14d558018d18, s=s@entry=0x14d558b5c800, remaining_tables=remaining_tables@entry=2, join_positions=0x14d558b5cdf0, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x14d558b5cf30, loose_scan_pos=0x14d5eabfd070)at /test/10.5_dbg/sql/sql_select.cc:7627
#2 0x0000557ff3b89b71 in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=1.2, search_depth=search_depth@entry=61, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:9943
#3 0x0000557ff3b8a4ab in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=3, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:10032
#4 0x0000557ff3b8b396 in greedy_search (use_cond_selectivity=4, prune_level=<optimized out>, search_depth=62, remaining_tables=3, join=0x14d558018d18) at /test/10.5_dbg/sql/sql_select.cc:9111
#5 choose_plan (join=join@entry=0x14d558018d18, join_tables=<optimized out>)at /test/10.5_dbg/sql/sql_select.cc:8673
#6 0x0000557ff3bb52fb in make_join_statistics (join=join@entry=0x14d558018d18, tables_list=@0x14d558005998: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d558018cf8, last = 0x14d558018d08, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x14d558019010)at /test/10.5_dbg/sql/sql_select.cc:5810
#7 0x0000557ff3bbb7ad in JOIN::optimize_inner (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:2416
#8 0x0000557ff3bbbcfe in JOIN::optimize (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:1765
#9 0x0000557ff3bbbe01 in mysql_select (thd=thd@entry=0x14d558000d58, tables=tables@entry=0x14d558014320, fields=@0x14d5eabfdc90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x557ff51d4530 <end_of_list>, last = 0x14d5eabfdc90, elements = 0}, <No data fields>}, conds=conds@entry=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14d558018bf8, unit=0x14d558004f08, select_lex=0x14d558005740)at /test/10.5_dbg/sql/sql_select.cc:4902
#10 0x0000557ff3c3075d in mysql_multi_update (thd=thd@entry=0x14d558000d58, table_list=0x14d558014320, fields=fields@entry=0x14d5580058a8, values=values@entry=0x14d558005e68, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x14d558004f08, select_lex=0x14d558005740, result=0x14d5eabfded0)at /test/10.5_dbg/sql/sql_update.cc:2003
#11 0x0000557ff3b4466c in mysql_execute_command (thd=thd@entry=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:4575
#12 0x0000557ff3b4acf1 in mysql_parse (thd=thd@entry=0x14d558000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14d5eabfe2b0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_parse.cc:8236
#13 0x0000557ff3b4d39c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14d558000d58, packet=packet@entry=0x14d55819c9c9 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_class.h:238
#14 0x0000557ff3b4fa8d in do_command (thd=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:1376
#15 0x0000557ff3c88d12 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x557ff66a9c88, put_in_cache=put_in_cache@entry=true)at /test/10.5_dbg/sql/sql_connect.cc:1417
#16 0x0000557ff3c89042 in handle_one_connection (arg=arg@entry=0x557ff66a9c88)at /test/10.5_dbg/sql/sql_connect.cc:1319
#17 0x0000557ff40cb95a in pfs_spawn_thread (arg=0x557ff660ed28)at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014d5efe9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
#19 0x000014d5eff29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78
And
CS 11.7.0 5bbda9711131845ae6b4315a268b4d1710943a85 (Optimized)
Core was generated by `/test/MD090924-mariadb-11.7.0-linux-x86_64-opt/bin/mariadbd --defaults-group-su'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105
[Current thread is 1 (LWP 3149311)]
(gdb) bt
#0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105
#1 0x000055b96ccd14a8 in best_access_path (join=join@entry=0x146e94b311a0, s=s@entry=0x146e941e10c0, remaining_tables=remaining_tables@entry=2, join_positions=0x146e941e1750, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x146e941e2080, loose_scan_pos=0x146e941e21d0)at /test/server_opt/sql/sql_select.cc:8582
#2 0x000055b96ccd5477 in get_costs_for_tables (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, pos=pos@entry=0x146e941e1720, store_position=0x146ed4467308, allowed_tables=0x146ed4467318, stop_on_eq_ref=false, trace_one_table=0x146ed4467320) at /test/server_opt/sql/sql_select.cc:11329
#3 0x000055b96ccdef1e in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44674b0)at /test/server_opt/sql/sql_select.cc:11586
#4 0x000055b96ccdfa0b in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=<optimized out>, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44675c0)at /test/server_opt/sql/sql_select.cc:11812
#5 0x000055b96cce0799 in greedy_search (use_cond_selectivity=4, search_depth=62, remaining_tables=3, join=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:10557
#6 choose_plan (join=join@entry=0x146e94b311a0, join_tables=<optimized out>, emb_sjm_nest=emb_sjm_nest@entry=0x0)at /test/server_opt/sql/sql_select.cc:10076
#7 0x000055b96cd07b6f in make_join_statistics (join=join@entry=0x146e94b311a0, tables_list=@0x146e940059e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146e94016dc8, last = 0x146e94b31190, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x146e94b314f8)at /test/server_opt/sql/sql_select.cc:6312
#8 0x000055b96cd0c700 in JOIN::optimize_inner (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:2691
#9 0x000055b96cd0ce4a in JOIN::optimize (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:1989
#10 0x000055b96cd0f07e in Sql_cmd_dml::execute_inner (this=this@entry=0x146e94016730, thd=thd@entry=0x146e94000c68)at /test/server_opt/sql/sql_select.cc:33844
#11 0x000055b96cd7e679 in Sql_cmd_update::execute_inner (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_update.cc:3109
#12 0x000055b96ccc5621 in Sql_cmd_dml::execute (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_select.cc:33793
#13 0x000055b96cc88b05 in mysql_execute_command (thd=thd@entry=0x146e94000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)at /test/server_opt/sql/sql_parse.cc:4403
#14 0x000055b96cc8d39e in mysql_parse (thd=0x146e94000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/server_opt/sql/sql_parse.cc:7876
#15 0x000055b96cc8fedd in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146e94000c68, packet=packet@entry=0x146e94008869 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, blocking=blocking@entry=true)at /test/server_opt/sql/sql_parse.cc:1991
#16 0x000055b96cc91b3d in do_command (thd=0x146e94000c68, blocking=blocking@entry=true) at /test/server_opt/sql/sql_parse.cc:1405
#17 0x000055b96cdc8d3f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55b970ec33a8, put_in_cache=put_in_cache@entry=true)at /test/server_opt/sql/sql_connect.cc:1448
#18 0x000055b96cdc90b5 in handle_one_connection (arg=arg@entry=0x55b970ec33a8)at /test/server_opt/sql/sql_connect.cc:1350
#19 0x000055b96d1907bf in pfs_spawn_thread (arg=0x55b970e67578)at /test/server_opt/storage/perfschema/pfs.cc:2198
#20 0x0000146edac9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
#21 0x0000146edad29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78
Reproducible on 10.5, 11.1, 11.2 and 11.7, on optimized and debug builds. All stacks seen thus far accross versions and build types:
SIGSEGV|st_join_table::choose_best_splitting|best_access_path|best_extension_by_limited_search|best_extension_by_limited_search
SIGSEGV|st_join_table::choose_best_splitting|best_access_path|get_costs_for_tables|best_extension_by_limited_search
Issue generally tends to repeat around 500 MTR rounds, though it varies a bit, and I have seen a 5000 rounds MTR run on 11.7 opt not crash, only to crash after 200 rounds on a repeat run.
test case from
MDEV-32624- reproducible after 10.5 e87440b (MDEV-26301)Crash is repeatable on 10.5-10.11, but not on 11.0+
--source include/have_innodb.inc
`quantity` mediumint(9),
) engine=innodb;
) engine=innodb;
) backups
WHERE
);
Version: '10.5.20-MariaDB-debug-log'
231030 15:51:48 [ERROR] mysqld got signal 11 ;
Server version: 10.5.20-MariaDB-debug-log source revision: e87440b79ec6f3e3ed2e6639a3239a4d02630b7e
sql/signal_handler.cc:241(handle_fatal_signal)[0x55a1094053ea]
sigaction.c:0(__restore_rt)[0x7feeb8d12420]
sql/opt_split.cc:1047(st_join_table::choose_best_splitting(unsigned int, unsigned long long, unsigned long long*))[0x55a10924134d]
sql/sql_select.cc:7555(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x55a108cca97c]
sql/sql_select.cc:9869(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd798c]
sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]
sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]
sql/sql_select.cc:9037(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x55a108cd36f7]
sql/sql_select.cc:8599(choose_plan(JOIN*, unsigned long long))[0x55a108cd13c3]
sql/sql_select.cc:5738(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55a108cbc0e7]
sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55a108c98b66]
sql/sql_select.cc:1700(JOIN::optimize())[0x55a108c9203f]
sql/sql_select.cc:4830(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55a108cb2ea6]
sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a108c83d35]
sql/sql_parse.cc:6331(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55a108be98c9]
sql/sql_parse.cc:4008(mysql_execute_command(THD*))[0x55a108bd863c]
sql/sql_parse.cc:8106(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a108bf4bc3]
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55a108bca819]
sql/sql_parse.cc:1375(do_command(THD*))[0x55a108bc718b]
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55a109023ea7]
sql/sql_connect.cc:1320(handle_one_connection)[0x55a10902380b]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a109cad45a]
nptl/pthread_create.c:478(start_thread)[0x7feeb8d06609]
Query (0x62b0000852a8): SELECT backups.id_product, quantity_backup, quantity_bl
FROM (
SELECT id_product, SUM(quantity) as quantity_backup
FROM`table1`
) backups
INNER JOIN (
SELECT id_product
FROM`table1`
GROUP BY id_product
) normals ON (normals.id_product = backups.id_product)
LEFT JOIN (
SELECT quantity as quantity_bl, id_product FROM table1
) bl ON (backups.id_product = bl.id_product )
WHERE
backups.id_product IN (
SELECT id_product
FROM table2
)