[MDEV-33126] virtual bool Item_subselect::exec(): Assertion `!eliminated' failed Created: 2023-12-27  Updated: 2023-12-28  Resolved: 2023-12-28

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Sergei Golubchik Assignee: Sergei Petrunia
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-28621 eliminated subquery: Server crash in ... Confirmed
Relates
relates to MDEV-29411 SIGSEGV's st_select_lex_unit::set_lim... Confirmed

 Description    

CREATE TABLE v0 ( v1 INT , v2 INT , v3 INT);
 
INSERT INTO v0 VALUES (1, 2, 3);
 
INSERT INTO v0 VALUES (3, 2, 1);
 
CREATE TABLE v4 ( v5 INT , v6 INT , v7 INT);
 
INSERT INTO v4 VALUES (4, 5, 6);
 
INSERT INTO v4 VALUES (6, 5, 4);
 
CREATE TABLE v8 ( v9 TEXT ( 1 ) CHECK ( CASE v9 WHEN -1 THEN v9 ELSE 18237335.000000 END AND v9 <= -128 ) );
 
INSERT INTO v0 ( v1 ) VALUES ( 39 );
 
UPDATE v0 JOIN v4 SET v2 = v7 NOT IN ( ( SELECT NULL FROM ( SELECT ( SELECT v6 FROM v4 WHERE v7 != 6 GROUP BY v5 ) != v5 AS v10 , v7 AS v11 FROM v4 ) AS v12 WHERE v10 = 33 AND v5 < 76 GROUP BY - 'x' >= v10 ) ) AND v1 NOT IN ( 'x' , 28 , -32768 , -1 , 55 , 12 ) WHERE v2 * 80201461.000000;

leads to

mariadbd: /home/serg/Abk/mysql/sql/item_subselect.cc:787: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
 
231227 12:58:01 [ERROR] mysqld got signal 6 ;
 
Sorry, we probably made a mistake, and this is a bug.
 
 
 
Your assistance in bug reporting will enable us to fix this for the next release.
 
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: 11.1.4-MariaDB-debug-log source revision: 50799752dc6306c35a47405c8f556d0b9639a1a9
 
key_buffer_size=1048576
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=1
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63964 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fa4fc000dc8
 
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 = 0x7fa50d499c78 thread_stack 0x49000
 
mysys/stacktrace.c:215(my_print_stacktrace)[0x564e98875e00]
 
sql/signal_handler.cc:238(handle_fatal_signal)[0x564e97f9a942]
 
libc_sigaction.c:0(__restore_rt)[0x7fa512b08390]
 
nptl/pthread_kill.c:44(__pthread_kill_implementation)[0x7fa512b55acc]
 
posix/raise.c:27(__GI_raise)[0x7fa512b082f2]
 
stdlib/abort.c:81(__GI_abort)[0x7fa512af2232]
 
intl/loadmsgcat.c:1177(_nl_load_domain)[0x7fa512af2155]
 
/lib64/libc.so.6(+0x30282)[0x7fa512b01282]
 
sql/item_subselect.cc:789(Item_subselect::exec())[0x564e980b3d00]
 
sql/item_subselect.cc:1480(Item_singlerow_subselect::val_int())[0x564e980b617f]
 
sql/item_cmpfunc.cc:992(Arg_comparator::compare_int_signed())[0x564e97ff0a28]
 
sql/item_cmpfunc.h:118(Arg_comparator::compare())[0x564e98009a18]
 
sql/item_cmpfunc.cc:1852(Item_func_ne::val_int())[0x564e97ff3819]
 
sql/item.cc:8717(Item_direct_ref::val_int())[0x564e97fdb6d0]
 
sql/item.h:6140(Item_direct_view_ref::val_int())[0x564e97fe6be9]
 
sql/item_cmpfunc.cc:992(Arg_comparator::compare_int_signed())[0x564e97ff0a28]
 
sql/item_cmpfunc.h:118(Arg_comparator::compare())[0x564e98009a18]
 
sql/item_cmpfunc.cc:1828(Item_func_eq::val_int())[0x564e97ff365d]
 
sql/sql_select.cc:23709(evaluate_join_record(JOIN*, st_join_table*, int))[0x564e97c4600a]
 
sql/sql_select.cc:23608(sub_select(JOIN*, st_join_table*, bool))[0x564e97c45b83]
 
sql/sql_select.cc:23119(do_select(JOIN*, Procedure*))[0x564e97c44c3b]
 
sql/sql_select.cc:4991(JOIN::exec_inner())[0x564e97c108e1]
 
sql/sql_select.cc:4768(JOIN::exec())[0x564e97c0f8a7]
 
sql/item_subselect.cc:4174(subselect_single_select_engine::exec())[0x564e980c2728]
 
sql/item_subselect.cc:812(Item_subselect::exec())[0x564e980b3eea]
 
sql/item_subselect.cc:994(Item_in_subselect::exec())[0x564e980b4663]
 
sql/item_subselect.cc:2009(Item_in_subselect::val_bool())[0x564e980b8b38]
 
sql/item.h:1803(Item::val_bool_result())[0x564e97a04c6d]
 
sql/item_cmpfunc.cc:1712(Item_in_optimizer::val_int())[0x564e97ff2cd9]
 
sql/sql_type.cc:5090(Type_handler_int_result::Item_val_bool(Item*) const)[0x564e97e76551]
 
sql/item.h:1708(Item::val_bool())[0x564e97a04ab8]
 
sql/item_cmpfunc.cc:203(Item_func_not::val_int())[0x564e97fed7d3]
 
sql/sql_type.cc:5090(Type_handler_int_result::Item_val_bool(Item*) const)[0x564e97e76551]
 
sql/item.h:1708(Item::val_bool())[0x564e97a04ab8]
 
sql/item_cmpfunc.cc:5610(Item_cond_and::val_int())[0x564e98000a54]
 
sql/item.cc:6879(Item::save_int_in_field(Field*, bool))[0x564e97fd55b4]
 
sql/sql_type.cc:4323(Type_handler_int_result::Item_save_in_field(Item*, Field*, bool) const)[0x564e97e74c06]
 
sql/item.cc:6889(Item::save_in_field(Field*, bool))[0x564e97fd5665]
 
sql/sql_base.cc:9345(fill_record(THD*, TABLE*, Field**, List<Item>&, bool, bool))[0x564e97af1566]
 
sql/sql_update.cc:2374(multi_update::send_data(List<Item>&))[0x564e97d0ec9e]
 
sql/sql_class.h:5868(select_result_sink::send_data_with_check(List<Item>&, st_select_lex_unit*, unsigned long long))[0x564e97c662b7]
 
sql/sql_select.cc:24915(end_send(JOIN*, st_join_table*, bool))[0x564e97c490bb]
 
sql/sql_select.cc:23841(evaluate_join_record(JOIN*, st_join_table*, int))[0x564e97c463b5]
 
sql/sql_select.cc:23608(sub_select(JOIN*, st_join_table*, bool))[0x564e97c45b83]
 
sql/sql_select.cc:23841(evaluate_join_record(JOIN*, st_join_table*, int))[0x564e97c463b5]
 
sql/sql_select.cc:23608(sub_select(JOIN*, st_join_table*, bool))[0x564e97c45b83]
 
sql/sql_select.cc:23119(do_select(JOIN*, Procedure*))[0x564e97c44c3b]
 
sql/sql_select.cc:4991(JOIN::exec_inner())[0x564e97c108e1]
 
sql/sql_select.cc:4768(JOIN::exec())[0x564e97c0f8a7]
 
sql/sql_select.cc:33667(Sql_cmd_dml::execute_inner(THD*))[0x564e97c61619]
 
sql/sql_update.cc:3075(Sql_cmd_update::execute_inner(THD*))[0x564e97d117f1]
 
sql/sql_select.cc:33604(Sql_cmd_dml::execute(THD*))[0x564e97c61382]
 
sql/sql_parse.cc:4407(mysql_execute_command(THD*, bool))[0x564e97b9d870]
 
sql/sql_parse.cc:7832(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x564e97ba916f]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x564e97b95b11]
 
sql/sql_parse.cc:1405(do_command(THD*, bool))[0x564e97b9446a]
 
sql/sql_connect.cc:1415(do_handle_one_connection(CONNECT*, bool))[0x564e97d8c959]
 
sql/sql_connect.cc:1319(handle_one_connection)[0x564e97d8c6ce]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x564e982c19b0]
 
nptl/pthread_create.c:444(start_thread)[0x7fa512b53dec]
 
x86_64/clone3.S:83(clone3)[0x7fa512bccbcc]

starting from 11.1

 Comments   
Comment by Alice Sherepa [ 2023-12-28 ] 

CREATE TABLE t1 ( a int );
 
INSERT intO t1 VALUES (1),(3);
 
 
 
CREATE TABLE t2 ( i int, j int);
 
INSERT intO t2 VALUES (4, 6),(6, 4);
 
 
 
UPDATE t1 SET a = 1 IN (SELECT null FROM (SELECT (SELECT 1 FROM t2) = i AS b  FROM t2) dt WHERE b = 33 GROUP BY b );


mysqld: /10.4/src/sql/item_subselect.cc:733: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
 
231228 17:24:37 [ERROR] mysqld got signal 6 ;
 
 
 
 
 
Server version: 10.4.33-MariaDB-debug-log source revision: 1b747ffd05dd524f8d43b35a2b583dc4c00d767b
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f3935cb6fd6]
 
sql/item_subselect.cc:735(Item_subselect::exec())[0x56056d4a113e]
 
sql/item_subselect.cc:1400(Item_singlerow_subselect::val_int())[0x56056d4a7561]
 
sql/item_cmpfunc.cc:950(Arg_comparator::compare_int_signed())[0x56056d33cc3b]
 
sql/item_cmpfunc.h:104(Arg_comparator::compare())[0x56056d37e9de]
 
sql/item_cmpfunc.cc:1790(Item_func_eq::val_int())[0x56056d3467bb]
 
sql/item.cc:8543(Item_direct_ref::val_int())[0x56056d30381d]
 
sql/item.h:5953(Item_direct_view_ref::val_int())[0x56056d322367]
 
sql/item_cmpfunc.cc:950(Arg_comparator::compare_int_signed())[0x56056d33cc3b]
 
sql/item_cmpfunc.h:104(Arg_comparator::compare())[0x56056d37e9de]
 
sql/item_cmpfunc.cc:1790(Item_func_eq::val_int())[0x56056d3467bb]
 
sql/sql_select.cc:21017(evaluate_join_record(JOIN*, st_join_table*, int))[0x56056cbf5592]
 
sql/sql_select.cc:20922(sub_select(JOIN*, st_join_table*, bool))[0x56056cbf48cc]
 
sql/sql_select.cc:20443(do_select(JOIN*, Procedure*))[0x56056cbf2672]
 
sql/sql_select.cc:4625(JOIN::exec_inner())[0x56056cb8027c]
 
sql/sql_select.cc:4408(JOIN::exec())[0x56056cb7d8ac]
 
sql/item_subselect.cc:4039(subselect_single_select_engine::exec())[0x56056d4c686e]
 
sql/item_subselect.cc:758(Item_subselect::exec())[0x56056d4a15f8]
 
sql/item_subselect.cc:938(Item_in_subselect::exec())[0x56056d4a2bfc]
 
sql/item_subselect.cc:1886(Item_in_subselect::val_bool())[0x56056d4ae1d7]
 
sql/item.h:1562(Item::val_bool_result())[0x56056c7e0962]
 
sql/item_cmpfunc.cc:1673(Item_in_optimizer::val_int())[0x56056d344930]
 
sql/item.cc:6718(Item::save_int_in_field(Field*, bool))[0x56056d2f2d71]
 
sql/sql_type.cc:3847(Type_handler_int_result::Item_save_in_field(Item*, Field*, bool) const)[0x56056d029c4c]
 
sql/item.cc:6728(Item::save_in_field(Field*, bool))[0x56056d2f2f55]
 
sql/sql_base.cc:8661(fill_record(THD*, TABLE*, List<Item>&, List<Item>&, bool, bool))[0x56056c930121]
 
sql/sql_base.cc:8833(fill_record_n_invoke_before_triggers(THD*, TABLE*, List<Item>&, List<Item>&, bool, trg_event_type))[0x56056c93133c]
 
sql/sql_update.cc:1022(mysql_update(THD*, TABLE_LIST*, List<Item>&, List<Item>&, Item*, unsigned int, st_order*, unsigned long long, bool, unsigned long long*, unsigned long long*))[0x56056cd88df5]
 
sql/sql_parse.cc:4453(mysql_execute_command(THD*))[0x56056caa95fb]
 
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56056cac287f]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56056ca98a2b]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x56056ca95556]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x56056ceaa5cc]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x56056cea9e70]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x56056db480d4]
 
nptl/pthread_create.c:478(start_thread)[0x7f39361d1609]
 
 
 
Query (0x62b000062290): UPDATE t1 SET a = 1 IN (SELECT null FROM (SELECT (SELECT 1 FROM t2) = i AS b  FROM t2) dt WHERE b = 33 GROUP BY b )


 
 
CREATE TABLE t1 ( a int );
 
INSERT intO t1 VALUES (1),(3);
 
 
 
CREATE TABLE t2 ( i int, j int);
 
INSERT intO t2 VALUES (4, 6),(6, 4);
 
 
 
UPDATE t1 SET a = 1 IN (SELECT null FROM (SELECT (SELECT 5 FROM t2) AS b  FROM t2) dt WHERE b = 33 GROUP BY b );

or the same as the initial test, but with an empty table t2 (or with only 1 row) - also leads to ~MDEV-28621

CREATE TABLE t1 ( a int );
 
CREATE TABLE t2 ( i int, j int);
 
 
 
UPDATE t1 SET a = 1 IN (SELECT null FROM (SELECT (SELECT 5 FROM t2) AS b  FROM t2) dt WHERE b = 33 GROUP BY b );


231228 13:53:24 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 11.1.4-MariaDB-debug-log source revision: 50799752dc6306c35a47405c8f556d0b9639a1a9
 
 
 
sql/signal_handler.cc:238(handle_fatal_signal)[0x5620bcfc380c]
 
sigaction.c:0(__restore_rt)[0x7fdcb0f2b420]
 
sql/sql_lex.h:1421(st_select_lex::next_select())[0x5620bc5aee29]
 
sql/item_subselect.cc:584(Item_subselect::is_expensive())[0x5620bd257773]
 
sql/item_subselect.h:257(Item_subselect::is_expensive_processor(void*))[0x5620bd293360]
 
sql/item_subselect.cc:777(Item_subselect::walk(bool (Item::*)(void*), bool, void*))[0x5620bd2592cd]
 
sql/item.h:6102(Item_direct_view_ref::walk(bool (Item::*)(void*), bool, void*))[0x5620bd083781]
 
sql/item.h:2784(Item_args::walk_args(bool (Item::*)(void*), bool, void*))[0x5620bc3684e3]
 
sql/item.h:5487(Item_func_or_sum::walk(bool (Item::*)(void*), bool, void*))[0x5620bc3695fb]
 
sql/item.h:2605(Item::is_expensive())[0x5620bc2d78e2]
 
sql/item.h:1718(Item::can_eval_in_optimize())[0x5620bc368260]
 
sql/sql_select.cc:20372(Item_bool_func2::remove_eq_conds(THD*, Item::cond_result*, bool))[0x5620bc816993]
 
sql/sql_select.cc:19908(optimize_cond(JOIN*, Item*, List<TABLE_LIST>*, bool, Item::cond_result*, COND_EQUAL**, int))[0x5620bc813df8]
 
sql/sql_select.cc:2376(JOIN::optimize_inner())[0x5620bc79287a]
 
sql/sql_select.cc:1966(JOIN::optimize())[0x5620bc78e502]
 
sql/sql_lex.cc:4956(st_select_lex::optimize_unflattened_subqueries(bool))[0x5620bc615ed1]
 
sql/sql_update.cc:426(Sql_cmd_update::update_single_table(THD*))[0x5620bca1bc71]
 
sql/sql_update.cc:3071(Sql_cmd_update::execute_inner(THD*))[0x5620bca3565d]
 
sql/sql_select.cc:33604(Sql_cmd_dml::execute(THD*))[0x5620bc87c40d]
 
sql/sql_parse.cc:4407(mysql_execute_command(THD*, bool))[0x5620bc6953f0]
 
sql/sql_parse.cc:7832(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5620bc6aced3]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5620bc684ff7]
 
sql/sql_parse.cc:1405(do_command(THD*, bool))[0x5620bc681d41]
 
sql/sql_connect.cc:1415(do_handle_one_connection(CONNECT*, bool))[0x5620bcb5a878]
 
sql/sql_connect.cc:1319(handle_one_connection)[0x5620bcb5a1d5]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5620bd794584]
 
nptl/pthread_create.c:478(start_thread)[0x7fdcb0f1f609]
 
 
 
Query (0x6290001092a8): UPDATE t1 SET a = 1 IN (SELECT null FROM (SELECT (SELECT 5 FROM t2) AS b  FROM t2) dt WHERE b = 33 GROUP BY b )

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