[MDEV-32424] Computing unoptimized subquery? SEGV at /mariadb-11.3.0/sql/sql_select.cc:4717 Created: 2023-10-10  Updated: 2024-02-08

Status: In Review
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.33

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

Ubuntu 20.04

Issue Links:
Duplicate
duplicates MDEV-29363 Server crashed with heap-use-after-fr... In Review
is duplicated by MDEV-32539 Server crash in Time_and_counter_trac... Closed

 Description   

Run these queries in release build:

CREATE TABLE x ( x VARCHAR ( 1 ) ) ;
 
INSERT INTO x ( x ) VALUES ( 'x' ) , ( NULL ) , ( 'x' ) , ( NULL ) ;
 
SELECT 1 - x IN ( SELECT x FROM x ORDER BY x + 1 ) FROM x GROUP BY x HAVING x = ( SELECT x AS x FROM x WHERE x = x ORDER BY ( 1 < x AND x = 1 ) ) AND x IN ( 1 , x ) ;

Will trigger Segmentation fault.
GDB info:

Thread 16 "mariadbd" received signal SIGSEGV, Segmentation fault.
 
[Switching to Thread 0x7fffe011a700 (LWP 46873)]
 
JOIN::exec (this=0x7fff94079fa0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4717
 
4717	  ANALYZE_START_TRACKING(thd, &explain->time_tracker);
 
(gdb) p explain
 
$62 = (Explain_select *) 0x0
 
(gdb) p &explain->time_tracker
 
$64 = (Time_and_counter_tracker *) 0xb0
 
 
 
#0  JOIN::exec (this=0x7fff94079fa0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4717
 
#1  0x00005555560b1422 in subselect_single_select_engine::exec (this=0x7fff940725d0)
 
    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:4159
 
#2  0x00005555560b040c in Item_subselect::exec (this=0x7fff94072440)
 
    at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:812
 
#3  0x00005555560afda4 in Item_singlerow_subselect::val_str (this=0x7fff94072440, 
    str=0x7fffe0118280) at /home/wx/mariadb-11.3.0/sql/item_subselect.cc:1484
 
#4  0x0000555556028ab8 in cmp_item_sort_string::cmp (this=0x7fff94079c20, arg=0x7fff94072440)
 
    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.h:1742
 
#5  0x0000555556020ce6 in Predicant_to_list_comparator::cmp_arg (i=1, args=0x7fff94072ba8, 
    this=0x7fff94072c08) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.h:2083
 
#6  Predicant_to_list_comparator::cmp (found_unknown_values=0x7fff94072b9c, 
    idx=<synthetic pointer>, args=0x7fff94072ba8, this=0x7fff94072c08)
 
    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.h:2260
 
#7  Item_func_in::val_int (this=0x7fff94072b30)
 
    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:4761
 
#8  0x0000555555f211e4 in Type_handler_int_result::Item_val_bool (this=<optimized out>, 
    item=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:5082
 
#9  0x0000555556011c6c in Item_cond_and::val_int (this=0x7fff9407ce00)
 
    at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:5524
 
#10 0x0000555555dad3fd in evaluate_join_record (join=join@entry=0x7fff94073708, 
    join_tab=join_tab@entry=0x7fff9407be10, error=<optimized out>)
 
    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23545
 
#11 0x0000555555dbf7fb in sub_select (join=0x7fff94073708, join_tab=0x7fff9407be10, 
    end_of_records=false) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23444
 
#12 0x0000555555df19b2 in do_select (procedure=<optimized out>, join=0x7fff94073708)
 
    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22961
 
#13 JOIN::exec_inner (this=this@entry=0x7fff94073708)
 
    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941
 
#14 0x0000555555df1d78 in JOIN::exec (this=this@entry=0x7fff94073708)
 
    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718
 
#15 0x0000555555defe1c in mysql_select (thd=thd@entry=0x7fff94000c58, tables=0x7fff940150f0, 
    fields=..., conds=0x0, og_num=1, order=0x0, group=0x7fff94015940, having=0x7fff94072c98, 
    proc_param=0x0, select_options=<optimized out>, result=0x7fff940736e0, 
    unit=0x7fff94004ee8, select_lex=0x7fff94012de8)
 
    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5249
 
