[MDEV-27259] Query with self join and sets of foreign keys crashes server: SIGSEGV and Assertion `key_col_info.comparator' failed, both in Item_func_in::get_func_row_mm_tree Created: 2021-12-14  Updated: 2022-08-04

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.32, 10.2, 10.3
Fix Version/s: 10.3

Type: Bug Priority: Major
Reporter: Benjamin Dauth Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 20.4

Issue Links:
Relates
relates to MDEV-24337 Server crash in DTCollation::set_repe... Confirmed
relates to MDEV-28346 UBSAN: runtime error: downcast of add... Confirmed

 Description   

I got a TABLE with an ID two foreign keys then some more columns.
For context it is a sport project with tupels representing each a result of a match between Host and Guest.

SELECT * FROM match t1 WHERE (203, 204) IN ((t1.Host, t1.Guest), (t1.Guest, t1.Host))

works as expected

SELECT * FROM match t1, match t2 WHERE (203, t2.hostgoals) IN ((t1.hostgoals, t1.guestgoals), (t1.guestgoals, t1.hostgoals))

does, too

SELECT * FROM match t1, match t2 WHERE (203, t2.Host) IN ((t1.Host, t1.Guest), (t1.Guest, t1.Host))

crashes the server

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Also after first occurence seems to have left server in corrupt state so it kept crashing without this query.

Clarifications:
"Host" and "Guest" are foreign keys to another table (same for both).
"hostgoals" and "guestgoals" are just any numbers.

 Comments   
Comment by Alice Sherepa [ 2021-12-14 ]

Thank you for the report!

I repeated it with foreigh keys, but then simplified it to indexes.
The crash is repeatable on 10.2 and 10.3, not on 10.4+

--source include/have_innodb.inc
 
 
 
CREATE TABLE t (a int, KEY (a)) engine=innodb;
 
insert into t values (1),(2),(3);  #not important
 
SELECT * FROM t , t t2 WHERE (5, t2.a) IN ((t.a,1),(2,t.a));

10.2 6b066ec33285c089ce770c2d1

 
Version: '10.2.42-MariaDB-debug-log' 
211214 17:41:17 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.2.42-MariaDB-debug-log
 
 
 
sql/signal_handler.cc:221(handle_fatal_signal)[0x55e5aee8875d]
 
sigaction.c:0(__restore_rt)[0x7fe1e318a3c0]
 
sql/opt_range.cc:7419(Item_func_in::get_func_row_mm_tree(RANGE_OPT_PARAM*, Item_row*))[0x55e5af1fdb17]
 
sql/opt_range.cc:7848(Item_func_in::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x55e5af2012a4]
 
sql/opt_range.cc:2545(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool))[0x55e5af1df93f]
 
sql/sql_select.cc:10325(make_join_select(JOIN*, SQL_SELECT*, Item*))[0x55e5ae937946]
 
sql/sql_select.cc:1797(JOIN::optimize_inner())[0x55e5ae8f45f4]
 
sql/sql_select.cc:1127(JOIN::optimize())[0x55e5ae8ed5f4]
 
sql/sql_select.cc:3835(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*))[0x55e5ae908cfb]
 
sql/sql_select.cc:361(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e5ae8e58c2]
 
sql/sql_parse.cc:6271(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e5ae85bf0f]
 
sql/sql_parse.cc:3582(mysql_execute_command(THD*))[0x55e5ae849298]
 
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e5ae865424]
 
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e5ae83e629]
 
sql/sql_parse.cc:1381(do_command(THD*))[0x55e5ae83b3f4]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x55e5aebc760e]
 
sql/sql_connect.cc:1242(handle_one_connection)[0x55e5aebc6ed1]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e5aff672d8]
 
nptl/pthread_create.c:478(start_thread)[0x7fe1e317e609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fe1e2d57293]
 
 
 
Query (0x62b000000290): SELECT * FROM t , t t2 WHERE (5, t2.a) IN ((t.a,1),(2,t.a))

there is an assertion on 10.3

10.3 375ae890c76228c1f827

 
Version: '10.3.33-MariaDB-debug-log'  
mysqld: /10.3/src/sql/opt_range.cc:7446: SEL_TREE* Item_func_in::get_func_row_mm_tree(RANGE_OPT_PARAM*, Item_row*): Assertion `key_col_info.comparator' failed.
 
211214 17:51:01 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.3.33-MariaDB-debug-log
 
 
 
sql/opt_range.cc:7447(Item_func_in::get_func_row_mm_tree(RANGE_OPT_PARAM*, Item_row*))[0x5587550610a3]
 
