[MDEV-15467] "IN (SELECT ..." query sometimes crashes at optimizer_search_depth=63 Created: 2018-03-05  Updated: 2023-04-27

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.28, 10.1.31
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File test40.sql    

 Description   

Can't reliably reproduce this yet, but a query of the form

SELECT count(*) FROM t1 WHERE col1 in (SELECT col2 FROM t2  INNER JOIN t3 USING(col3) WHERE col4 = 123 );

may sometimes crash with the following backtrace when setting

optimizer_search_depth=63

The backtrace is as follows:

(gdb) bt
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11)
 
    at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000561993f4c50a in my_write_core (sig=sig@entry=11)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/mysys/stacktrace.c:477
 
#2  0x0000561993b311d8 in handle_fatal_signal (sig=11)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/signal_handler.cc:296
 
#3  <signal handler called>
 
#4  0x0000561993ab7303 in optimize_semijoin_nests (join=join@entry=0x7f9a19023618, 
    all_table_map=all_table_map@entry=7)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/opt_subselect.cc:2359
 
#5  0x0000561993a0758f in make_join_statistics (join=join@entry=0x7f9a19023618, tables_list=..., 
    keyuse_array=keyuse_array@entry=0x7f9a19023958)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_select.cc:4187
 
#6  0x0000561993a0e607 in JOIN::optimize_inner (this=0x7f9a19023618)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_select.cc:1367
 
#7  0x0000561993a11908 in JOIN::optimize (this=0x7f9a19023618)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_select.cc:1045
 
#8  0x0000561993a12305 in mysql_select (thd=thd@entry=0x7f9a8139c008, 
    rref_pointer_array=rref_pointer_array@entry=0x7f9a813a02d8, tables=0x7f9a19020428, 
    wild_num=<optimized out>, fields=..., conds=0x7f9a19022d40, og_num=0, order=0x0, group=0x0, having=0x0, 
    proc_param=0x0, select_options=2147748612, result=0x7f9a19023038, unit=0x7f9a8139f948, 
    select_lex=0x7f9a813a0048)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_select.cc:3435
 
#9  0x0000561993a126f8 in mysql_explain_union (thd=thd@entry=0x7f9a8139c008, 
    unit=unit@entry=0x7f9a8139f948, result=result@entry=0x7f9a19023038)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_select.cc:24843
 
#10 0x0000561993919d5e in execute_sqlcom_select (thd=thd@entry=0x7f9a8139c008, all_tables=<optimized out>)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_parse.cc:5851
 
#11 0x00005619939c578f in mysql_execute_command (thd=thd@entry=0x7f9a8139c008)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_parse.cc:2982
 
#12 0x00005619939c791a in mysql_parse (thd=0x7f9a8139c008, rawbuf=<optimized out>, length=<optimized out>, 
    parser_state=<optimized out>)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_parse.cc:7333
 
#13 0x00005619939cad6e in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f9a8139c008, 
    packet=packet@entry=0x7f9a6f3c0009 "", packet_length=packet_length@entry=191)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_parse.cc:1484
 
#14 0x00005619939cb53a in do_command (thd=0x7f9a8139c008)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_parse.cc:1106
 
#15 0x0000561993a873dc in do_handle_one_connection (thd_arg=thd_arg@entry=0x7f9a8139c008)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_connect.cc:1349
 
#16 0x0000561993a87587 in handle_one_connection (arg=0x7f9a8139c008)
 
    at /home/hartmut/projects/mariadb/releases/mariadb-10.1.28/sql/sql_connect.cc:1261
 
#17 0x00007f9a831006ba in start_thread (arg=0x7f9a84b70b00) at pthread_create.c:333
 
#18 0x00007f9a827ab41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Hartmut Holzgraefe [ 2018-03-05 ]

Now able to reliably crash 10.1.28 and 10.1.30 with attached test40.sql

Not reproducible with 10.2.x though.

Comment by Alice Sherepa [ 2018-03-05 ]

Reproducible on 5.5-10.1, not on 10.2

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (c1 smallint(5) DEFAULT '0' PRIMARY KEY ) ENGINE=Innodb;
 
CREATE TABLE t2 (c2 int,c1 int,PRIMARY KEY (c2,c1)) ENGINE=Innodb;
 
CREATE TABLE t3 (c2 int, c3 int, PRIMARY KEY (c2)) ENGINE=Innodb;
 
 
 
INSERT INTO t2 VALUES (349,25502),(349,25636),(349,25697),(349,25698),(349,25699),(349,25700),(349,25701),(349,25702),(349,25703),
 
(349,25704),(349,25705),(349,25706),(350,25455),(350,25456),(350,25457),(350,25458),(350,25459),(350,25460),(350,25461),(350,25462),
 
