[MDEV-28620] Server crash in /sql/item_subselect.cc:812 in Item_subselect::get_cache_parameters(List<Item>&) Created: 2022-05-19  Updated: 2024-01-15

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.35, 10.3, 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Critical
Reporter: Shihao Wen Assignee: Rex Johnston
Resolution: Unresolved Votes: 0
Labels: fuzzer, not-10.4

Attachments: HTML File 305_stack    
Issue Links:
Relates
relates to MDEV-28621 eliminated subquery: Server crash in ... Confirmed
relates to MDEV-30842 Item_subselect::get_cache_parameters ... Stalled

 Description   

poc:

CREATE TABLE v1374 ( v1375 VARCHAR ( 1 ) CHECK ( v1375 NOT LIKE 84979020.000000 ) , v1376 INT , v1377 INT , v1378 INT , UNIQUE INDEX v1379 ( v1376 , v1378 ) ) ;
 
 CREATE UNIQUE INDEX v1380 USING BTREE ON v1374 ( v1378 ASC ) ;
 
 INSERT INTO v1374 ( v1378 ) VALUES ( -128 ) , ( 8 ) ;
 
 UPDATE v1374 SET v1375 = NULL WHERE v1376 BETWEEN -2147483648 AND 48 ;
 
 SELECT v1377 FROM v1374 WHERE EXISTS ( SELECT v1375 , 'x' FROM v1374 GROUP BY ( SELECT ( v1375 NOT IN ( 16 , 19946199.000000 NOT BETWEEN 'x' AND 'x' ) AND v1378 NOT IN ( -1 % v1376 ) ) , - 'x' >= v1378 AS v1381 FROM v1374 UNION SELECT v1377 , v1375 FROM v1374 WHERE ( v1376 , ( 13774910.000000 % ( ( NOT ( v1378 IS NULL ) ) ) + v1377 ) ) NOT IN ( SELECT ( v1376 % v1377 <= v1377 ) , -1 FROM v1374 ) LIMIT 1 OFFSET 1 ) IN ( SELECT v1375 , ( SELECT v1376 FROM ( SELECT DISTINCT ( 'x' / v1376 = v1377 + CASE v1378 WHEN TRUE THEN -1 ELSE v1378 END OR v1378 = v1376 OR v1376 = v1375 ) % 42 , ( v1376 = 0 OR v1376 > 'x' ) FROM v1374 WHERE v1375 = 2147483647 AND ( v1376 = -128 OR v1378 = 64 OR v1377 = 85 ) ) AS v1382 WHERE v1375 = v1378 ) AS v1383 FROM v1374 ) , v1378 ORDER BY v1376 + ( ( SELECT v1376 FROM v1374 WHERE ( v1378 , ( 36 < 'x' ) ) NOT IN ( SELECT ( v1375 % v1377 <= v1376 ) , 0 FROM v1374 ) LIMIT 1 OFFSET 1 ) * 78 BETWEEN ( SELECT v1375 FROM v1374 WHERE ( 50 , ( v1377 < 'x' ) ) IN ( SELECT ( v1375 % v1377 <= v1376 ) , 91 FROM v1374 ) ) * 'x' AND 87 ) , v1376 ) ;

output:
SUMMARY: AddressSanitizer: SEGV /sql/item_subselect.cc:812 in Item_subselect::get_cache_parameters(List<Item>&)

The full error log is in the attachment.

 Comments   
Comment by Daniel Black [ 2022-05-19 ]

Confirmed on 10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243

Comment by Alice Sherepa [ 2022-05-25 ]

reproducible on 10.3, but not on 10.4+

CREATE TABLE t1 ( a int);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
SELECT EXISTS 
( SELECT 1 FROM t1 GROUP BY 1 IN (SELECT a FROM t1) 
	ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))   
);

10.3 7d3d3838c1b8af98a9704

 
 
 
220525 11:32:30 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.3.36-MariaDB-debug-log
 
 
 
mysys/stacktrace.c:174(my_print_stacktrace)[0x55671bfb1af1]
 
sql/signal_handler.cc:221(handle_fatal_signal)[0x55671ac0e742]
 
sql/item_subselect.cc:812(Item_subselect::get_cache_parameters(List<Item>&))[0x55671ae27052]
 
sql/item_cmpfunc.cc:1458(Item_in_optimizer::get_cache_parameters(List<Item>&))[0x55671acd9d9d]
 
sql/item.cc:8738(Item_cache_wrapper::init_on_demand())[0x55671aca0811]
 
sql/item.cc:8863(Item_cache_wrapper::check_cache())[0x55671aca1686]
 
sql/item.cc:8898(Item_cache_wrapper::save_val(Field*))[0x55671aca1b7f]
 
sql/item.h:5258(Item_cache_wrapper::save_in_result_field(bool))[0x55671acbbab0]
 
