[MDEV-32395] update_depend_map_for_order: SEGV at /mariadb-11.3.0/sql/sql_select.cc:16583 Created: 2023-10-10  Updated: 2024-02-08

Status: In Review
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 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: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 20.04

Issue Links:
Relates

 Description   

Run these queries in release build:

CREATE TABLE t0 ( c6 INT , c21 INT ) ;
INSERT INTO t0 VALUES ( 55 , -95 ) , ( 9 , 90 ) ;
ALTER TABLE t0 ADD COLUMN c37 INT AFTER c6 ;
INSERT INTO t0 VALUES ( ) , ( ) ;
SELECT t0 . c6 AS c42 FROM ( SELECT t0 . c6 = TRIM( TRAILING FROM 96 ) SOUNDS LIKE CONVERT ( t0 . c6 , UNSIGNED ) >> PI ( ) AS c49 FROM t0 ) AS t1 JOIN t0 ON RTRIM ( - RAND ( -66 ) BETWEEN FIND_IN_SET ( 20 , UNHEX ( -80 ) IS NULL OR IF ( 85 , -83 , -113 ) ) AND -125 ) / EXP ( c21 ) = t1 . c49 ORDER BY c42 , ( c42 + ( SELECT c21 AS c61 FROM t0 WHERE t0 . c37 >= -19.601384 = RAND ( ) / TRIM( t0 . c21 FROM 'C@rG3D(#9*17(a.,rV' ) = -106 GROUP BY c21 , c42 HAVING c42 = -73 LIMIT 1 ) ) ;

Will trigger Segmentation fault.
GDB info:
Thread 16 "mariadbd" received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7fffd242e300 (LWP 2691)]
0x0000000000c24454 in update_depend_map_for_order (join=0x6290000b23e0, order=0x6290000ae168) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16583
16583 order->depend_map|=(*tab)->ref.depend_map;
(gdb) p *tab
$10 = (JOIN_TAB *) 0x0

#0 0x0000000000c24454 in update_depend_map_for_order (join=0x6290000b7480, order=0x6290000b46c0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16583
#1 remove_const (join=<optimized out>, first_order=<optimized out>, cond=<optimized out>, change_list=<optimized out>, simple_order=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:16696
#2 0x0000000000c05386 in JOIN::optimize_stage2 (this=0x6290000b7480) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:3104
#3 0x0000000000c13911 in JOIN::optimize_inner (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2650
#4 0x0000000000bfc156 in JOIN::optimize (this=0x6290000b7480) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
#5 0x0000000000ab5421 in st_select_lex::optimize_unflattened_subqueries (this=<optimized out>, const_only=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_lex.cc:4916
#6 0x0000000000c0856c in JOIN::optimize_stage2 (this=0x6290000b58b0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:3229
#7 0x0000000000c13911 in JOIN::optimize_inner (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2650
#8 0x0000000000bfc156 in JOIN::optimize (this=this@entry=0x6290000b58b0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
#9 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=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x6290000b5880, unit=0x62b0001704a8, select_lex=0x629000091670) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5235
#10 0x0000000000be4596 in handle_select (thd=thd@entry=0x62b00016c218, lex=<optimized out>, lex@entry=0x62b0001703c8, result=<optimized out>, result@entry=0x6290000b5880, setup_tables_done_option=<optimized out>, setup_tables_done_option@entry=0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:628
#11 0x0000000000b3df18 in execute_sqlcom_select (thd=0x62b00016c218, all_tables=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:6013
#12 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
#13 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
#14 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
#15 0x0000000000b20b71 in do_command (thd=0x62b00016c218, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
#16 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
#17 0x0000000000f02eb9 in handle_one_connection (arg=arg@entry=0x6080015148b8) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
#18 0x0000000001a00c1b in pfs_spawn_thread (arg=0x617000006618) at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
#19 0x00007ffff79f7609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#20 0x00007ffff770f133 in clone () from /lib/x86_64-linux-gnu/libc.so.6

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

Thanks! THis is the same as MDEV-29681

231012 17:58:00 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: 0c7af6a2a19343cb9d4fedbd7165b8f73bc4cf96
 
 
 
sql/signal_handler.cc:238(handle_fatal_signal)[0x5606bf364f8d]
 
sigaction.c:0(__restore_rt)[0x7fd4ede5b420]
 
sql/sql_select.cc:14381(update_depend_map_for_order(JOIN*, st_order*))[0x5606becc49ee]
 