sql/opt_range.cc:7876(Item_func_in::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x558755064886]
 
sql/opt_range.cc:2553(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool))[0x5587550429b8]
 
sql/sql_select.cc:11065(make_join_select(JOIN*, SQL_SELECT*, Item*))[0x558754625a97]
 
sql/sql_select.cc:2233(JOIN::optimize_stage2())[0x5587545de2ad]
 
sql/sql_select.cc:2003(JOIN::optimize_inner())[0x5587545dba21]
 
sql/sql_select.cc:1519(JOIN::optimize())[0x5587545d6b61]
 
sql/sql_select.cc:4331(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*))[0x5587545f3e75]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5587545ca6f1]
 
sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55875453b5e3]
 
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x55875452961e]
 
sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x558754545340]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55875451c21d]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x558754518d60]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x5587548e9b07]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x5587548e93c1]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x558755f17225]
 
nptl/pthread_create.c:478(start_thread)[0x7f27fae44609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f27fad69293]
 
 
 
Query (0x62b000000290): SELECT * FROM t , t t2 WHERE (5, t2.a) IN ((t.a,1),(5,t.a))


CREATE TABLE t (a int, KEY (a));
 
DELETE FROM t WHERE (1,a) IN ((a,1),(1,a));

10.2 6b066ec33285c089ce770c2d1

 
Version: '10.2.42-MariaDB-debug-log'
 
211214 18:09:42 [ERROR] mysqld got signal 11 ;
 
 
 
sigaction.c:0(__restore_rt)[0x7f05364573c0]
 
sql/opt_range.cc:7419(Item_func_in::get_func_row_mm_tree(RANGE_OPT_PARAM*, Item_row*))[0x563e2a6f6b17]
 
sql/opt_range.cc:7848(Item_func_in::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x563e2a6fa2a4]
 
sql/opt_range.cc:2545(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool))[0x563e2a6d893f]
 
sql/opt_range.h:1623(SQL_SELECT::check_quick(THD*, bool, unsigned long long))[0x563e29fdbf77]
 
sql/sql_delete.cc:416(mysql_delete(THD*, TABLE_LIST*, Item*, SQL_I_List<st_order>*, unsigned long long, unsigned long long, select_result*))[0x563e2a7879fb]
 
sql/sql_parse.cc:4420(mysql_execute_command(THD*))[0x563e29d47645]
 
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x563e29d5e424]
 
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x563e29d37629]
 
sql/sql_parse.cc:1381(do_command(THD*))[0x563e29d343f4]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x563e2a0c060e]
 
sql/sql_connect.cc:1242(handle_one_connection)[0x563e2a0bfed1]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x563e2b4602d8]
 
nptl/pthread_create.c:478(start_thread)[0x7f053644b609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f0536024293]
 
Query (0x62b000000290): DELETE FROM t WHERE (1,a) IN ((a,1),(1,a))

Comment by Roel Van de Paar [ 2022-04-19 ]

Issue confirmed here also in 10.2 and 10.3, debug and optimized. A variety of stacks are produced across these 4 builds and two testcases above. UniqueID's for the same:

SIGSEGV|Item_func_in::get_func_row_mm_tree|Item_func_in::get_mm_tree|SQL_SELECT::test_quick_select|make_join_select
 
SIGSEGV|Item_func_in::get_func_row_mm_tree|SQL_SELECT::test_quick_select|make_join_select|JOIN::optimize_inner
 
SIGSEGV|Item_func_in::get_func_row_mm_tree|SQL_SELECT::test_quick_select|make_join_select|JOIN::optimize_stage2
 
SIGSEGV|Item_func_in::get_func_row_mm_tree|Item_func_in::get_mm_tree|SQL_SELECT::test_quick_select|SQL_SELECT::check_quick
 
SIGSEGV|Item_func_in::get_func_row_mm_tree|SQL_SELECT::test_quick_select|SQL_SELECT::check_quick|mysql_delete
 
key_col_info.comparator|SIGABRT|Item_func_in::get_func_row_mm_tree|Item_func_in::get_mm_tree|SQL_SELECT::test_quick_select|make_join_select
 
key_col_info.comparator|SIGABRT|Item_func_in::get_func_row_mm_tree|Item_func_in::get_mm_tree|SQL_SELECT::test_quick_select|SQL_SELECT::check_quick

For 10.4+, see MDEV-28346

Generated at Thu Feb 08 09:51:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.