sql/sql_select.cc:24989(copy_funcs(Item**, THD const*))[0x55671a5f4806]
 
sql/sql_select.cc:21303(end_write(JOIN*, st_join_table*, bool))[0x55671a5d9810]
 
sql/sql_select.cc:27989(AGGR_OP::put_record(bool))[0x55671a60c555]
 
sql/sql_select.h:1030(AGGR_OP::put_record())[0x55671a6194cb]
 
sql/sql_select.cc:19607(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x55671a5cce25]
 
sql/sql_select.cc:20112(evaluate_join_record(JOIN*, st_join_table*, int))[0x55671a5cf5b1]
 
sql/sql_select.cc:19885(sub_select(JOIN*, st_join_table*, bool))[0x55671a5cdec9]
 
sql/sql_select.cc:19423(do_select(JOIN*, Procedure*))[0x55671a5cbe88]
 
sql/sql_select.cc:4151(JOIN::exec_inner())[0x55671a55ea6d]
 
sql/sql_select.cc:3946(JOIN::exec())[0x55671a55c3ea]
 
sql/item_subselect.cc:4026(subselect_single_select_engine::exec())[0x55671ae4b819]
 
sql/item_subselect.cc:791(Item_subselect::exec())[0x55671ae26bac]
 
sql/item_subselect.cc:1729(Item_exists_subselect::val_int())[0x55671ae31111]
 
sql/sql_type.cc:5449(Type_handler::Item_send_longlong(Item*, Protocol*, st_value*) const)[0x55671a9b0454]
 
sql/sql_type.h:2499(Type_handler_longlong::Item_send(Item*, Protocol*, st_value*) const)[0x55671a9b9964]
 
sql/item.h:885(Item::send(Protocol*, st_value*))[0x55671a20244c]
 
sql/protocol.cc:1000(Protocol::send_result_set_row(List<Item>*))[0x55671a1f4bc1]
 
sql/sql_class.cc:3049(select_send::send_data(List<Item>&))[0x55671a39047b]
 
sql/sql_select.cc:4025(JOIN::exec_inner())[0x55671a55d65c]
 
sql/sql_select.cc:3946(JOIN::exec())[0x55671a55c3ea]
 
sql/sql_select.cc:4356(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*))[0x55671a55fee8]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55671a536449]
 
sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55671a4a7191]
 
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x55671a4951cc]
 
sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55671a4b0eee]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55671a487dcb]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x55671a48490e]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55671a857ef1]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x55671a8577ab]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55671be87209]
 
nptl/pthread_create.c:478(start_thread)[0x7f7c61a24609]
 
 
 
Query (0x62b000000290): SELECT EXISTS 
( SELECT 1 FROM t1 GROUP BY 1 IN (SELECT a FROM t1) 
ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))   
)

Comment by Alice Sherepa [ 2022-05-25 ]

