[MDEV-17362] SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0' failed in Item_equal::fix_fields, server crashes after 2nd execution of PS Created: 2018-10-03  Updated: 2019-04-17  Resolved: 2019-04-16

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

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: 10.4-rc

Issue Links:
Duplicate
is duplicated by MDEV-18978 Server crashes in JOIN::optimize_inn... Closed
Relates
relates to MDEV-17962 Server crashes in JOIN::optimize_inne... Closed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (pk int primary key, i1 int, i2 int, v1 varchar(1), v2 varchar(1), KEY i1 (i1), KEY v2 (v2,i1)) ENGINE=InnoDB ;
 
INSERT INTO t1 VALUES (12,1,1,'r','r');
 
 
 
CREATE TABLE t2 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ;
 
 
 
CREATE TABLE t3 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ;
 
INSERT INTO t3 VALUES (19,1,9,NULL,NULL),(20,5,6,'r','r');
 
 
 
prepare stmt from "
 
SELECT 1 FROM t1
 
WHERE t1.pk = t1.i1 
	AND ((t1.pk,t1.i1) IN (SELECT t3.pk, COUNT(t3.pk) FROM t3
 
          WHERE EXISTS (SELECT 1 FROM (t1 JOIN t3 ON (t3.i2 = t1.i1)) WHERE t3.v1 = t1.v1)));";
 
 
 
execute stmt;
 
execute stmt;


 10.4 55dd0776566000c5ea12e177df0c784b500ab7c1
 
Thread 1 (Thread 0x7f22998d7700 (LWP 2335)):
 
#0  0x00007f22a178bccb in ?? () from /lib/x86_64-linux-gnu/libgcc_s.so.1
 
#1  0x00007f22a178d668 in _Unwind_Backtrace () from /lib/x86_64-linux-gnu/libgcc_s.so.1
 
#2  0x00007f22a14c8b4f in __GI___backtrace (array=<optimized out>, size=<optimized out>) at ../sysdeps/x86_64/backtrace.c:110
 
#3  0x0000557a517d65b4 in my_print_stacktrace (stack_bottom=0x7f22998d6cf0 "", thread_stack=299008, silent=0 '\000') at /10.4/mysys/stacktrace.c:269
 
#4  0x0000557a510276d0 in handle_fatal_signal (sig=11) at /10.4/sql/signal_handler.cc:166
 
#5  <signal handler called>
 
#6  0x0000000000000002 in ?? ()
 
#7  0x0000557a50d72b52 in JOIN::optimize_inner (this=0x7f223c015b08) at /10.4/sql/sql_select.cc:1687
 
#8  0x0000557a50d71ceb in JOIN::optimize (this=0x7f223c015b08) at /10.4/sql/sql_select.cc:1448
 
#9  0x0000557a510f8c42 in Item_in_subselect::optimize (this=0x7f223c04c688, out_rows=0x7f22998d48f8, cost=0x7f22998d4900) at /10.4/sql/item_subselect.cc:770
 
#10 0x0000557a50ef2189 in setup_jtbm_semi_joins (join=0x7f223c015418, join_list=0x7f223c047430, eq_list=...) at /10.4/sql/opt_subselect.cc:5964
 
#11 0x0000557a50d72d5c in JOIN::optimize_inner (this=0x7f223c015418) at /10.4/sql/sql_select.cc:1716
 
#12 0x0000557a50d71ceb in JOIN::optimize (this=0x7f223c015418) at /10.4/sql/sql_select.cc:1448
 
#13 0x0000557a50d7bd8b in mysql_select (thd=0x7f223c000b00, tables=0x7f223c047798, wild_num=0, fields=..., conds=0x7f223c0151c8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7f223c04d1a8, unit=0x7f223c0457c8, select_lex=0x7f223c047288) at /10.4/sql/sql_select.cc:4274
 
#14 0x0000557a50d6d78e in handle_select (thd=0x7f223c000b00, lex=0x7f223c045700, result=0x7f223c04d1a8, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:382
 
#15 0x0000557a50d37e53 in execute_sqlcom_select (thd=0x7f223c000b00, all_tables=0x7f223c047798) at /10.4/sql/sql_parse.cc:6550
 
#16 0x0000557a50d2e2aa in mysql_execute_command (thd=0x7f223c000b00) at /10.4/sql/sql_parse.cc:3772
 
#17 0x0000557a50d59b81 in Prepared_statement::execute (this=0x7f223c0452a0, expanded_query=0x7f22998d5ab0, open_cursor=false) at /10.4/sql/sql_prepare.cc:4766
 
#18 0x0000557a50d57f5e in Prepared_statement::execute_loop (this=0x7f223c0452a0, expanded_query=0x7f22998d5ab0, open_cursor=false, packet=0x0, packet_end=0x0) at /10.4/sql/sql_prepare.cc:4194
 
