[MDEV-16517] Join using subselects with in clause containing data element crashes with signal 6 Created: 2018-06-19  Updated: 2020-08-25  Resolved: 2018-06-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 10.2.15, 10.2
Fix Version/s: 10.2.16

Type: Bug Priority: Blocker
Reporter: Juan Assignee: Galina Shalygina (Inactive)
Resolution: Fixed Votes: 0
Labels: regression
Environment:

CentOS 7.4.1708


 Description   

Server crashes with signal 6 every time when a specific set of queries is run:

use test;
 
 
 
DROP TABLE IF EXISTS SomeTable;
 
CREATE TABLE `SomeTable` (
 
	`Id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 
	INDEX `Index 1` (`Id`)
 
)
 
ENGINE=InnoDB;
 
 
 
INSERT INTO `SomeTable` (`Id`) VALUES (1);
 
 
 
SET @id = 1;
 
SELECT S.Id FROM (
 
		SELECT m.Id FROM SomeTable m
 
	WHERE @id IN (0, m.Id)
 
	GROUP BY m.Id -- CRASH !!!!
 
) AS M
 
INNER JOIN (
 
	SELECT s.Id FROM SomeTable s
 
	WHERE @id IN (0, s.Id) -- CRASH !!!!
 
) AS S
 
ON S.Id = M.Id;

Statement crashes every time regardless of the value of @id, so long as there is a static value and a data element included in the in clause, and the table is not empty.

Notes:

  • happens only on CentOS and RedHat - not on Debian / Ubuntu
  • happens with default and customized configurations
  • does not happen in any tested 10.1 or 10.3 version of the server, or any other tested 10.2 release; only 10.2.15
  • appens with InnoDB or Aria storage engines.
  • only happens with data in the table.
  • happens whether primary key is defined or not.

Version: '10.2.15-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 
pure virtual method called
 
terminate called without an active exception
 
180618 23:10:46 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed,
 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.2.15-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=7
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467238 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x55b18989bb78
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7ff7019c4d70 thread_stack 0x49000
 
mysys/stacktrace.c:268(my_print_stacktrace)[0x55b18691ff9e]
 
sql/signal_handler.cc:168(handle_fatal_signal)[0x55b1863aba05]
 
sigaction.c:0(__restore_rt)[0x7ff7194d05e0]
 
:0(__GI_raise)[0x7ff7179dd1f7]
 
:0(__GI_abort)[0x7ff7179de8e8]
 
/lib64/libstdc++.so.6(_ZN9__gnu_cxx27__verbose_terminate_handlerEv+0x165)[0x7ff7180cdac5]
 
/lib64/libstdc++.so.6(+0x5ea36)[0x7ff7180cba36]
 
/lib64/libstdc++.so.6(+0x5ea63)[0x7ff7180cba63]
 
/lib64/libstdc++.so.6(+0x5f5cf)[0x7ff7180cc5cf]
 
sql/item_cmpfunc.cc:4405(Item_func_in::val_int())[0x55b1863eb587]



 Comments   
Comment by Alice Sherepa [ 2018-06-19 ]

Reproducible on MariaDB 10.2

with MyIsam:

CREATE TABLE t1 (id int);
 
INSERT INTO t1 VALUES (1);
 
 
SELECT * FROM 
(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id) GROUP BY t1.id) AS M
 
 JOIN 
(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id)) AS S
 
ON S.id = M.id;
 
 
 
DROP TABLE t1;


Thread 1 (Thread 0x7fd284056700 (LWP 3037)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000562623a70175 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477
 
#2  0x000056262330a4b9 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x0000562623355a85 in Item_func_in::val_int (this=0x7fd234015a40) at /home/alice/git/10.2/sql/item_cmpfunc.cc:4420
 
#5  0x00005626233824b2 in eval_const_cond (cond=0x7fd234015a40) at /home/alice/git/10.2/sql/item_func.cc:80
 
#6  0x00005626231009c2 in Item::remove_eq_conds (this=0x7fd234015a40, thd=0x7fd234000b00, cond_value=0x7fd284053b04, top_level_arg=false) at /home/alice/git/10.2/sql/sql_select.cc:15785
 
#7  0x0000562623100191 in Item_cond::remove_eq_conds (this=0x7fd23407a698, thd=0x7fd234000b00, cond_value=0x7fd234017088, top_level_arg=true) at /home/alice/git/10.2/sql/sql_select.cc:15608
 
#8  0x00005626230e4a5e in make_join_statistics (join=0x7fd234016d78, tables_list=..., keyuse_array=0x7fd234017068) at /home/alice/git/10.2/sql/sql_select.cc:4325
 
#9  0x00005626230db264 in JOIN::optimize_inner (this=0x7fd234016d78) at /home/alice/git/10.2/sql/sql_select.cc:1558
 
#10 0x00005626230d997b in JOIN::optimize (this=0x7fd234016d78) at /home/alice/git/10.2/sql/sql_select.cc:1115
 
#11 0x00005626230e2b34 in mysql_select (thd=0x7fd234000b00, tables=0x7fd234013f00, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fd234016d58, unit=0x7fd2340046a0, select_lex=0x7fd234004dd8) at /home/alice/git/10.2/sql/sql_select.cc:3768
 
#12 0x00005626230d713c in handle_select (thd=0x7fd234000b00, lex=0x7fd2340045d8, result=0x7fd234016d58, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376
 
#13 0x00005626230a291a in execute_sqlcom_select (thd=0x7fd234000b00, all_tables=0x7fd234013f00) at /home/alice/git/10.2/sql/sql_parse.cc:6474
 
#14 0x0000562623098646 in mysql_execute_command (thd=0x7fd234000b00) at /home/alice/git/10.2/sql/sql_parse.cc:3480
 
#15 0x00005626230a66f5 in mysql_parse (thd=0x7fd234000b00, rawbuf=0x7fd234012448 "SELECT * FROM \n(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id) GROUP BY t1.id) AS M\nJOIN \n(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id)) AS S\nON S.id = M.id", length=150, parser_state=0x7fd284055200, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7999
 
#16 0x0000562623093eb9 in dispatch_command (command=COM_QUERY, thd=0x7fd234000b00, packet=0x7fd23408ca61 "SELECT * FROM \n(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id) GROUP BY t1.id) AS M\nJOIN \n(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id)) AS S\nON S.id = M.id", packet_length=150, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1821
 
#17 0x0000562623092815 in do_command (thd=0x7fd234000b00) at /home/alice/git/10.2/sql/sql_parse.cc:1375
 
#18 0x00005626231e2a0f in do_handle_one_connection (connect=0x5626268bbab0) at /home/alice/git/10.2/sql/sql_connect.cc:1335
 
#19 0x00005626231e278f in handle_one_connection (arg=0x5626268bbab0) at /home/alice/git/10.2/sql/sql_connect.cc:1241
 
#20 0x0000562623a0d8a6 in pfs_spawn_thread (arg=0x56262681f6a0) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862
 
#21 0x00007fd28be2a6ba in start_thread (arg=0x7fd284056700) at pthread_create.c:333
 
#22 0x00007fd28b2bf41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

with Innodb (just to make it searchable) 

CREATE TABLE t1 (id int) engine=innodb;
 
INSERT INTO t1 VALUES (1);
 
 
SELECT * FROM 
(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id) GROUP BY t1.id) AS M
 
 JOIN 
(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id)) AS S
 
ON S.id = M.id;
 
 
 
Thread 1 (Thread 0x7f1158076700 (LWP 2141)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x00005560c883f175 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477
 
#2  0x00005560c80d94b9 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x00005560c8124a85 in Item_func_in::val_int (this=0x7f1114015a40) at /home/alice/git/10.2/sql/item_cmpfunc.cc:4420
 
#5  0x00005560c80edd73 in Item::val_bool (this=0x7f1114015a40) at /home/alice/git/10.2/sql/item.cc:112
 
#6  0x00005560c8126547 in Item_cond_and::val_int (this=0x7f1114038050) at /home/alice/git/10.2/sql/item_cmpfunc.cc:5011
 
#7  0x00005560c7ed7809 in evaluate_join_record (join=0x7f1114016d58, join_tab=0x7f1114036fe8, error=0) at /home/alice/git/10.2/sql/sql_select.cc:18808
 
#8  0x00005560c7ed7435 in sub_select (join=0x7f1114016d58, join_tab=0x7f1114036fe8, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18713
 
#9  0x00005560c7ed69d0 in do_select (join=0x7f1114016d58, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18257
 
#10 0x00005560c7eb1553 in JOIN::exec_inner (this=0x7f1114016d58) at /home/alice/git/10.2/sql/sql_select.cc:3587
 
#11 0x00005560c7eb0a02 in JOIN::exec (this=0x7f1114016d58) at /home/alice/git/10.2/sql/sql_select.cc:3382
 
#12 0x00005560c7eb1bc4 in mysql_select (thd=0x7f1114000b00, tables=0x7f1114013f00, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1114016d38, unit=0x7f11140046a0, select_lex=0x7f1114004dd8) at /home/alice/git/10.2/sql/sql_select.cc:3782
 
#13 0x00005560c7ea613c in handle_select (thd=0x7f1114000b00, lex=0x7f11140045d8, result=0x7f1114016d38, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376
 
#14 0x00005560c7e7191a in execute_sqlcom_select (thd=0x7f1114000b00, all_tables=0x7f1114013f00) at /home/alice/git/10.2/sql/sql_parse.cc:6474
 
#15 0x00005560c7e67646 in mysql_execute_command (thd=0x7f1114000b00) at /home/alice/git/10.2/sql/sql_parse.cc:3480
 
#16 0x00005560c7e756f5 in mysql_parse (thd=0x7f1114000b00, rawbuf=0x7f1114012448 "SELECT * FROM \n(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id) GROUP BY t1.id) AS M\nJOIN \n(SELECT t1.id FROM t1 WHERE 1 IN (0, t1.id)) AS S\nON S.id = M.id", length=150, parser_state=0x7f1158075200, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7999
 
#17 0x00005560c7e62eb9 in dispatch_command (command=COM_QUERY, thd=0x7f1114000b00, packet=0x7f111408ca61 "", packet_length=150, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1821
 
#18 0x00005560c7e61815 in do_command (thd=0x7f1114000b00) at /home/alice/git/10.2/sql/sql_parse.cc:1375
 
#19 0x00005560c7fb1a0f in do_handle_one_connection (connect=0x5560ca6bcab0) at /home/alice/git/10.2/sql/sql_connect.cc:1335
 
#20 0x00005560c7fb178f in handle_one_connection (arg=0x5560ca6bcab0) at /home/alice/git/10.2/sql/sql_connect.cc:1241
 
#21 0x00005560c87dc8a6 in pfs_spawn_thread (arg=0x5560ca6206a0) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862
 
#22 0x00007f116607f6ba in start_thread (arg=0x7f1158076700) at pthread_create.c:333
 
#23 0x00007f116551441d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

10.2.15:

Server version: 10.2.15-MariaDB
 
 
 
stack_bottom = 0x7ff14c8d2ec8 thread_stack 0x49000
 
/10.2.15/bin/mysqld(my_print_stacktrace+0x2e)[0xde328e]
 
/10.2.15/bin/mysqld(handle_fatal_signal+0x471)[0x7e05f1]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7ff151946390]
 
linux/raise.c:54(__GI_raise)[0x7ff1506cf428]
 
stdlib/abort.c:91(__GI_abort)[0x7ff1506d102a]
 
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(_ZN9__gnu_cxx27__verbose_terminate_handlerEv+0x16d)[0x7ff150dfc84d]
 
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0x8d6b6)[0x7ff150dfa6b6]
 
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0x8d701)[0x7ff150dfa701]
 
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0x8e23f)[0x7ff150dfb23f]
 
/10.2.15/bin/mysqld(_ZN12Item_func_in7val_intEv+0x22e)[0x825b5e]
 
/10.2.15/bin/mysqld[0x6326ae]
 
/10.2.15/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x138)[0x632938]
 
/10.2.15/bin/mysqld(_ZN4JOIN10exec_innerEv+0xd0f)[0x64feaf]
 
/10.2.15/bin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x1fc)[0x64e6ac]
 
/10.2.15/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x254)[0x6502c4]
 
/10.2.15/bin/mysqld[0x5e7b24]
 
/10.2.15/bin/mysqld(_Z21mysql_execute_commandP3THD+0x1276)[0x5eb046]
 
/10.2.15/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x3a2)[0x5f4d32]
 
/10.2.15/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x26e2)[0x5f8342]
 
/10.2.15/bin/mysqld(_Z10do_commandP3THD+0x165)[0x5f8c85]
 
/10.2.15/bin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x235)[0x6ddfa5]
 
/10.2.15/bin/mysqld(handle_one_connection+0x3f)[0x6de16f]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7ff15193c6ba]
 
x86_64/clone.S:111(clone)[0x7ff1507a141d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7ff0d400efd0): SELECT S.Id FROM   (SELECT m.Id    FROM SomeTable m    WHERE @id IN (0,                  m.Id)    GROUP BY m.Id  ) AS M INNER JOIN   (SELECT s.Id    FROM SomeTable s    WHERE @id IN (0,                  s.Id)  ) AS S ON S.Id = M.Id
 
Connection ID (thread ID): 12
 
Status: NOT_KILLED

Comment by Elena Stepanova [ 2018-06-19 ]

The crash appeared on 10.2 tree with this commit:

commit 6c0f3dd3414a97266912651fb169f630a36f2659
 
Author: Galina Shalygina <galina.shalygina@mariadb.com>
 
Date:   Sat May 12 20:32:16 2018 +0200
 
 
 
    MDEV-16090: Server crash in in Item_func_in::val_int or assertion `in_item'
 
                failure upon SELECT with impossible condition
 
    
    The problem appears because of a wrong implementation of the
 
    Item_func_in::build_clone() method. It didn't clone 'array' and 'cmp_fields'
 
    fields for the cloned IN predicate and this could cause crashes.
 
    The Item_func_in::fix_length_and_dec() method was refactored and a new method
 
    named Item_func_in::create_array() was created. It allowed to create 'array'
 
    for cloned IN predicates in a proper way.

Comment by Igor Babaev [ 2018-06-19 ]

Ok to push into 10.2

Comment by Galina Shalygina (Inactive) [ 2018-06-19 ]

Pushed in 10.2

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