similar test --but fails with assertion `!eliminated' in Item_subselect::exec() (might be related to MDEV-28621)

CREATE TABLE t1 ( a int);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
SELECT EXISTS 
( SELECT 1 FROM t1 GROUP BY (SELECT a FROM t1) 
	ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))   
);

10.3 7d3d3838c1b8af98a9704

 
mysqld: /10.3/src/sql/item_subselect.cc:766: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
 
220525 11:39:09 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.3.36-MariaDB-debug-log
 
 
 
sql/signal_handler.cc:221(handle_fatal_signal)[0x55fcb7088742]
 
sql/item_subselect.cc:768(Item_subselect::exec())[0x55fcb72a06fa]
 
sql/item_subselect.cc:1415(Item_singlerow_subselect::val_int())[0x55fcb72a6c77]
 
sql/item.cc:7002(Item::save_int_in_field(Field*, bool))[0x55fcb7109815]
 
sql/sql_type.cc:2592(Type_handler_int_result::Item_save_in_field(Item*, Field*, bool) const)[0x55fcb6e204b6]
 
sql/item.cc:7012(Item::save_in_field(Field*, bool))[0x55fcb71099f9]
 
sql/item.h:2903(Item_result_field::save_in_result_field(bool))[0x55fcb67c0f13]
 
sql/sql_select.cc:24989(copy_funcs(Item**, THD const*))[0x55fcb6a6e806]
 
sql/sql_select.cc:21303(end_write(JOIN*, st_join_table*, bool))[0x55fcb6a53810]
 
sql/sql_select.cc:27989(AGGR_OP::put_record(bool))[0x55fcb6a86555]
 
sql/sql_select.h:1030(AGGR_OP::put_record())[0x55fcb6a934cb]
 
sql/sql_select.cc:19607(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x55fcb6a46e25]
 
sql/sql_select.cc:20112(evaluate_join_record(JOIN*, st_join_table*, int))[0x55fcb6a495b1]
 
sql/sql_select.cc:19885(sub_select(JOIN*, st_join_table*, bool))[0x55fcb6a47ec9]
 
sql/sql_select.cc:19423(do_select(JOIN*, Procedure*))[0x55fcb6a45e88]
 
sql/sql_select.cc:4151(JOIN::exec_inner())[0x55fcb69d8a6d]
 
sql/sql_select.cc:3946(JOIN::exec())[0x55fcb69d63ea]
 
sql/item_subselect.cc:4026(subselect_single_select_engine::exec())[0x55fcb72c5819]
 
sql/item_subselect.cc:791(Item_subselect::exec())[0x55fcb72a0bac]
 
sql/item_subselect.cc:1729(Item_exists_subselect::val_int())[0x55fcb72ab111]
 
sql/sql_type.cc:5449(Type_handler::Item_send_longlong(Item*, Protocol*, st_value*) const)[0x55fcb6e2a454]
 
sql/sql_type.h:2499(Type_handler_longlong::Item_send(Item*, Protocol*, st_value*) const)[0x55fcb6e33964]
 
sql/item.h:885(Item::send(Protocol*, st_value*))[0x55fcb667c44c]
 
sql/protocol.cc:1000(Protocol::send_result_set_row(List<Item>*))[0x55fcb666ebc1]
 
sql/sql_class.cc:3049(select_send::send_data(List<Item>&))[0x55fcb680a47b]
 
sql/sql_select.cc:4025(JOIN::exec_inner())[0x55fcb69d765c]
 
sql/sql_select.cc:3946(JOIN::exec())[0x55fcb69d63ea]
 
sql/sql_select.cc:4356(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*))[0x55fcb69d9ee8]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55fcb69b0449]
 
sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55fcb6921191]
 
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x55fcb690f1cc]
 
sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55fcb692aeee]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55fcb6901dcb]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x55fcb68fe90e]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55fcb6cd1ef1]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x55fcb6cd17ab]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55fcb8301209]
 
nptl/pthread_create.c:478(start_thread)[0x7f1579c8d609]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530ea0)
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f1579bb0133]
 
 
 
Query (0x62b000000290): SELECT EXISTS 
( SELECT 1 FROM t1 GROUP BY (SELECT a FROM t1) 
ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))   
)
 
 

Comment by Alice Sherepa [ 2023-01-24 ]

The issue is not reproducible on current 10.3 7a98d232e42b66efc759d584b0

But on 10.3.36 the initial test case was failing as in MDEV-29350 (st_select_lex_node::exclude_from_tree/st_select_lex_node::exclude/Item_subselect::eliminate_subselect_processor)...
and that assertion is fixed now, but after running the test from the description I'm getting Assertion `status_var.local_memory_used == 0 || !debug_assert_on_not_freed_memory' failed during shutdown. (10.3-10.11)

Comment by Rex Johnston [ 2023-12-04 ]

Unable to reproduce as at 9a8b1f2ac41, 2023-12-05, 10.4.33.

Comment by Sergei Petrunia [ 2024-01-08 ]

Re-closing as "Cannot reproduce"

Comment by Roel Van de Paar [ 2024-01-15 ]

The bug is not fixed. Still reproducible as per alice, and hereby confirmed. This is 11.4 Trunk of 27/12/23:

11.4.0 9bd95e914f3f12d0d9d93e7a1f2c49e6e8841f17 (Debug)

 
2024-01-15 15:28:58 0 [Note] /test/MD271223-mariadb-11.4.0-linux-x86_64-dbg/bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown
 
mariadbd: /test/11.4_dbg/sql/sql_class.cc:1747: virtual THD::~THD(): Assertion `status_var.local_memory_used == 0 || !debug_assert_on_not_freed_memory' failed.

This can be obtained by running (for example) the original t/c in the CLI, and adding 'SHUTDOWN;'. Then find bug in error log.

Comment by Roel Van de Paar [ 2024-01-15 ]

10.4.33, 27/12/23, debug, same setup:

10.4.33 1b747ffd05dd524f8d43b35a2b583dc4c00d767b (Debug)

 
2024-01-15 15:35:25 0 [Note] /test/MD271223-mariadb-10.4.33-linux-x86_64-dbg/bin/mariadbd: ready for connections.
 
Version: '10.4.33-MariaDB-debug'  socket: '/test/MD271223-mariadb-10.4.33-linux-x86_64-dbg/socket.sock'  port: 10690  MariaDB Server
 
2024-01-15 15:35:30 0 [Note] /test/MD271223-mariadb-10.4.33-linux-x86_64-dbg/bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown
 
2024-01-15 15:35:30 0 [Note] Event Scheduler: Purging the queue. 0 events
 
mariadbd: /test/10.4_dbg/sql/sql_class.cc:1732: virtual THD::~THD(): Assertion `status_var.local_memory_used == 0 || !debug_assert_on_not_freed_memory' failed.
 
2024-01-15 15:35:30 0 [Note] InnoDB: FTS optimize thread exiting.

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