#19 0x0000557a50d55b8d in mysql_sql_stmt_execute (thd=0x7f223c000b00) at /10.4/sql/sql_prepare.cc:3302
 
#20 0x0000557a50d2e2ef in mysql_execute_command (thd=0x7f223c000b00) at /10.4/sql/sql_parse.cc:3788
 
#21 0x0000557a50d3bc3b in mysql_parse (thd=0x7f223c000b00, rawbuf=0x7f223c014e98 "execute stmt", length=12, parser_state=0x7f22998d6470, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:8085
 
#22 0x0000557a50d28d06 in dispatch_command (command=COM_QUERY, thd=0x7f223c000b00, packet=0x7f223c179421 "execute stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1850
 
#23 0x0000557a50d27723 in do_command (thd=0x7f223c000b00) at /10.4/sql/sql_parse.cc:1395
 
#24 0x0000557a50e8fd7f in do_handle_one_connection (connect=0x557a549d8150) at /10.4/sql/sql_connect.cc:1402
 
#25 0x0000557a50e8fad0 in handle_one_connection (arg=0x557a549d8150) at /10.4/sql/sql_connect.cc:1308
 
#26 0x0000557a517662f9 in pfs_spawn_thread (arg=0x557a549df130) at /10.4/storage/perfschema/pfs.cc:1862
 
#27 0x00007f22a20256ba in start_thread (arg=0x7f22998d7700) at pthread_create.c:333
 
#28 0x00007f22a14ba41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

variations:

# returns assertion
 
prepare stmt from "
 
SELECT 1 FROM t1
 
WHERE t1.pk = t1.i1 
	AND ((t1.pk,t1.i1) IN (SELECT t3.pk, COUNT(t3.pk) FROM (t3 JOIN t2 ON (t2.v1 = t3.v2))
 
          WHERE EXISTS (SELECT 1 FROM (t1 JOIN t3 ON (t3.i2 = t1.i1)) WHERE t3.v1 = t1.v1)));";


