[MDEV-32763] Assertion Failed at /mariadb-11.3.0/sql/item_strfunc.cc:2391 Created: 2023-11-10  Updated: 2023-12-06

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

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

Ubuntu 20.04

Issue Links:
Relates
relates to MDEV-32701 Assertion Failed at /mariadb-11.3.0/s... Closed

 Description   

Run these queries in debug build:

CREATE TABLE t0 ( c26 INT , c42 TEXT ) ;
INSERT INTO t0 VALUES ( DEFAULT , DEFAULT ) , ( DEFAULT , DEFAULT ) ;
ALTER TABLE t0 ADD COLUMN c53 INT AFTER c26 ;
INSERT INTO t0 VALUES ( -40 , -102 , -9 ) , ( 11 , 47 , 106 ) ;
SELECT t1 . c16 NOT BETWEEN RAND ( ) AND -122 AS c54 FROM ( SELECT TRIM( TRAILING FROM 45 ) AS c16 FROM t0 ) AS t1 JOIN t0 LEFT OUTER JOIN t0 AS t2 USING ( c26 ) ON RAND ( ) GROUP BY c16 , t0 . c26 , t2 . c53 HAVING t0 . c26 = ASCII ( t1 . c16 IN ( SELECT IFNULL ( t0 . c26 , -59 ) AS c54 FROM t0 ) ) ;

Will trigger Assertion Failed.
GDB info:
#0 0x00007ffff6c0c00b in raise () from /lib/x86_64-linux-gnu/libc.so.6
#1 0x00007ffff6beb859 in abort () from /lib/x86_64-linux-gnu/libc.so.6
#2 0x00007ffff6beb729 in ?? () from /lib/x86_64-linux-gnu/libc.so.6
#3 0x00007ffff6bfcfd6 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
#4 0x0000555557d15d30 in Item_func_rtrim::val_str (this=0x6290000f6408, str=0x6290001a1c10) at /home/wx/mariadb-11.3.0/sql/item_strfunc.cc:2391
#5 0x0000555556e14bef in Item::str_result (this=0x6290000f6408, tmp=0x6290001a1c10) at /home/wx/mariadb-11.3.0/sql/item.h:1794
#6 0x0000555557bbe190 in Item_direct_view_ref::str_result (this=0x6290001a1170, tmp=0x6290001a1c10) at /home/wx/mariadb-11.3.0/sql/item.cc:9526
#7 0x0000555557bc6f08 in Item_cache_str::cache_value (this=0x6290001a1b18) at /home/wx/mariadb-11.3.0/sql/item.cc:10512
#8 0x0000555557bf3310 in Item_in_optimizer::val_int (this=0x6290001a1a40) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:1577
#9 0x0000555557c706e4 in Item_int_func::val_str (this=0x6290001a1a40, str=0x62900019d070) at /home/wx/mariadb-11.3.0/sql/item_func.cc:760
#10 0x0000555557c8f975 in Item_func_ascii::val_int (this=0x62900019cfc0) at /home/wx/mariadb-11.3.0/sql/item_func.cc:3270
#11 0x00005555578cc1de in Type_handler_int_result::Item_eq_value (this=0x55555b7b6a00 <type_handler_slonglong>, thd=0x62c0001e0288, attr=0x6290001a6c38, a=0x62900019cfc0, b=0x62900019cfc0) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:8689
#12 0x0000555557c235d4 in Item_equal::add_const (this=0x6290001a6b88, thd=0x62c0001e0288, c=0x62900019cfc0) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6849
#13 0x0000555557c23c81 in Item_equal::merge_with_check (this=0x6290001a6b88, thd=0x62c0001e0288, item=0x6290001a6b88, save_merged=true) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6974
#14 0x00005555573410d3 in propagate_new_equalities (thd=0x62c0001e0288, cond=0x6290001a6b88, new_equalities=0x6290001a6e10, inherited=0x0, is_simplifiable_cond=0x7fffd162a900) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:19844
#15 0x0000555557340f3c in propagate_new_equalities (thd=0x62c0001e0288, cond=0x6290001a6d28, new_equalities=0x6290001a6e10, inherited=0x0, is_simplifiable_cond=0x7fffd162a900) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:19831
#16 0x00005555577ba9a8 in and_new_conditions_to_optimized_cond (thd=0x62c0001e0288, cond=0x6290001a6d28, cond_eq=0x62900019e130, new_conds=..., cond_value=0x62900019e008) at /home/wx/mariadb-11.3.0/sql/opt_subselect.cc:6331
#17 0x00005555572c0873 in JOIN::optimize_inner (this=0x62900019dc68) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2384
#18 0x00005555572bbba6 in JOIN::optimize (this=0x62900019dc68) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
#19 0x00005555572dd9ba in mysql_select (thd=0x62c0001e0288, tables=0x6290000f7588, fields=..., conds=0x0, og_num=3, order=0x0, group=0x6290000f8f90, having=0x62900019d098, proc_param=0x0, select_options=2165049856, result=0x62900019dc38, unit=0x62c0001e46d8, select_lex=0x6290000f5570) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5235
#20 0x00005555572ad18a in handle_select (thd=0x62c0001e0288, lex=0x62c0001e45f8, result=0x62900019dc38, setup_tables_done_option=0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:628
#21 0x00005555571ce583 in execute_sqlcom_select (thd=0x62c0001e0288, all_tables=0x6290000f7588) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:6013
#22 0x00005555571becf6 in mysql_execute_command (thd=0x62c0001e0288, is_called_from_prepared_stmt=false) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:3912
#23 0x00005555571d95e2 in mysql_parse (thd=0x62c0001e0288, rawbuf=0x6290000f52a8 "SELECT t1 . c16 NOT BETWEEN RAND ( ) AND -122 AS c54 FROM ( SELECT TRIM( TRAILING FROM 45 ) AS c16 FROM t0 ) AS t1 JOIN t0 LEFT OUTER JOIN t0 AS t2 USING ( c26 ) ON RAND ( ) GROUP BY c16 , t0 . c26 , "..., length=300, parser_state=0x7fffd162c870) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
#24 0x00005555571b1237 in dispatch_command (command=COM_QUERY, thd=0x62c0001e0288, packet=0x6290000fa289 "SELECT t1 . c16 NOT BETWEEN RAND ( ) AND -122 AS c54 FROM ( SELECT TRIM( TRAILING FROM 45 ) AS c16 FROM t0 ) AS t1 JOIN t0 LEFT OUTER JOIN t0 AS t2 USING ( c26 ) ON RAND ( ) GROUP BY c16 , t0 . c26 , "..., packet_length=303, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893
#25 0x00005555571adf7c in do_command (thd=0x62c0001e0288, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
#26 0x000055555768e557 in do_handle_one_connection (connect=0x611000057508, put_in_cache=true) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
#27 0x000055555768deb4 in handle_one_connection (arg=0x6110000573c8) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
#28 0x00005555582fa350 in pfs_spawn_thread (arg=0x618000005908) at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
#29 0x00007ffff7115609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#30 0x00007ffff6ce8133 in clone () from /lib/x86_64-linux-gnu/libc.so.6

 Comments   
