[MDEV-18978] Server crashes in JOIN::optimize_inner with condition_pushdown_from_having=on upon 2nd execution of SP Created: 2019-03-20  Updated: 2019-04-09  Resolved: 2019-04-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.4.4

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Galina Shalygina (Inactive)
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-17362 SIGSEGV in JOIN::optimize_inner or As... Closed

 Description    

set optimizer_switch='condition_pushdown_from_having=on';
 
CREATE TABLE t1 ( pk int, i int);
 
INSERT INTO t1 VALUES (10,1),(14,1),(16,1),(17,1);
 
 
 
prepare stmt from 
"SELECT i FROM t1 WHERE 6 IN (SELECT MAX(pk) FROM t1) AND i = 17
 
GROUP BY i HAVING i < 1;";
 
 
 
execute stmt;
 
execute stmt;

10.4 514b305dfb2839c619a

 
#3  <signal handler called>
 
#4  0x000055c61775bc81 in JOIN::optimize_inner (this=0x7f48c80598a0) at /10.4/sql/sql_select.cc:1902
 
#5  0x000055c61775a9d6 in JOIN::optimize (this=0x7f48c80598a0) at /10.4/sql/sql_select.cc:1551
 
#6  0x000055c617b42a58 in Item_in_subselect::optimize (this=0x7f48c803a2b0, out_rows=0x7f490f567e48, cost=0x7f490f567e50) at /10.4/sql/item_subselect.cc:770
 
#7  0x000055c6178f9d5a in setup_jtbm_semi_joins (join=0x7f48c8058170, join_list=0x7f48c8039f90, eq_list=...) at /10.4/sql/opt_subselect.cc:6045
 
#8  0x000055c61775c2bf in JOIN::optimize_inner (this=0x7f48c8058170) at /10.4/sql/sql_select.cc:1970
 
#9  0x000055c61775a9d6 in JOIN::optimize (this=0x7f48c8058170) at /10.4/sql/sql_select.cc:1551
 
#10 0x000055c6177659fe in mysql_select (thd=0x7f48c8000a98, tables=0x7f48c8052da0, wild_num=0, fields=..., conds=0x7f48c805ae20, og_num=1, order=0x0, group=0x7f48c8054ff0, having=0x7f48c8055200, proc_param=0x0, select_options=2416184064, result=0x7f48c8038ba0, unit=0x7f48c80511c8, select_lex=0x7f48c8039da0) at /10.4/sql/sql_select.cc:4571
 
#11 0x000055c617755ede in handle_select (thd=0x7f48c8000a98, lex=0x7f48c8051100, result=0x7f48c8038ba0, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:424
 
#12 0x000055c61771e6c4 in execute_sqlcom_select (thd=0x7f48c8000a98, all_tables=0x7f48c8052da0) at /10.4/sql/sql_parse.cc:6601
 
#13 0x000055c61771379e in mysql_execute_command (thd=0x7f48c8000a98) at /10.4/sql/sql_parse.cc:3889
 
#14 0x000055c617740fef in Prepared_statement::execute (this=0x7f48c8090428, expanded_query=0x7f490f569390, open_cursor=false) at /10.4/sql/sql_prepare.cc:4756
 
#15 0x000055c61773f5cc in Prepared_statement::execute_loop (this=0x7f48c8090428, expanded_query=0x7f490f569390, open_cursor=false, packet=0x0, packet_end=0x0) at /10.4/sql/sql_prepare.cc:4242
 
#16 0x000055c61773d1d9 in mysql_sql_stmt_execute (thd=0x7f48c8000a98) at /10.4/sql/sql_prepare.cc:3359
 
#17 0x000055c6177137e3 in mysql_execute_command (thd=0x7f48c8000a98) at /10.4/sql/sql_parse.cc:3905
 
#18 0x000055c61772261c in mysql_parse (thd=0x7f48c8000a98, rawbuf=0x7f48c803a510 "execute stmt", length=12, parser_state=0x7f490f569ff0, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:8154
 
#19 0x000055c61770d8ec in dispatch_command (command=COM_QUERY, thd=0x7f48c8000a98, packet=0x7f48c8009da9 "execute stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1832
 
#20 0x000055c61770c080 in do_command (thd=0x7f48c8000a98) at /10.4/sql/sql_parse.cc:1365
 
