[MDEV-32405] Early subquery exec: SEGV at /mariadb-11.3.0/sql/item_subselect.cc:4070 Created: 2023-10-10  Updated: 2023-12-11

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-32980 SQL Simplification for the SQL Query Closed

 Description   

Run these queries in release build:

CREATE TABLE t0 ( c49 DOUBLE ( 249 , 19 ) ) ;
INSERT INTO t0 VALUES ( -120 ) , ( 105 ) ;
CREATE INDEX i0 ON t0 ( c49 ) ;
INSERT INTO t0 VALUES ( ) , ( ) ;
SELECT t2 . c5 AS c22 FROM ( SELECT ~ OCT ( t0 . c49 ) << CONVERT ( -1968393419284614186 , UNSIGNED ) % RAND ( ) - RAND ( -76 ) << + EXISTS ( SELECT -126 AS c42 ) AS c15 FROM t0 ) AS t1 JOIN ( SELECT ROUND ( 88 , ORD ( -25 ) / ATAN ( 32 IN ( SELECT t0 . c49 BETWEEN -24 AND 126 AS c10 FROM t0 ) ) / UNHEX ( 57 ) = ALL ( SELECT t0 . c49 AS c18 FROM t0 ) ) NOT BETWEEN 118 AND 20 AS c5 FROM t0 ) AS t2 ON t1 . c15 = t1 . c15 WHERE LTRIM ( t1 . c15 ) % 42.121931 = -58 GROUP BY c5 , c15 HAVING c15 = 2 REGEXP EXP ( t2 . c5 ) ;