Comment by Alice Sherepa [ 2023-11-10 ]

Thanks! I repeated on 10.4-11.2

Version: '10.4.32-MariaDB-debug-log'  
mysqld: /10.4/src/sql/item_strfunc.cc:2010: virtual String* Item_func_rtrim::val_str(String*): Assertion `fixed == 1' failed.
 
231110 10:47:00 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: 62d80652be7c19f4ad2bf68d6ffbb4e1eb1d77ea
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f7a3abe9fd6]
 
sql/item_strfunc.cc:2012(Item_func_rtrim::val_str(String*))[0x561e2e1f1658]
 
sql/item.h:1559(Item::str_result(String*))[0x561e2d56c86d]
 
sql/item.cc:9386(Item_direct_view_ref::str_result(String*))[0x561e2e096bec]
 
sql/item.cc:10377(Item_cache_str::cache_value())[0x561e2e09fc20]
 
sql/item_cmpfunc.cc:1587(Item_in_optimizer::val_int())[0x561e2e0cdd95]
 
sql/item_func.cc:772(Item_int_func::val_str(String*))[0x561e2e156292]
 
sql/item_func.cc:3182(Item_func_ascii::val_int())[0x561e2e178692]
 
sql/sql_type.cc:8305(Type_handler_int_result::Item_eq_value(THD*, Type_cmp_attributes const*, Item*, Item*) const)[0x561e2ddcce70]
 
sql/item_cmpfunc.cc:6718(Item_equal::add_const(THD*, Item*))[0x561e2e0ff076]
 
sql/item_cmpfunc.cc:6844(Item_equal::merge_with_check(THD*, Item_equal*, bool))[0x561e2e0ff723]
 
sql/sql_select.cc:17513(propagate_new_equalities(THD*, Item*, List<Item_equal>*, COND_EQUAL*, bool*))[0x561e2d964093]
 
sql/sql_select.cc:17497(propagate_new_equalities(THD*, Item*, List<Item_equal>*, COND_EQUAL*, bool*))[0x561e2d963efc]
 
sql/opt_subselect.cc:6089(and_new_conditions_to_optimized_cond(THD*, Item*, COND_EQUAL**, List<Item>&, Item::cond_result*))[0x561e2dd3aba2]
 
sql/sql_select.cc:2163(JOIN::optimize_inner())[0x561e2d8efee4]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x561e2d8eb205]
 
sql/sql_select.cc:4832(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*))[0x561e2d90c1ef]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x561e2d8dcc56]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x561e2d843c5c]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x561e2d8313d3]
 
sql/sql_parse.cc:8014(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x561e2d84d1d7]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x561e2d823563]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x561e2d82008e]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x561e2dc3467e]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x561e2dc33f22]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x561e2e8d1bb0]
 
nptl/pthread_create.c:478(start_thread)[0x7f7a3b104609]
 
 
 
Query (0x62b0000a1290): SELECT t1 . c16 NOT BETWEEN RAND ( ) AND -122 AS c54 FROM ( SELECT TRIM( TRAILING FROM 45 ) AS c16 FROM t0 ) AS t1 JOIN t0 LEFT OUTER JOIN t0 AS t2 USING ( c26 ) ON RAND ( ) GROUP BY c16 , t0 . c26 , t2 . c53 HAVING t0 . c26 = ASCII ( t1 . c16 IN ( SELECT IFNULL ( t0 . c26 , -59 ) AS c54 FROM t0 ) )
 
 
 



Version: '11.1.2-MariaDB' 
231110 10:48:44 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 11.1.2-MariaDB source revision: 9bc25d98209df6810f7a7d5e7dd3ae677a313ab5
 
 
 
sql/signal_handler.cc:241(handle_fatal_signal)[0x55786a2b21f7]
 
sigaction.c:0(__restore_rt)[0x7fe81d673420]
 
sql/item.cc:10836(Item_direct_view_ref::used_tables() const)[0x55786a2dd837]
 
sql/opt_table_elimination.cc:736(eliminate_tables(JOIN*))[0x55786a1bfe01]
 
sql/sql_select.cc:5693(JOIN::optimize_inner())[0x55786a0d15bc]
 
sql/sql_select.cc:1946(JOIN::optimize())[0x55786a0d32a2]
 
sql/sql_select.cc:5229(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55786a0d33a1]
 
sql/sql_select.cc:640(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x55786a0d3cc4]
 
sql/sql_parse.cc:6041(execute_sqlcom_select(THD*, TABLE_LIST*))[0x557869ef55f1]
 
sql/sql_parse.cc:3954(mysql_execute_command(THD*, bool))[0x55786a063910]
 
sql/sql_parse.cc:7787(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55786a06591b]
 
sql/sql_parse.cc:1951(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55786a067d58]
 
sql/sql_parse.cc:1407(do_command(THD*, bool))[0x55786a069283]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55786a1760c7]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x55786a176364]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55786a501e5c]
 
nptl/pthread_create.c:478(start_thread)[0x7fe81d667609]
 
 
 
Query (0x7fe7a4010c40): SELECT t1 . c16 NOT BETWEEN RAND ( ) AND -122 AS c54 FROM ( SELECT TRIM( TRAILING FROM 45 ) AS c16 FROM t0 ) AS t1 JOIN t0 LEFT OUTER JOIN t0 AS t2 USING ( c26 ) ON RAND ( ) GROUP BY c16 , t0 . c26 , t2 . c53 HAVING t0 . c26 = ASCII ( t1 . c16 IN ( SELECT IFNULL ( t0 . c26 , -59 ) AS c54 FROM t0 ) )

while simplifying I'm getting slight variations:

CREATE TABLE t0 ( a int);
 
INSERT INTO t0 VALUES (1),(2);
 
 
 
SELECT t1.x FROM ( SELECT 5 AS x) t1 JOIN t0 ON 1
 
 GROUP BY x, t0.a 
 HAVING t0.a =  ( t1.x IN (SELECT IFNULL(t0.a,4)  FROM t0 ));


mysqld: /10.4/src/sql/item.cc:10700: virtual table_map Item_direct_view_ref::used_tables() const: Assertion `fixed' failed.
 