Thread 1 (Thread 0x7f1abffff700 (LWP 2672)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x000055af031596a6 in my_write_core (sig=6) at /10.4/mysys/stacktrace.c:481
 
#2  0x000055af029aaafb in handle_fatal_signal (sig=6) at /10.4/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x00007f1acbb2e428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#5  0x00007f1acbb3002a in __GI_abort () at abort.c:89
 
#6  0x00007f1acbb26bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55af03392761 "fixed == 0", file=file@entry=0x55af03392630 "/10.4/sql/item_cmpfunc.cc", line=line@entry=6643, function=function@entry=0x55af033956c0 <Item_equal::fix_fields(THD*, Item**)::__PRETTY_FUNCTION__> "virtual bool Item_equal::fix_fields(THD*, Item**)") at assert.c:92
 
#7  0x00007f1acbb26c82 in __GI___assert_fail (assertion=0x55af03392761 "fixed == 0", file=0x55af03392630 "/10.4/sql/item_cmpfunc.cc", line=6643, function=0x55af033956c0 <Item_equal::fix_fields(THD*, Item**)::__PRETTY_FUNCTION__> "virtual bool Item_equal::fix_fields(THD*, Item**)") at assert.c:101
 
#8  0x000055af029fff52 in Item_equal::fix_fields (this=0x7f1a68016bf0, thd=0x7f1a68000b00, ref=0x7f1a68015e20) at /10.4/sql/item_cmpfunc.cc:6643
 
#9  0x000055af026f5b52 in JOIN::optimize_inner (this=0x7f1a68015c70) at /10.4/sql/sql_select.cc:1687
 
#10 0x000055af026f4ceb in JOIN::optimize (this=0x7f1a68015c70) at /10.4/sql/sql_select.cc:1448
 
#11 0x000055af02a7bc42 in Item_in_subselect::optimize (this=0x7f1a6804d780, out_rows=0x7f1abfffc8f8, cost=0x7f1abfffc900) at /10.4/sql/item_subselect.cc:770
 
#12 0x000055af02875189 in setup_jtbm_semi_joins (join=0x7f1a68015580, join_list=0x7f1a68047470, eq_list=...) at /10.4/sql/opt_subselect.cc:5964
 
#13 0x000055af026f5d5c in JOIN::optimize_inner (this=0x7f1a68015580) at /10.4/sql/sql_select.cc:1716
 
#14 0x000055af026f4ceb in JOIN::optimize (this=0x7f1a68015580) at /10.4/sql/sql_select.cc:1448
 
#15 0x000055af026fed8b in mysql_select (thd=0x7f1a68000b00, tables=0x7f1a680477d8, wild_num=0, fields=..., conds=0x7f1a68015320, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7f1a6804e370, unit=0x7f1a680457c8, select_lex=0x7f1a680472c8) at /10.4/sql/sql_select.cc:4274
 
#16 0x000055af026f078e in handle_select (thd=0x7f1a68000b00, lex=0x7f1a68045700, result=0x7f1a6804e370, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:382
 
#17 0x000055af026bae53 in execute_sqlcom_select (thd=0x7f1a68000b00, all_tables=0x7f1a680477d8) at /10.4/sql/sql_parse.cc:6550
 
#18 0x000055af026b12aa in mysql_execute_command (thd=0x7f1a68000b00) at /10.4/sql/sql_parse.cc:3772
 
#19 0x000055af026dcb81 in Prepared_statement::execute (this=0x7f1a680452a0, expanded_query=0x7f1abfffdab0, open_cursor=false) at /10.4/sql/sql_prepare.cc:4766
 
#20 0x000055af026daf5e in Prepared_statement::execute_loop (this=0x7f1a680452a0, expanded_query=0x7f1abfffdab0, open_cursor=false, packet=0x0, packet_end=0x0) at /10.4/sql/sql_prepare.cc:4194
 
#21 0x000055af026d8b8d in mysql_sql_stmt_execute (thd=0x7f1a68000b00) at /10.4/sql/sql_prepare.cc:3302
 
#22 0x000055af026b12ef in mysql_execute_command (thd=0x7f1a68000b00) at /10.4/sql/sql_parse.cc:3788
 
#23 0x000055af026bec3b in mysql_parse (thd=0x7f1a68000b00, rawbuf=0x7f1a68014e98 "execute stmt", length=12, parser_state=0x7f1abfffe470, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:8085
 
#24 0x000055af026abd06 in dispatch_command (command=COM_QUERY, thd=0x7f1a68000b00, packet=0x7f1a68179421 "execute stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1850
 
#25 0x000055af026aa723 in do_command (thd=0x7f1a68000b00) at /10.4/sql/sql_parse.cc:1395
 
#26 0x000055af02812d7f in do_handle_one_connection (connect=0x55af06912e70) at /10.4/sql/sql_connect.cc:1402
 
#27 0x000055af02812ad0 in handle_one_connection (arg=0x55af06912e70) at /10.4/sql/sql_connect.cc:1308
 
#28 0x000055af030e92f9 in pfs_spawn_thread (arg=0x55af06919e50) at /10.4/storage/perfschema/pfs.cc:1862
 
#29 0x00007f1acc76b6ba in start_thread (arg=0x7f1abffff700) at pthread_create.c:333
 
#30 0x00007f1acbc0041d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109


#3  <signal handler called>
 
#4  0x00005593b9705278 in vtable for Type_handler_date ()
 
#5  0x00005593b82e2b52 in JOIN::optimize_inner (this=0x7fa33c015de8) at /10.4/sql/sql_select.cc:1687
 
#6  0x00005593b82e1ceb in JOIN::optimize (this=0x7fa33c015de8) at /10.4/sql/sql_select.cc:1448
 
#7  0x00005593b8668c42 in Item_in_subselect::optimize (this=0x7fa33c077a48, out_rows=0x7fa38c1468f8, cost=0x7fa38c146900) at /10.4/sql/item_subselect.cc:770
 
#8  0x00005593b8462189 in setup_jtbm_semi_joins (join=0x7fa33c0156e8, join_list=0x7fa33c06dec0, eq_list=...) at /10.4/sql/opt_subselect.cc:5964
 
#9  0x00005593b82e2d5c in JOIN::optimize_inner (this=0x7fa33c0156e8) at /10.4/sql/sql_select.cc:1716
 
#10 0x00005593b82e1ceb in JOIN::optimize (this=0x7fa33c0156e8) at /10.4/sql/sql_select.cc:1448
 
#11 0x00005593b82ebd8b in mysql_select (thd=0x7fa33c000b00, tables=0x7fa33c06e420, wild_num=0, fields=..., conds=0x7fa33c0153e8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7fa33c0784c0, unit=0x7fa33c06c088, select_lex=0x7fa33c06dd18) at /10.4/sql/sql_select.cc:4274
 
#12 0x00005593b82dd78e in handle_select (thd=0x7fa33c000b00, lex=0x7fa33c06bfc0, result=0x7fa33c0784c0, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:382
 
#13 0x00005593b82a7e53 in execute_sqlcom_select (thd=0x7fa33c000b00, all_tables=0x7fa33c06e420) at /10.4/sql/sql_parse.cc:6550
 
#14 0x00005593b829e2aa in mysql_execute_command (thd=0x7fa33c000b00) at /10.4/sql/sql_parse.cc:3772
 
#15 0x00005593b82c9b81 in Prepared_statement::execute (this=0x7fa33c06bb60, expanded_query=0x7fa38c147ab0, open_cursor=false) at /10.4/sql/sql_prepare.cc:4766
 
#16 0x00005593b82c7f5e in Prepared_statement::execute_loop (this=0x7fa33c06bb60, expanded_query=0x7fa38c147ab0, open_cursor=false, packet=0x0, packet_end=0x0) at /10.4/sql/sql_prepare.cc:4194
 
#17 0x00005593b82c5b8d in mysql_sql_stmt_execute (thd=0x7fa33c000b00) at /10.4/sql/sql_prepare.cc:3302
 
#18 0x00005593b829e2ef in mysql_execute_command (thd=0x7fa33c000b00) at /10.4/sql/sql_parse.cc:3788
 
#19 0x00005593b82abc3b in mysql_parse (thd=0x7fa33c000b00, rawbuf=0x7fa33c014e98 "execute stmt", length=12, parser_state=0x7fa38c148470, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:8085
 
#20 0x00005593b8298d06 in dispatch_command (command=COM_QUERY, thd=0x7fa33c000b00, packet=0x7fa33c1791d1 "execute stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1850
 
#21 0x00005593b8297723 in do_command (thd=0x7fa33c000b00) at /10.4/sql/sql_parse.cc:1395
 
#22 0x00005593b83ffd7f in do_handle_one_connection (connect=0x5593bbd78080) at /10.4/sql/sql_connect.cc:1402
 
#23 0x00005593b83ffad0 in handle_one_connection (arg=0x5593bbd78080) at /10.4/sql/sql_connect.cc:1308
 
#24 0x00005593b8cd62f9 in pfs_spawn_thread (arg=0x5593bbd7f060) at /10.4/storage/perfschema/pfs.cc:1862
 
#25 0x00007fa393a366ba in start_thread (arg=0x7fa38c149700) at pthread_create.c:333
 
#26 0x00007fa392ecb41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109


prepare stmt from "
 
SELECT 1 FROM t1
 
WHERE t1.pk = t1.i1 AND (t1.i2 >= SOME (SELECT t1.i2 FROM t1))
 
	AND ((t1.pk,t1.i1) IN (SELECT t3.pk, COUNT(t3.pk) FROM t3
 
          WHERE EXISTS (SELECT 1 FROM (t1 JOIN t3 ON (t3.i2 = t1.i1)) WHERE t3.v1 = t1.v1)));";

Not reproducible on 10.3.

 Comments   
Comment by Oleksandr Byelkin [ 2019-04-01 ]

It is consequences of pusdown condition for IN subquery:
All new items in pushdown created on runtime-root, but it looks like they should be rolled back. But somehow the reference on the item left unrolled.

Comment by Oleksandr Byelkin [ 2019-04-02 ]

mysql_derived_reinit is not called that is why cond_pushed_into_where and cond_pushed_into_having do not work as thea weher designed.

Comment by Oleksandr Byelkin [ 2019-04-02 ]

OK, there is no derived tables, so probably cond_pushed_into_where and cond_pushed_into_having was reused somehow without changing the code.

Comment by Oleksandr Byelkin [ 2019-04-02 ]

revision-id: 11fa88ef67bea74bd13ad688938a03d5b266db64 (mariadb-10.4.3-107-g11fa88ef67b)
parent(s): a90622644c11568c2bf982492d2f47bc164ddfef
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2019-04-02 15:04:45 +0200
message:

MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0' failed in Item_equal::fix_fields, server crashes after 2nd execution of PS

Move reinitialisation of pushdown variables for every query, because it used now not only for derived tables.

—

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

Anther test case for this bug:

create table t1 (a int, b int) engine = Innodb;
 
create table t2 (x int, y int) engine = Innodb;
 
 
 
insert into t1 values (1,1),(2,2);
 
insert into t2 values (1,1),(2,2),(2,3);
 
 
 
prepare stmt from "
 
SELECT * FROM t1
 
WHERE a = b 
      AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);";
 
 
 
execute stmt;
 
execute stmt;

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

Sanja’s fix doesn’t fully solve the problem.
For the first stmt execution pushdown into IN subquery is made. For the second, third, … stmt executions pushdown into IN subquery is not made while it should be made.

It happens because after the first execution of stmt is_jtbm_const_tab field is set for IN subquery.
It remains the same (true) for the second execution while it should be reset.
If is_jtbm_const_tab is set (subquery is degenerated) pushdown into IN subquery is not made. So as is_jtbm_const_tab remains set after the first execution pushdown is not made for the second, ... executions.

Explanation: (copied from MDEV-17962 comments)
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.

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

To fix it for each execution of stmt for each subquery is_jtbm_const_tab should be reset.

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

Fails for MyISAM tables also.

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

For the bug describen above a separate MDEV-19269 was created

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