Will trigger Segmentation fault.
GDB info:
Thread 17 "mariadbd" received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7fffd1c17300 (LWP 3109)]
0x00000000015d77b0 in subselect_single_select_engine::exec (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:4070
4070 if (join->optimization_state == JOIN::NOT_OPTIMIZED)
(gdb) p join
$31 = (JOIN *) 0x0

#0 0x00000000015d77b0 in subselect_single_select_engine::exec (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:4070
#1 0x00000000015b4bab in Item_subselect::exec (this=0x6290000c5628) at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:812
#2 Item_in_subselect::exec (this=0x6290000c5628) at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:994
#3 0x00000000015be5e0 in Item_in_subselect::val_bool (this=0x6290000c5628) at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:1991
#4 0x00000000013b4fcc in Item_in_optimizer::val_int (this=0x6290000d0a70) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:1664
#5 0x000000000145d518 in Item_int_func::val_real (this=0x56c) at /home/wx/mariadb-11.3.0/sql/item_func.cc:753
#6 0x000000000146d797 in Item_func_atan::val_real (this=0x6290000c59a8) at /home/wx/mariadb-11.3.0/sql/item_func.cc:2128
#7 0x00000000014655c6 in Item_func_div::real_op (this=0x6290000c5a60) at /home/wx/mariadb-11.3.0/sql/item_func.cc:1503
#8 0x0000000001465564 in Item_func_div::real_op (this=0x6290000c5cf8) at /home/wx/mariadb-11.3.0/sql/item_func.cc:1502
#9 0x000000000137af52 in Item_cache_real::cache_value (this=0x6290000d0ed0) at /home/wx/mariadb-11.3.0/sql/item.cc:10388
#10 0x00000000013b4c68 in Item_in_optimizer::val_int (this=0x6290000d0df8) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:1577
#11 0x00000000010d6cd1 in Type_handler_int_result::Item_val_bool (this=<optimized out>, item=0x56c) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:5082
#12 0x00000000013a5d61 in Item_func_not_all::val_int (this=0x6290000c6e60) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:222
#13 0x0000000001473594 in Item_func_round::int_op (this=0x6290000c7058) at /home/wx/mariadb-11.3.0/sql/item_func.cc:2718
#14 0x00000000013bb4ad in Item::to_longlong_hybrid (this=0x6290000c7058) at /home/wx/mariadb-11.3.0/sql/item.h:1448
#15 Item_func_between::val_int_cmp_int (this=0x6290000c8b00) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:2263
#16 0x000000000145d518 in Item_int_func::val_real (this=0x56c) at /home/wx/mariadb-11.3.0/sql/item_func.cc:753
#17 0x000000000138dc33 in Item_direct_ref::val_real (this=0x6290000d5ff8) at /home/wx/mariadb-11.3.0/sql/item.cc:8672
#18 Item_direct_view_ref::val_real (this=0x6290000d5ff8) at /home/wx/mariadb-11.3.0/sql/item.h:6134
#19 0x000000000146ccd1 in Item_func_exp::val_real (this=0x6290000cbe90) at /home/wx/mariadb-11.3.0/sql/item_func.cc:2076
#20 0x000000000145cbb2 in Item_real_func::val_str (this=0x6290000cbe90, str=0x7fffd2429c80) at /home/wx/mariadb-11.3.0/sql/item_func.cc:688
#21 0x00000000013e4148 in Regexp_processor_pcre::compile (this=<optimized out>, item=<optimized out>, send_error=<optimized out>) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6083
#22 0x00000000013e5a65 in Regexp_processor_pcre::recompile (this=<optimized out>, item=0x62b00016c270) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.h:3024
#23 Item_func_regex::val_int (this=0x6290000cbf48) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6218
#24 0x00000000010fc885 in Type_handler_int_result::Item_eq_value (this=<optimized out>, thd=<optimized out>, attr=<optimized out>, a=0x6290000cbf48, b=0x6290000cbf48) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:8689
#25 0x00000000013eea64 in Item_equal::add_const (this=this@entry=0x62d0000d7430, thd=0x62b00016c270, thd@entry=0x62b00016c218, c=0x6290000916b8) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6849
#26 0x00000000013ef988 in Item_equal::merge_with_check (this=0x62d0000d7430, thd=0x62b00016c218, item=0x62d0000d7430, save_merged=true) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6974
#27 0x0000000000c814de in propagate_new_equalities (thd=<optimized out>, cond=<optimized out>, new_equalities=<optimized out>, inherited=<optimized out>, is_simplifiable_cond=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:19844
#28 0x0000000000c817b3 in propagate_new_equalities (thd=<optimized out>, cond=<optimized out>, new_equalities=<optimized out>, inherited=<optimized out>, is_simplifiable_cond=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:19831
#29 0x0000000000fdf4bf in and_new_conditions_to_optimized_cond (thd=<optimized out>, cond=<optimized out>, cond_eq=<optimized out>, new_conds=..., cond_value=<optimized out>) at /home/wx/mariadb-11.3.0/sql/opt_subselect.cc:6331
#30 0x0000000000c12ba2 in JOIN::optimize_inner (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2384
#31 0x0000000000bfc156 in JOIN::optimize (this=this@entry=0x6290000cdce0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
#32 0x0000000000be4fdf in mysql_select (thd=<optimized out>, thd@entry=0x62b00016c218, tables=<optimized out>, fields=..., conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=0x6290000cb950, having=0x6290000cd238, proc_param=0x0, select_options=<optimized out>, result=0x6290000cdcb0, unit=0x62b0001704a8, select_lex=0x6290000916b8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5235
#33 0x0000000000be4596 in handle_select (thd=thd@entry=0x62b00016c218, lex=<optimized out>, lex@entry=0x62b0001703c8, result=<optimized out>, result@entry=0x6290000cdcb0, setup_tables_done_option=<optimized out>, setup_tables_done_option@entry=0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:628
#34 0x0000000000b3df18 in execute_sqlcom_select (thd=0x62b00016c218, all_tables=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:6013
#35 0x0000000000b2cd51 in mysql_execute_command (thd=0x62b00016c218, is_called_from_prepared_stmt=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:3912
#36 0x0000000000b1fe79 in mysql_parse (thd=thd@entry=0x62b00016c218, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7fffd242ca80) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
#37 0x0000000000b19069 in dispatch_command (command=<optimized out>, thd=0x62b00016c218, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893
#38 0x0000000000b20b71 in do_command (thd=0x62b00016c218, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
#39 0x0000000000f03476 in do_handle_one_connection (connect=<optimized out>, put_in_cache=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
#40 0x0000000000f02eb9 in handle_one_connection (arg=arg@entry=0x6080019c8538) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
#41 0x0000000001a00c1b in pfs_spawn_thread (arg=0x617000005118) at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
#42 0x00007ffff79f7609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#43 0x00007ffff770f133 in clone () from /lib/x86_64-linux-gnu/libc.so.6

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

Thanks! I repeated as described on 10.4-11.3

Version: '10.4.32-MariaDB-debug-log' 
mysqld: /10.4/src/sql/item_func.h:797: virtual double Item_func_hybrid_field_type::val_real(): Assertion `fixed' failed.
 
231103 14:15:52 [ERROR] mysqld got signal 6 ;
 
 
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: b4de67da451b580989843fd63c0d248f7b8b3a53
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f5919419fd6]
 
sql/item_func.h:798(Item_func_hybrid_field_type::val_real())[0x560ea6d6f3e1]
 
sql/item.h:1556(Item::val_result())[0x560ea6aea052]
 
sql/item.cc:10253(Item_cache_real::cache_value())[0x560ea7614955]
 
sql/item_cmpfunc.cc:1587(Item_in_optimizer::val_int())[0x560ea7644187]
 
sql/sql_type.cc:4638(Type_handler_int_result::Item_val_bool(Item*) const)[0x560ea732d39a]
 
sql/item.h:1474(Item::val_bool())[0x560ea6ae9d52]
 
sql/item_cmpfunc.cc:219(Item_func_not_all::val_int())[0x560ea76331ac]
 
sql/item_func.cc:2611(Item_func_round::int_op())[0x560ea76e5965]
 
sql/item_func.h:750(Item_func_hybrid_field_type::val_int_from_int_op())[0x560ea735b826]
 
sql/sql_type.cc:4962(Type_handler_int_result::Item_func_hybrid_field_type_val_int(Item_func_hybrid_field_type*) const)[0x560ea73301e4]
 
sql/item_func.h:806(Item_func_hybrid_field_type::val_int())[0x560ea6d6f5a7]
 
sql/item.h:1220(Item::to_longlong_hybrid())[0x560ea767c3c2]
 
sql/item_cmpfunc.cc:2263(Item_func_between::val_int_cmp_int())[0x560ea764c4eb]
 
sql/sql_type.cc:5258(Type_handler_int_result::Item_func_between_val_int(Item_func_between*) const)[0x560ea73313e4]
 
sql/item_cmpfunc.h:909(Item_func_between::val_int())[0x560ea79a32d7]
 
sql/item_func.cc:756(Item_int_func::val_real())[0x560ea76cbfbd]
 
sql/item.cc:8535(Item_direct_ref::val_real())[0x560ea76039e9]
 
sql/item.h:5936(Item_direct_view_ref::val_real())[0x560ea76226e1]
 
sql/item_func.cc:2011(Item_func_exp::val_real())[0x560ea76dc55d]
 
sql/item_func.cc:696(Item_real_func::val_str(String*))[0x560ea76cb3a4]
 
sql/item_cmpfunc.cc:5873(Regexp_processor_pcre::compile(Item*, bool))[0x560ea766f377]
 
sql/item_cmpfunc.h:2853(Regexp_processor_pcre::recompile(Item*))[0x560ea7687645]
 
sql/item_cmpfunc.cc:6089(Item_func_regex::val_int())[0x560ea7670669]
 
sql/sql_type.cc:8305(Type_handler_int_result::Item_eq_value(THD*, Type_cmp_attributes const*, Item*, Item*) const)[0x560ea73438d8]
 
sql/item_cmpfunc.cc:6712(Item_equal::add_const(THD*, Item*))[0x560ea767548e]
 
sql/item_cmpfunc.cc:6838(Item_equal::merge_with_check(THD*, Item_equal*, bool))[0x560ea7675b3b]
 
sql/sql_select.cc:17513(propagate_new_equalities(THD*, Item*, List<Item_equal>*, COND_EQUAL*, bool*))[0x560ea6edaf05]
 
sql/sql_select.cc:17497(propagate_new_equalities(THD*, Item*, List<Item_equal>*, COND_EQUAL*, bool*))[0x560ea6edad6e]
 
sql/opt_subselect.cc:6089(and_new_conditions_to_optimized_cond(THD*, Item*, COND_EQUAL**, List<Item>&, Item::cond_result*))[0x560ea72b1b36]
 
sql/sql_select.cc:2163(JOIN::optimize_inner())[0x560ea6e66d56]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x560ea6e62077]
 
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*))[0x560ea6e83061]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560ea6e53ac8]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560ea6dbf830]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x560ea6dacfa7]
 
sql/sql_parse.cc:8013(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560ea6dc8d6f]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560ea6d9f131]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x560ea6d9bc5c]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x560ea71ab55f]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x560ea71aae03]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x560ea7e47498]
 
nptl/pthread_create.c:478(start_thread)[0x7f5919934609]
 
 
 
Query (0x62b0000a1290): SELECT t2 . c5 AS c22 FROM ( SELECT ~ OCT ( t0 . c49 ) << CONVERT ( -1968393419284614186 , UNSIGNED ) % RAND ( ) - RAND ( -76 ) << + EXISTS ( SELECT -126 AS c42 ) AS c15 FROM t0 ) AS t1 JOIN ( SELECT ROUND ( 88 , ORD ( -25 ) / ATAN ( 32 IN ( SELECT t0 . c49 BETWEEN -24 AND 126 AS c10 FROM t0 ) ) / UNHEX ( 57 ) = ALL ( SELECT t0 . c49 AS c18 FROM t0 ) ) NOT BETWEEN 118 AND 20 AS c5 FROM t0 ) AS t2 ON t1 . c15 = t1 . c15 WHERE LTRIM ( t1 . c15 ) % 42.121931 = -58 GROUP BY c5 , c15 HAVING c15 = 2 REGEXP EXP ( t2 . c5 )

Comment by Alice Sherepa [ 2023-12-11 ]

simplification by Wangdada

CREATE TABLE t0 ( c49 DOUBLE ( 249 , 19 ) ) ;
 
INSERT INTO t0 VALUES ( -120 ) , ( 105 ) ;
 
 
SELECT t2 . c5  FROM 
( SELECT  ( SELECT -126 ) AS c15 ) AS t1 JOIN 
( SELECT    ATAN ( 32 IN ( SELECT t0 . c49 FROM t0 ) ) /
 
  57  = ALL ( SELECT t0 . c49  FROM t0 )  AS c5 FROM t0 ) AS t2 
WHERE  t1 . c15 
GROUP BY  c15 
HAVING c15 = 2 REGEXP EXP ( t2 . c5 ) ;

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