231117 16:16:50 [ERROR] mysqld got signal 6 ;
 
 
 
 
 
Server version: 10.4.33-MariaDB-debug-log source revision: 0381197855c58e339ab5034b871ea9c0c2d61522
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f26ef5e5fd6]
 
sql/item.cc:10702(Item_direct_view_ref::used_tables() const)[0x55da3b48b4d7]
 
sql/sql_select.cc:25371(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x55da3ad8902b]
 
sql/sql_select.cc:5447(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55da3acf9e0b]
 
sql/sql_select.cc:2388(JOIN::optimize_inner())[0x55da3acda79f]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x55da3acd37b5]
 
sql/sql_select.cc:4832(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*))[0x55da3acf479f]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55da3acc5206]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55da3ac2c20c]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55da3ac19983]
 
sql/sql_parse.cc:8014(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55da3ac35787]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55da3ac0bb13]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55da3ac0863e]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55da3b01cf66]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x55da3b01c80a]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55da3bcba5ec]
 
nptl/pthread_create.c:478(start_thread)[0x7f26efb00609]
 
 
 
Query (0x62b000103290): SELECT t1.x FROM ( SELECT 5 AS x FROM t0 ) t1 JOIN t0 ON 1
 
GROUP BY x,t0.a 
HAVING t0.a =  ( t1.x IN (SELECT IFNULL(t0.a,4)  FROM t0 ))