sql/sql_select.cc:14477(remove_const(JOIN*, st_order*, Item*, bool, bool*))[0x5606becc51b7]
 
sql/sql_select.cc:2818(JOIN::optimize_stage2())[0x5606bec69e91]
 
sql/sql_select.cc:2394(JOIN::optimize_inner())[0x5606bec65402]
 
sql/sql_select.cc:1711(JOIN::optimize())[0x5606bec5e10d]
 
sql/sql_lex.cc:4232(st_select_lex::optimize_unflattened_subqueries(bool))[0x5606beb369e8]
 
sql/opt_subselect.cc:5603(JOIN::optimize_unflattened_subqueries())[0x5606bf0a858b]
 
sql/sql_select.cc:2942(JOIN::optimize_stage2())[0x5606bec6b48b]
 
sql/sql_select.cc:2394(JOIN::optimize_inner())[0x5606bec65402]
 
sql/sql_select.cc:1711(JOIN::optimize())[0x5606bec5e10d]
 
sql/sql_select.cc:4812(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*))[0x5606bec7f0f7]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5606bec4fde0]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5606bebbbbe4]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x5606beba935b]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5606bebc50bf]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5606beb9b4e5]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x5606beb98010]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5606befa5deb]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x5606befa568f]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5606bfc50274]
 
nptl/pthread_create.c:478(start_thread)[0x7fd4ede4f609]
 
 
 
Query (0x62b0000a1290): SELECT t0 . c6 AS c42 FROM ( SELECT t0 . c6 = TRIM( TRAILING FROM 96 ) SOUNDS LIKE CONVERT ( t0 . c6 , UNSIGNED ) >> PI ( ) AS c49 FROM t0 ) AS t1 JOIN t0 ON RTRIM ( - RAND ( -66 ) BETWEEN FIND_IN_SET ( 20 , UNHEX ( -80 ) IS NULL OR IF ( 85 , -83 , -113 ) ) AND -125 ) / EXP ( c21 ) = t1 . c49 ORDER BY c42 , ( c42 + ( SELECT c21 AS c61 FROM t0 WHERE t0 . c37 >= -19.601384 = RAND ( ) / TRIM( t0 . c21 FROM 'C@rG3D(#9*17(a.,rV' ) = -106 GROUP BY c21 , c42 HAVING c42 = -73 LIMIT 1 ) )
 
 

Comment by Oleg Smirnov [ 2023-11-04 ]

MDEV-29681 is a different case, reopening this issue.

CREATE TABLE t0 ( c6 INT , c21 INT ) ;
 
INSERT INTO t0 VALUES ( 55 , -95 ) , ( 9 , 90 ) ;
 
ALTER TABLE t0 ADD COLUMN c37 INT AFTER c6 ;
 
INSERT INTO t0 VALUES ( ) , ( ) ;
 
SELECT t0 . c6 AS c42 FROM ( SELECT t0 . c6 = TRIM( TRAILING FROM 96 ) SOUNDS LIKE CONVERT ( t0 . c6 , UNSIGNED ) >> PI ( ) AS c49 FROM t0 ) AS t1 JOIN t0 ON RTRIM ( - RAND ( -66 ) BETWEEN FIND_IN_SET ( 20 , UNHEX ( -80 ) IS NULL OR IF ( 85 , -83 , -113 ) ) AND -125 ) / EXP ( c21 ) = t1 . c49 ORDER BY c42 , ( c42 + ( SELECT c21 AS c61 FROM t0 WHERE t0 . c37 >= -19.601384 = RAND ( ) / TRIM( t0 . c21 FROM 'C@rG3D(#9*17(a.,rV' ) = -106 GROUP BY c21 , c42 HAVING c42 = -73 LIMIT 1 ) ) ;

Comment by Jason (Inactive) [ 2023-11-14 ]

simplified query to this:

WITH t0(c6) AS (VALUES(0)),
t1(c49) AS (VALUES(0)),
tx(c) AS (VALUES(0))
SELECT t0.c6 AS c42 FROM t1 JOIN t0 ON c6=c49
ORDER BY ( SELECT 1 FROM tx GROUP BY c42 HAVING c42=-73);