(350,25463),(350,25464),(350,25465),(350,25503),(350,25504),(350,25505),(350,25506),(350,25677),(350,25678),(350,25679),(350,25680),
 
(355,25793),(356,25794),(357,25795),(358,26026);
 
 
 
SET optimizer_search_depth=63;
 
SELECT count(*) FROM t1 WHERE c1 IN (SELECT c1 FROM t2 INNER JOIN t3 USING(c2) WHERE c3 = 1 );
 
 
 
ANALYZE TABLE t1;
 
ANALYZE TABLE t2;
 
ANALYZE TABLE t3;
 
 
 
SELECT count(*) FROM t1 WHERE c1 IN (SELECT c1 FROM t2 INNER JOIN t3 USING(c2) WHERE c3 = 1 );
 
 
 
DROP TABLE IF EXISTS t1,t2,t3;


 5.5 ac3fd5acac6b3717ce206e3
 
Thread 1 (Thread 0x7f6c1ee61700 (LWP 30170)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000000000d1c181 in my_write_core (sig=11) at /home/alice/git/5.5/mysys/stacktrace.c:457
 
#2  0x00000000007dce6e in handle_fatal_signal (sig=11) at /home/alice/git/5.5/sql/signal_handler.cc:262
 
#3  <signal handler called>
 
#4  0x00000000007643ef in optimize_semijoin_nests (join=0x7f6c0e850e18, all_table_map=7) at /home/alice/git/5.5/sql/opt_subselect.cc:2382
 
#5  0x000000000064f41d in make_join_statistics (join=0x7f6c0e850e18, tables_list=..., conds=0x7f6c0e87af18, keyuse_array=0x7f6c0e851178) at /home/alice/git/5.5/sql/sql_select.cc:3814
 
#6  0x00000000006459a9 in JOIN::optimize (this=0x7f6c0e850e18) at /home/alice/git/5.5/sql/sql_select.cc:1247
 
#7  0x000000000064c9a9 in mysql_select (thd=0x7f6c17b67000, rref_pointer_array=0x7f6c17b6ad28, tables=0x7f6c0e84e418, wild_num=0, fields=..., conds=0x7f6c0e8451d8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f6c0e844418, unit=0x7f6c17b6a328, select_lex=0x7f6c17b6aa28) at /home/alice/git/5.5/sql/sql_select.cc:3104
 
#8  0x00000000006429fd in handle_select (thd=0x7f6c17b67000, lex=0x7f6c17b6a278, result=0x7f6c0e844418, setup_tables_done_option=0) at /home/alice/git/5.5/sql/sql_select.cc:323
 
#9  0x00000000006190fa in execute_sqlcom_select (thd=0x7f6c17b67000, all_tables=0x7f6c0e84e418) at /home/alice/git/5.5/sql/sql_parse.cc:4678
 
#10 0x0000000000611bb4 in mysql_execute_command (thd=0x7f6c17b67000) at /home/alice/git/5.5/sql/sql_parse.cc:2224
 
#11 0x000000000061c032 in mysql_parse (thd=0x7f6c17b67000, rawbuf=0x7f6c0e893678 "SELECT count(*) FROM t1 WHERE c1 IN (SELECT c1 FROM t2 INNER JOIN t3 USING(c2) WHERE c3 = 1 )", length=93, parser_state=0x7f6c1ee60660) at /home/alice/git/5.5/sql/sql_parse.cc:5923
 
#12 0x000000000060ef22 in dispatch_command (command=COM_QUERY, thd=0x7f6c17b67000, packet=0x7f6c16933001 "SELECT count(*) FROM t1 WHERE c1 IN (SELECT c1 FROM t2 INNER JOIN t3 USING(c2) WHERE c3 = 1 )", packet_length=93) at /home/alice/git/5.5/sql/sql_parse.cc:1066
 
#13 0x000000000060e093 in do_command (thd=0x7f6c17b67000) at /home/alice/git/5.5/sql/sql_parse.cc:793
 
#14 0x0000000000723fbb in do_handle_one_connection (thd_arg=0x7f6c17b67000) at /home/alice/git/5.5/sql/sql_connect.cc:1268
 
#15 0x0000000000723d24 in handle_one_connection (arg=0x7f6c17b67000) at /home/alice/git/5.5/sql/sql_connect.cc:1184
 
#16 0x00000000009985fd in pfs_spawn_thread (arg=0x7f6c17bd6430) at /home/alice/git/5.5/storage/perfschema/pfs.cc:1015
 
#17 0x00007f6c1e00e6ba in start_thread (arg=0x7f6c1ee61700) at pthread_create.c:333
 
#18 0x00007f6c1d6b941d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

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