mysqld: /10.4/src/sql/item.cc:10700: virtual table_map Item_direct_view_ref::used_tables() const: Assertion `fixed' failed.
 
231117 16:28:40 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.4.33-MariaDB-debug-log source revision: 0381197855c58e339ab5034b871ea9c0c2d61522
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7fbc62d9ffd6]
 
sql/item.cc:10702(Item_direct_view_ref::used_tables() const)[0x55ea9e8594d7]
 
sql/item.h:5174(Used_tables_and_const_cache::used_tables_and_const_cache_join(Item const*))[0x55ea9de7a16c]
 
sql/item.h:5181(Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join(Item*))[0x55ea9de7a2d1]
 
sql/item.h:5190(Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join(unsigned int, Item**))[0x55ea9de7a33e]
 
sql/item_func.h:193(Item_func::update_used_tables())[0x55ea9de7e868]
 
sql/item.h:5180(Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join(Item*))[0x55ea9de7a2be]
 
sql/item.h:5190(Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join(unsigned int, Item**))[0x55ea9de7a33e]
 
sql/item_func.h:193(Item_func::update_used_tables())[0x55ea9de7e868]
 
sql/sql_select.cc:16141(eliminate_item_equal(THD*, Item*, COND_EQUAL*, Item_equal*))[0x55ea9e1121f1]
 
sql/sql_select.cc:16315(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*, bool))[0x55ea9e112ded]
 
sql/sql_select.cc:2533(JOIN::optimize_stage2())[0x55ea9e0aa166]
 
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55ea9e0a8aaa]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x55ea9e0a17b5]
 
sql/sql_select.cc:4832(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*))[0x55ea9e0c279f]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55ea9e093206]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55ea9dffa20c]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55ea9dfe7983]
 
sql/sql_parse.cc:8014(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55ea9e003787]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55ea9dfd9b13]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55ea9dfd663e]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55ea9e3eaf66]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x55ea9e3ea80a]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55ea9f0885ec]
 
nptl/pthread_create.c:478(start_thread)[0x7fbc632ba609]
 
 
 
Query (0x62b000103290): SELECT t1.x FROM ( SELECT 5 AS x FROM t0 ) t1 JOIN t0 ON 1
 
GROUP BY t0.a 
HAVING t0.a =  ( t1.x IN (SELECT IFNULL(t0.a,4)  FROM t0 ))

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