#16 0x0000555555df0607 in handle_select (thd=thd@entry=0x7fff94000c58, 
    lex=lex@entry=0x7fff94004e08, result=result@entry=0x7fff940736e0, 
    setup_tables_done_option=setup_tables_done_option@entry=0)
 
    at /home/wx/mariadb-11.3.0/sql/sql_select.cc:628
 
#17 0x0000555555d6de41 in execute_sqlcom_select (thd=thd@entry=0x7fff94000c58, 
    all_tables=0x7fff940150f0) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:6013
 
#18 0x0000555555d7c2aa in mysql_execute_command (thd=thd@entry=0x7fff94000c58, 
    is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)
 
    at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:3912
 
#19 0x0000555555d68c27 in mysql_parse (thd=0x7fff94000c58, rawbuf=<optimized out>, 
    length=<optimized out>, parser_state=<optimized out>)
 
    at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
 
#20 0x0000555555d74fdd in dispatch_command (command=command@entry=COM_QUERY, 
    thd=thd@entry=0x7fff94000c58, packet=packet@entry=0x7fff94008509 "", 
    packet_length=packet_length@entry=164, blocking=blocking@entry=true)
 
    at /home/wx/mariadb-11.3.0/sql/sql_class.h:251
 
#21 0x0000555555d7721e in do_command (thd=0x7fff94000c58, blocking=blocking@entry=true)
 
    at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
 
#22 0x0000555555e9a617 in do_handle_one_connection (connect=<optimized out>, 
    connect@entry=0x555557e14ea8, put_in_cache=put_in_cache@entry=true)
 
    at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
 
#23 0x0000555555e9a94d in handle_one_connection (arg=arg@entry=0x555557e14ea8)
 
    at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
 
#24 0x00005555561e658d in pfs_spawn_thread (arg=0x555557dbe968)
 
    at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
 
#25 0x00007ffff7b48609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#26 0x00007ffff7719133 in clone () from /lib/x86_64-linux-gnu/libc.so.6



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

Thanks! I repeated as described on 10.4-11.2, this is the same bug as MDEV-32539