Of note is that the columns in the subquery (c42) refer to the outer query (t0.c6).
SEGV occurs in update_depend_map_for_order which is looping over the JOIN->map2table for the subquery. The subquery map2table contains only JOIN_TAB for tx, but the order column had been resolved to t0.c6 which is not in table tx. So the loop through map2table wanders into uninitialized storage and causes the segfault.
The dependency check is avoided if the reference to the outer select is detected (ie. OUTER_REF_TABLE_BIT is set in order's depend_map). I am investigating why this is not happening.

Comment by Sergei Petrunia [ 2023-11-15 ]

As pointed out by Jason in the email, one of the steps that creates the problem (and I suspect it's the main one) in that eliminate_item_equal() "unwraps" Item_direct_ref objects:

        eq_item= new (thd->mem_root) Item_func_eq(thd,
 
                                                  field_item->remove_item_direct_ref(),
 
                                                  head_item->remove_item_direct_ref());

The remove_item_direct_ref() was introduced by https://github.com/MariaDB/server/commit/ae15f91f227015b3e1ad3f566db9396232cf0a3f

commit ae15f91f227015b3e1ad3f566db9396232cf0a3f
 
Author:	Galina Shalygina <galina.shalygina@mariadb.com>  Sat Mar 23 15:28:22 2019
 
Committer:	Galina Shalygina <galina.shalygina@mariadb.com>  Thu Apr  4 18:06:56 2019
 
 
 
MDEV-18769 Assertion `fixed == 1' failed in Item_cond_or::val_int
 
 
 
    
        This bug is caused by pushdown from HAVING into WHERE.
 
        It appears because condition that is pushed wasn't fixed.

unfortunately, that patch doesn't describe the purpose of remove_item_direct_ref() call.

Also note that disabling condition pushdown and running the above Jason's simplified query still causes the crash.

Comment by Sergei Petrunia [ 2023-11-15 ]

... as for HAVING->WHERE pushdown: it looks like it could happen for the subquery:

GROUP BY c42 HAVING c42=-73

but the code doesn't do it: collect_grouping_fields disallows Item_direct_ref objects (no idea why).

Comment by Igor Babaev [ 2023-11-15 ]

The following test case that does not use any CTE or derived tables and does not use any condition pushdown crashes in the same way on the latest 10.4:

create table t1 (c1 int) engine=myisam;
 
insert into t1 values (1), (2);
 
create table t2 (c2 int) engine=myisam;
 
insert into t2 values (1), (2);
 
create table t3 (c3 int) engine=myisam;
 
insert into t3 values (1), (2);
 
 
 
set optimizer_switch=
 
'condition_pushdown_for_derived=off,condition_pushdown_for_subquery=off,condition_pushdown_from_having=off';
 
 
 
select t1.c1 as a from t2, t1 where t1.c1=t2.c2
 
  order by (select c3 from t3 group by a having a=2);

Comment by Igor Babaev [ 2023-11-16 ]

It looks like the following change fixes the problem:

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
 
index a871fae..1361792 100644
 
--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -16073,9 +16073,7 @@ Item *eliminate_item_equal(THD *thd, COND *cond, COND_EQUAL *upper_levels,
 
       */
 
       Item *head_item= (!item_const && current_sjm && 
                         current_sjm_head != field_item) ? current_sjm_head: head;
 
-      eq_item= new (thd->mem_root) Item_func_eq(thd,
 
-                                                field_item->remove_item_direct_ref(),
 
-                                                head_item->remove_item_direct_ref());
 
+      eq_item= new (thd->mem_root) Item_func_eq(thd, field_item, head_item);
 
 
       if (!eq_item || eq_item->set_cmp_func())
 
         return 0;

Comment by Jason (Inactive) [ 2023-11-17 ]

Igor's suggested patch does seem to resolve the problem, and it passes my local run of the main tests including those introduced by https://github.com/MariaDB/server/commit/ae15f91f227015b3e1ad3f566db9396232cf0a3f (which introduced the change). I will submit it to buildbot as soon as I can resolve some permission issues with the remote repo (working through helpdesk).

It's still unclear why remove_item_direct_ref() was introduced in the above code in the first place, but removing them apparently does no harm to existing tests and resolves the issue reported here.

Comment by Sergei Petrunia [ 2023-11-21 ]

Note: eliminate_item_equal() is the only place that has calls to {remove_item_direct_ref() . If we make it not to call remove_item_direct_ref, let's then remove remove_item_direct_ref altogether?

Comment by Sergei Petrunia [ 2023-11-29 ]

It's still unclear why remove_item_direct_ref() was introduced in the above code in the first place,

Re-assigning to Igor as he has reviewed the patch that introduced that call.

Comment by Daniel Black [ 2024-02-08 ]

igor the pull request https://github.com/MariaDB/server/pull/2873 has been waiting a while now since jasoncu left.

Also work out with serg, sanja if this is going into 10.4

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