#21 0x000055c61788bf1b in do_handle_one_connection (connect=0x55c61bd6bc68) at /10.4/sql/sql_connect.cc:1398
 
#22 0x000055c61788bc59 in handle_one_connection (arg=0x55c61bd6bc68) at /10.4/sql/sql_connect.cc:1301
 
#23 0x000055c61821a227 in pfs_spawn_thread (arg=0x55c61bd204f8) at /10.4/storage/perfschema/pfs.cc:1862
 
#24 0x00007f491becb6ba in start_thread (arg=0x7f490f56b700) at pthread_create.c:333
 
#25 0x00007f491b15c41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Alice Sherepa [ 2019-03-20 ]

similar case, adding just to make it searchable:

    #0 0x5593b17cc88a in Item_cond::fix_fields(THD*, Item**) /10.4/sql/item_cmpfunc.cc:4802
 
    #1 0x5593b103560f in JOIN::optimize_inner() /10.4/sql/sql_select.cc:1902
 
    #2 0x5593b10322e6 in JOIN::optimize() /10.4/sql/sql_select.cc:1551
 
    #3 0x5593b18e695a in Item_in_subselect::optimize(double*, double*) /10.4/sql/item_subselect.cc:770
 
    #4 0x5593b13dbe4f in setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&) /10.4/sql/opt_subselect.cc:6045
 
    #5 0x5593b103648e in JOIN::optimize_inner() /10.4/sql/sql_select.cc:1970
 
    #6 0x5593b10322e6 in JOIN::optimize() /10.4/sql/sql_select.cc:1551
 
    #7 0x5593b104f3e2 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.4/sql/sql_select.cc:4571
 
    #8 0x5593b1026471 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.4/sql/sql_select.cc:424
 
    #9 0x5593b0faaf19 in execute_sqlcom_select /10.4/sql/sql_parse.cc:6652
 
    #10 0x5593b0f97d09 in mysql_execute_command(THD*) /10.4/sql/sql_parse.cc:3889
 
    #11 0x5593b0ffa4ad in Prepared_statement::execute(String*, bool) /10.4/sql/sql_prepare.cc:4743
 
    #12 0x5593b0ff62e8 in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /10.4/sql/sql_prepare.cc:4229
 
    #13 0x5593b0ff0d59 in mysql_sql_stmt_execute(THD*) /10.4/sql/sql_prepare.cc:3346
 
    #14 0x5593b0f97d4e in mysql_execute_command(THD*) /10.4/sql/sql_parse.cc:3905
 
    #15 0x5593b0fb33a6 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.4/sql/sql_parse.cc:8205
 
    #16 0x5593b0f8bb66 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.4/sql/sql_parse.cc:1829
 
    #17 0x5593b0f88ab1 in do_command(THD*) /10.4/sql/sql_parse.cc:1358
 
    #18 0x5593b12fd49c in do_handle_one_connection(CONNECT*) /10.4/sql/sql_connect.cc:1399
 
    #19 0x5593b12fce66 in handle_one_connection /10.4/sql/sql_connect.cc:1302
 
    #20 0x7f99792d26b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #21 0x7f997856341c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

Comment by Galina Shalygina (Inactive) [ 2019-04-09 ]

Why this bug appears:

What is done on the first execution of stmt statement:

1. Pushdown from HAVING into WHERE is made.
i < 1 is pushed into WHERE clause.

2. Then pushdown into IN subquery is made. Condition from WHERE clause is saved to be pushed into IN subquery later. It is saved in cond_pushed_into_where field.

3. IN subquery is optimized. cond_pushed_into_where is pushed into IN subqueries WHERE clause.

4. IN subquery is executed. It is said to be degenerated and special flag is_jtbm_const_tab is set.

On the second execution of stmt statement 1 step is repeated.

On the 2 step it is checked if is_jtbm_const_tab is set. And it is set, it remains the same after the first execution. So pushdown into IN subquery is not made while is should be made.

At the same time cond_pushed_into_where remains after the first execution. On the 3 step when optimize_cond() is called for IN subquery cond_pushed_into_where is tried to be joined to IN subquery. As it remains as it was on the first execution it causes crash.

This bug is the same as MDEV-17362.

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