Version: '10.4.32-MariaDB-debug-log' 
mysqld: /10.4/src/sql/sql_select.cc:4324: bool JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool): Assertion `select_lex->select_number == (0x7fffffff * 2U + 1U) || select_lex->select_number == 0x7fffffff || !output || !output->get_select(select_lex->select_number) || output->get_select(select_lex->select_number)->select_lex == select_lex' failed.
 
 
 
231025 17:42:40 [ERROR] mysqld got signal 6 ;
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f36305b0fd6]
 
sql/sql_select.cc:4331(JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool))[0x561f158826fe]
 
sql/sql_select.cc:1660(JOIN::build_explain())[0x561f15865999]
 
sql/sql_select.cc:1717(JOIN::optimize())[0x561f15866358]
 
sql/sql_lex.cc:4232(st_select_lex::optimize_unflattened_subqueries(bool))[0x561f1573eb84]
 
sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x561f15cb1027]
 
sql/sql_select.cc:2942(JOIN::optimize_stage2())[0x561f15873627]
 
sql/sql_select.cc:2394(JOIN::optimize_inner())[0x561f1586d59e]
 
sql/sql_select.cc:1711(JOIN::optimize())[0x561f158662a9]
 
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*))[0x561f15887293]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x561f15857f7c]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x561f157c3d80]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x561f157b14f7]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x561f157cd25b]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x561f157a3681]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x561f157a01ac]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x561f15bae56d]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x561f15bade11]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x561f16858d8a]
 
nptl/pthread_create.c:478(start_thread)[0x7f3630acb609]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530f28)
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f363069c133]
 
 
 
 
 
Query (0x62b0000a1290): SELECT 1 - x IN ( SELECT x FROM x ORDER BY x + 1 ) FROM x GROUP BY x HAVING x = ( SELECT x AS x FROM x WHERE x = x ORDER BY ( 1 < x AND x = 1 ) ) AND x IN ( 1 , x )


Version: '10.11.5-MariaDB'  
231025 17:47:44 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.11.5-MariaDB source revision: 7875294b6b74b53dd3aaa723e6cc103d2bb47b2c
 
 
 
sql/signal_handler.cc:241(handle_fatal_signal)[0x55ee7b47e4c7]
 
sigaction.c:0(__restore_rt)[0x7f16c738e420]
 
sql/sql_analyze_stmt.h:172(Time_and_counter_tracker::incr_loops())[0x55ee7b2ac583]
 
sql/item_subselect.cc:4116(subselect_single_select_engine::exec())[0x55ee7b54ecf6]
 
sql/item_subselect.cc:816(Item_subselect::exec())[0x55ee7b54d2aa]
 
sql/item_subselect.cc:1483(Item_singlerow_subselect::val_str(String*))[0x55ee7b54deef]
 
sql/item_cmpfunc.h:1747(cmp_item_sort_string::cmp(Item*))[0x55ee7b4d003d]
 
sql/item_cmpfunc.h:2265(Predicant_to_list_comparator::cmp(Item_args*, unsigned int*, bool*))[0x55ee7b4c4a8f]
 
sql/sql_type.cc:5075(Type_handler_int_result::Item_val_bool(Item*) const)[0x55ee7b3c02f0]
 
sql/item_cmpfunc.cc:5533(Item_cond_and::val_int())[0x55ee7b4b5b3a]
 
sql/sql_select.cc:22240(evaluate_join_record(JOIN*, st_join_table*, int))[0x55ee7b274a08]
 
sql/sql_select.cc:22147(sub_select(JOIN*, st_join_table*, bool))[0x55ee7b27ac2f]
 
sql/sql_select.cc:21670(JOIN::exec_inner())[0x55ee7b2ac267]
 
sql/sql_select.cc:4633(JOIN::exec())[0x55ee7b2ac5a3]
 
sql/sql_select.cc:5114(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*))[0x55ee7b2aa7ee]
 
sql/sql_select.cc:598(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x55ee7b2ab054]
 
sql/sql_parse.cc:6290(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55ee7b0d19e6]
 
sql/sql_parse.cc:3959(mysql_execute_command(THD*, bool))[0x55ee7b23b9d0]
 
sql/sql_parse.cc:8035(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55ee7b23dd8b]
 
sql/sql_parse.cc:1953(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55ee7b2401c8]
 
sql/sql_parse.cc:1409(do_command(THD*, bool))[0x55ee7b2416f3]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55ee7b34d5e7]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x55ee7b34d884]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55ee7b6d0d6c]
 
nptl/pthread_create.c:478(start_thread)[0x7f16c7382609]
 
 
 
Query (0x7f1648010c20): SELECT 1 - x IN ( SELECT x FROM x ORDER BY x + 1 ) FROM x GROUP BY x HAVING x = ( SELECT x AS x FROM x WHERE x = x ORDER BY ( 1 < x AND x = 1 ) ) AND x IN ( 1 , x )

Comment by Alice Sherepa [ 2023-10-25 ]

test case from MDEV-32539 :

CREATE TABLE t1 (a date);
 
INSERT INTO t1 VALUES ('2012-12-12'),('2000-01-01'); # Optional, fails either way
 
CREATE TABLE t2 (f datetime);
 
INSERT INTO t2 VALUES ('0000-00-00 00:00:00'),('0000-00-00 00:00:00');
 
 
SELECT a FROM t1 GROUP BY a HAVING (a = '' OR a = 0) AND a = (SELECT MIN(f) FROM t2);
 
 
# Cleanup
 
DROP TABLE t1, t2;

from MDEV-32608 :

CREATE TABLE v0 ( v1 NUMERIC NOT NULL PRIMARY KEY , v2 NUMERIC ) engine=MYISAM;
 
INSERT INTO v0 VALUES ( 127 , -1 ) ;
 
UPDATE v0 SET v1 = -128 WHERE v2 = 255 ;
 
UPDATE v0 SET v1 = 5 WHERE v1 = NULL ;
 
UPDATE v0 SET v2 = 89 WHERE v1 = -2147483648 OR v1 = 57 ;
 
UPDATE v0 SET v2 = 49 WHERE v1 = 95 ;
 
SELECT * FROM v0 ORDER BY v1 ;
 
SELECT * FROM v0 GROUP BY v2 HAVING ( SELECT v2 FROM v0 WHERE v1 > 57 OR v2 > 83 AND v2 NOT LIKE 'x' ORDER BY v1 * v2 ) = v1 AND v1 * v2 + 52 = 'x' ORDER BY v2 ;

Comment by Yuchen Pei [ 2024-01-12 ]

The assertion failure in [1] looks similar to [2]. I wonder whether this ticket is related to MDEV-29363.

[1] https://jira.mariadb.org/browse/MDEV-32424?focusedCommentId=272672&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-272672
[2] https://jira.mariadb.org/browse/MDEV-29363?focusedCommentId=277986&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-277986

Comment by Dave Gosselin [ 2024-01-22 ]

A shorter reproduction case for this issue is:

  CREATE TABLE t1 (col VARCHAR(1));
 
  INSERT INTO t1 (col) VALUES ('x'), (NULL), ('x'), (NULL);
 
  SELECT * FROM t1 GROUP BY col HAVING col = (SELECT * FROM t1) AND col IN (1 , col);

This issue appeared at igor's commit git sha 1c97cd339e9513b152727f386573c8c048db0281
psergei's patch from this comment on MDEV-29363 fixes this issue. As such, I'm closing this ticket as a duplicate of MDEV-29363.

Comment by Dave Gosselin [ 2024-01-22 ]

Apparent duplicate of MDEV-29363

Comment by Yuchen Pei [ 2024-01-23 ]

Thanks for checking Gosselin - did you check all test cases
mentioned in this ticket? I am still getting the same assert failure
when running the MDEV-32539 test with psergei's patch
(2cf940943ad4a102facee4b90c945b538d6c32ef).

alice, why is MDEV-32539 a duplicate of this ticket (and
MDEV-29363)? For one thing, the case in MDEV-32539 results in an
assertion failure, but the main case in this ticket and MDEV-29363
results in segfaults/asan use-after-free.

BTW the current idea of fixing MDEV-29363 is to ban creating pointers
to items containing subqueries during equality propagation, with the
latest public patch being 5790cb27b33e3f117bea41f4ab99a9efaffaa263.

Comment by Dave Gosselin [ 2024-01-23 ]

ycp I didn't test cases from another ticket when closing this one. If there are other cases triggering crashes, those should be treated as separate bugs in my opinion. We can reopen this ticket if, after 29363 is resolved, the issue from the description when originally filed persists.

Comment by Dave Gosselin [ 2024-01-26 ]

alice it seems that the stack trace in the description of this ticket is different than what you posted in your comment. I think that there may be some relationship, but these are separate bugs. It gets confusing to post stack traces and test cases across tickets because it blurs the picture of what issues and test cases go together. Thanks.

Comment by Yuchen Pei [ 2024-01-29 ]

alice you are right - I could get these stacks for the original case and the MDEV-32539 case, both at 11.3.1 67a0224a3e0073c4e083a6f7e24380251bcb2361 and 11.0 c2da55ac01b5a50961ef4576edce7668ed791776. However in the MDEV-32539 case the "optional" line is not optional for the reproduction in the non-debug build. If we remove it it will pass.

Now, with psergei's MDEV-29363 patch (check out 2cf940943ad4a102facee4b90c945b538d6c32ef), the MDEV-32539 case still fails with the same trace. So I suggest that we re-open this ticket and re-close MDEV-32539. Gosselin.

Comment by Dave Gosselin [ 2024-01-29 ]

ycp I agree if we resolve this ticket by delivering psergei's patch from MDEV-29363.

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