[MDEV-31432] tmp_table field accessed after free Created: 2023-06-08  Updated: 2023-08-25  Resolved: 2023-08-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.10.7, 10.11.6, 11.0.4, 11.1.2, 11.2.1

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: ASAN, memory_corruption, not-10.6, regression-10.9

Attachments: File mdev31432-debug-temptables.sql    
Issue Links:
Problem/Incident
is caused by MDEV-28201 Server crashes upon SHOW ANALYZE/EXPL... Closed
Relates
relates to MDEV-24658 Assertion `marked_for_read()' failed ... Confirmed

 Description   

Apologies for the bit longer testcase line. When I tried to simplify it further, the ASAN stack changed. I am listing both testcase versions (ref comment).

SELECT * FROM (SELECT x,0 FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x IN ((SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT NOT x)) AS x) AS x GROUP BY x,x IN (SELECT 1 WHERE x IN (SELECT 1 WHERE NOT x IN (1)))) AS x WHERE x IN (1)) AS x GROUP BY NOT x IN (SELECT (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x GROUP BY x HAVING NOT 1) AS x WHERE x IN (1) GROUP BY x,x) AS x) AS x) IN ((SELECT (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x GROUP BY x HAVING NOT x) IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x) GROUP BY x,x HAVING x IN (SELECT x IN (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x WHERE x IN (1) GROUP BY x HAVING x IN (SELECT NOT (SELECT * FROM (SELECT * FROM (SELECT x IN (SELECT 1 AS x WHERE x IN ((SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 IN (1) AS x)))) AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x WHERE x IN (1) GROUP BY x HAVING NOT NOT x)) AS x) AS x) AS x) AS x) AS x WHERE x IN (1)) AS x) FROM (SELECT 1 AS x) AS x)))) WHERE NOT x IN (1))) AS x WHERE x IN (1)) AS x WHERE x IN (1) GROUP BY x HAVING NOT NOT x) AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x WHERE x IN (1)) AS x) AS x WHERE x IN (1))));

Leads to:

11.1.0 4e5b771e980edfdad5c5414aa62c81d409d585a4 (Debug)

 
Core was generated by `/test/MD120523-mariadb-11.1.0-linux-x86_64-dbg/bin/mariadbd --no-defaults --cor'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  0x000055a0faf7d41e in Item_field::print (this=0x149b9c113ed8, 
    str=0x149bf13c7360, query_type=QT_ORDINARY)
 
    at /test/11.1_dbg/sql/item.cc:7909
 
[Current thread is 1 (Thread 0x149bf13c9640 (LWP 799814))]
 
(gdb) bt
 
#0  0x000055a0faf7d41e in Item_field::print (this=0x149b9c113ed8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item.cc:7909
 
#1  0x000055a0faf7d010 in Item::print_parenthesised (this=this@entry=0x149b9c113ed8, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY, parent_prec=CMP_PRECEDENCE) at /test/11.1_dbg/sql/item.cc:498
 
#2  0x000055a0fafe8839 in Item_func::print_op (this=0x149b9c116fa0, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:633
 
#3  0x000055a0fab93e97 in Item_bool_rowready_func2::print (this=<optimized out>, str=<optimized out>, query_type=<optimized out>) at /test/11.1_dbg/sql/item_cmpfunc.h:550
 
#4  0x000055a0faf7d010 in Item::print_parenthesised (this=this@entry=0x149b9c116fa0, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY, parent_prec=AND_PRECEDENCE) at /test/11.1_dbg/sql/item.cc:498
 
#5  0x000055a0fafa10d1 in Item_cond::print (this=0x149b9c115e38, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:5384
 
#6  0x000055a0fad02a35 in st_select_lex::print (this=0x149b9c044ce8, thd=thd@entry=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30361
 
#7  0x000055a0fb05e236 in subselect_single_select_engine::print (this=0x149b9c05d158, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#8  0x000055a0fb05ebed in Item_subselect::print (this=0x149b9c05cfc8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#9  0x000055a0faf7d010 in Item::print_parenthesised (this=this@entry=0x149b9c05cfc8, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY, parent_prec=NEG_PRECEDENCE) at /test/11.1_dbg/sql/item.cc:498
 
#10 0x000055a0fafa4bb3 in Item_func_not::print (this=0x149b9c05d198, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:211
 
#11 0x000055a0fad02eab in st_select_lex::print (this=0x149b9c044840, thd=thd@entry=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30315
 
#12 0x000055a0fb05e236 in subselect_single_select_engine::print (this=0x149b9c05d4b8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#13 0x000055a0fb05ebed in Item_subselect::print (this=this@entry=0x149b9c0609c8, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#14 0x000055a0fb05efa5 in Item_in_subselect::print (this=0x149b9c0609c8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#15 0x000055a0fafe83b7 in Item_func::print_args (this=this@entry=0x149b9c0d0cf8, str=str@entry=0x149bf13c7360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#16 0x000055a0fafe85a4 in Item_func::print (this=this@entry=0x149b9c0d0cf8, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#17 0x000055a0fafaa5a8 in Item_in_optimizer::print (this=0x149b9c0d0cf8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#18 0x000055a0fad02a8d in st_select_lex::print (this=0x149b9c040d60, thd=thd@entry=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30393
 
#19 0x000055a0fb05e236 in subselect_single_select_engine::print (this=0x149b9c05d538, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#20 0x000055a0fad01c4e in TABLE_LIST::print (this=0x149b9c10e5c8, thd=thd@entry=0x149b9c000d58, eliminated_tables=eliminated_tables@entry=0, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30087
 
#21 0x000055a0fad025fe in print_table_array (query_type=QT_ORDINARY, end=0x149b9c381fb8, table=0x149b9c381fa8, str=0x149bf13c7360, eliminated_tables=0, thd=0x149b9c000d58) at /test/11.1_dbg/sql/sql_select.cc:29860
 
#22 print_join (thd=thd@entry=0x149b9c000d58, eliminated_tables=0, str=str@entry=0x149bf13c7360, tables=0x149b9c03d758, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30016
 
#23 0x000055a0fad02f75 in st_select_lex::print (this=0x149b9c03d598, thd=thd@entry=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30342
 
#24 0x000055a0fb05e236 in subselect_single_select_engine::print (this=0x149b9c062b20, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#25 0x000055a0fb05ebed in Item_subselect::print (this=this@entry=0x149b9c0628f0, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#26 0x000055a0fb05efa5 in Item_in_subselect::print (this=0x149b9c0628f0, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#27 0x000055a0fafe83b7 in Item_func::print_args (this=this@entry=0x149b9c0d1250, str=str@entry=0x149bf13c7360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#28 0x000055a0fafe85a4 in Item_func::print (this=this@entry=0x149b9c0d1250, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#29 0x000055a0fafaa5a8 in Item_in_optimizer::print (this=0x149b9c0d1250, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#30 0x000055a0fad02eab in st_select_lex::print (this=0x149b9c03cfb8, thd=thd@entry=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30315
 
#31 0x000055a0fb05e236 in subselect_single_select_engine::print (this=0x149b9c063cf0, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#32 0x000055a0fb05ebed in Item_subselect::print (this=this@entry=0x149b9c063ac0, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#33 0x000055a0fb05efa5 in Item_in_subselect::print (this=0x149b9c063ac0, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#34 0x000055a0fafe83b7 in Item_func::print_args (this=this@entry=0x149b9c0d1628, str=str@entry=0x149bf13c7360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#35 0x000055a0fafe85a4 in Item_func::print (this=this@entry=0x149b9c0d1628, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#36 0x000055a0fafaa5a8 in Item_in_optimizer::print (this=0x149b9c0d1628, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#37 0x000055a0fad02a8d in st_select_lex::print (this=this@entry=0x149b9c039e80, thd=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30393
 
#38 0x000055a0fac51600 in st_select_lex_unit::print (this=0x149b9c063d40, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_lex.cc:3671
 
#39 0x000055a0fad01ea0 in TABLE_LIST::print (this=0x149b9c0645b8, thd=thd@entry=0x149b9c000d58, eliminated_tables=eliminated_tables@entry=0, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30122
 
#40 0x000055a0fad025fe in print_table_array (query_type=QT_ORDINARY, end=0x149b9c381fa8, table=0x149b9c381fa0, str=0x149bf13c7360, eliminated_tables=0, thd=0x149b9c000d58) at /test/11.1_dbg/sql/sql_select.cc:29860
 
#41 print_join (thd=thd@entry=0x149b9c000d58, eliminated_tables=0, str=str@entry=0x149bf13c7360, tables=0x149b9c039580, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30016
 
#42 0x000055a0fad02f75 in st_select_lex::print (this=0x149b9c0393c0, thd=thd@entry=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30342
 
#43 0x000055a0fb05e236 in subselect_single_select_engine::print (this=0x149b9c0674c8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#44 0x000055a0fb05ebed in Item_subselect::print (this=this@entry=0x149b9c067298, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#45 0x000055a0fb05efa5 in Item_in_subselect::print (this=0x149b9c067298, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#46 0x000055a0fafe83b7 in Item_func::print_args (this=this@entry=0x149b9c0d52d0, str=str@entry=0x149bf13c7360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#47 0x000055a0fafe85a4 in Item_func::print (this=this@entry=0x149b9c0d52d0, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#48 0x000055a0fafaa5a8 in Item_in_optimizer::print (this=0x149b9c0d52d0, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#49 0x000055a0faf8c283 in Item_cache_wrapper::print (this=0x149b9c252650, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item.cc:8814
 
#50 0x000055a0fad02eab in st_select_lex::print (this=0x149b9c034f30, thd=thd@entry=0x149b9c000d58, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30315
 
#51 0x000055a0fb05e236 in subselect_single_select_engine::print (this=0x149b9c06dd18, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#52 0x000055a0fb05ebed in Item_subselect::print (this=this@entry=0x149b9c06dae8, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#53 0x000055a0fb05efa5 in Item_in_subselect::print (this=0x149b9c06dae8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#54 0x000055a0fafe83b7 in Item_func::print_args (this=this@entry=0x149b9c0d5fc8, str=str@entry=0x149bf13c7360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#55 0x000055a0fafe85a4 in Item_func::print (this=this@entry=0x149b9c0d5fc8, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#56 0x000055a0fafaa5a8 in Item_in_optimizer::print (this=0x149b9c0d5fc8, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#57 0x000055a0faf8c283 in Item_cache_wrapper::print (this=0x149b9c381538, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item.cc:8814
 
#58 0x000055a0faf7d010 in Item::print_parenthesised (this=this@entry=0x149b9c381538, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY, parent_prec=BETWEEN_PRECEDENCE) at /test/11.1_dbg/sql/item.cc:498
 
#59 0x000055a0fafe87a3 in Item_func::print_op (this=0x149b9c10a7d0, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:638
 
#60 0x000055a0fab93e97 in Item_bool_rowready_func2::print (this=<optimized out>, str=<optimized out>, query_type=<optimized out>) at /test/11.1_dbg/sql/item_cmpfunc.h:550
 
#61 0x000055a0faf7d044 in Item::print_parenthesised (this=this@entry=0x149b9c10a7d0, str=str@entry=0x149bf13c7360, query_type=query_type@entry=QT_ORDINARY, parent_prec=<optimized out>) at /test/11.1_dbg/sql/item.cc:498
 
#62 0x000055a0fafa4bb3 in Item_func_not::print (this=0x149b9c06e920, str=0x149bf13c7360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:211
 
#63 0x000055a0facc83f1 in change_to_use_tmp_fields (thd=0x149b9c000d58, ref_pointer_array=<optimized out>, res_selected_fields=@0x149b9c06f760: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55a0fc16fc80 <end_of_list>, last = 0x149b9c06f760, elements = 0}, <No data fields>}, res_all_fields=@0x149b9c06f718: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55a0fc16fc80 <end_of_list>, last = 0x149b9c06f718, elements = 0}, <No data fields>}, elements=2, all_fields=@0x149b9c06f700: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x149b9c0d7908, last = 0x149b9c0147c0, elements = 3}, <No data fields>}) at /test/11.1_dbg/sql/sql_select.cc:28103
 
#64 0x000055a0facfb75d in JOIN::make_aggr_tables_info (this=this@entry=0x149b9c06f350) at /test/11.1_dbg/sql/sql_select.cc:3743
 
#65 0x000055a0fad0d0fb in JOIN::optimize_stage2 (this=this@entry=0x149b9c06f350) at /test/11.1_dbg/sql/sql_select.cc:3349
 
#66 0x000055a0fad0f367 in JOIN::optimize_inner (this=this@entry=0x149b9c06f350) at /test/11.1_dbg/sql/sql_select.cc:2602
 
#67 0x000055a0fad0f814 in JOIN::optimize (this=this@entry=0x149b9c06f350) at /test/11.1_dbg/sql/sql_select.cc:1902
 
#68 0x000055a0fad0f91d in mysql_select (thd=thd@entry=0x149b9c000d58, tables=<optimized out>, fields=@0x149b9c0144a8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x149b9c0147c0, last = 0x149b9c081728, elements = 2}, <No data fields>}, conds=0x0, og_num=1, order=0x0, group=0x149b9c06e9d8, having=0x0, proc_param=0x0, select_options=2164525824, result=0x149b9c06f328, unit=0x149b9c004fa8, select_lex=0x149b9c0141e8) at /test/11.1_dbg/sql/sql_select.cc:5143
 
#69 0x000055a0fad10102 in handle_select (thd=thd@entry=0x149b9c000d58, lex=lex@entry=0x149b9c004ec8, result=result@entry=0x149b9c06f328, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/11.1_dbg/sql/sql_select.cc:611
 
#70 0x000055a0fac763bc in execute_sqlcom_select (thd=thd@entry=0x149b9c000d58, all_tables=0x149b9c02e6d8) at /test/11.1_dbg/sql/sql_parse.cc:6024
 
#71 0x000055a0fac81a1c in mysql_execute_command (thd=thd@entry=0x149b9c000d58, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/11.1_dbg/sql/sql_parse.cc:3944
 
#72 0x000055a0fac87fad in mysql_parse (thd=thd@entry=0x149b9c000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x149bf13c8230) at /test/11.1_dbg/sql/sql_parse.cc:7760
 
#73 0x000055a0fac8a141 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x149b9c000d58, packet=packet@entry=0x149b9c00ae49 "SELECT * FROM (SELECT x,0 FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x WHERE x "..., packet_length=packet_length@entry=1862, blocking=blocking@entry=true) at /test/11.1_dbg/sql/sql_class.h:242
 
#74 0x000055a0fac8bf9d in do_command (thd=0x149b9c000d58, blocking=blocking@entry=true) at /test/11.1_dbg/sql/sql_parse.cc:1405
 
#75 0x000055a0faddde5a in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55a0fddf6408, put_in_cache=put_in_cache@entry=true) at /test/11.1_dbg/sql/sql_connect.cc:1416
 
#76 0x000055a0fadde0b9 in handle_one_connection (arg=0x55a0fddf6408) at /test/11.1_dbg/sql/sql_connect.cc:1318
 
#77 0x0000149c0f094b43 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#78 0x0000149c0f126a00 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Additionally it produces:

11.0.2 368dd22a816f3b437bccd0b9ff28b9de9b1abf0a (Debug)

 
==1035094==ERROR: AddressSanitizer: heap-use-after-free on address 0x61900007cbb8 at pc 0x55924a484606 bp 0x14c4e9274eb0 sp 0x14c4e9274ea0
 
READ of size 8 at 0x61900007cbb8 thread T36
 
    #0 0x55924a484605 in Item_field::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item.cc:7898
 
    #1 0x55924a4828c3 in Item::print_parenthesised(String*, enum_query_type, precedence) /test/11.0_dbg_san/sql/item.cc:498
 
    #2 0x55924a96f17a in Item_func::print_op(String*, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:633
 
    #3 0x5592484d391a in Item_bool_rowready_func2::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.h:549
 
    #4 0x55924a4828c3 in Item::print_parenthesised(String*, enum_query_type, precedence) /test/11.0_dbg_san/sql/item.cc:498
 
    #5 0x55924a6366cd in Item_cond::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:5384
 
    #6 0x5592490221ed in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30916
 
    #7 0x55924ae5ebe8 in subselect_single_select_engine::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:4666
 
    #8 0x55924ae805b3 in Item_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:1081
 
    #9 0x55924a4828c3 in Item::print_parenthesised(String*, enum_query_type, precedence) /test/11.0_dbg_san/sql/item.cc:498
 
    #10 0x55924a63d77d in Item_func_not::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:211
 
    #11 0x55924901f913 in st_select_lex::print_item_list(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30618
 
    #12 0x5592490259f0 in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30837
 
    #13 0x55924ae5ebe8 in subselect_single_select_engine::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:4666
 
    #14 0x55924ae805b3 in Item_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:1081
 
    #15 0x55924ae81db6 in Item_in_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:3526
 
    #16 0x55924a96d486 in Item_func::print_args(String*, unsigned int, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:624
 
    #17 0x55924a96dc88 in Item_func::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:613
 
    #18 0x55924a6a2142 in Item_in_optimizer::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:1249
 
    #19 0x55924902239e in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30948
 
    #20 0x55924ae5ebe8 in subselect_single_select_engine::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:4666
 
    #21 0x55924901939b in TABLE_LIST::print(THD*, unsigned long long, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30408
 
    #22 0x55924901e4a0 in print_table_array /test/11.0_dbg_san/sql/sql_select.cc:30181
 
    #23 0x55924901e4a0 in print_join /test/11.0_dbg_san/sql/sql_select.cc:30337
 
    #24 0x559249025ad2 in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30850
 
    #25 0x55924ae5ebe8 in subselect_single_select_engine::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:4666
 
    #26 0x55924ae805b3 in Item_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:1081
 
    #27 0x55924ae81db6 in Item_in_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:3526
 
    #28 0x55924a96d486 in Item_func::print_args(String*, unsigned int, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:624
 
    #29 0x55924a96dc88 in Item_func::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:613
 
    #30 0x55924a6a2142 in Item_in_optimizer::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:1249
 
    #31 0x55924901f913 in st_select_lex::print_item_list(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30618
 
    #32 0x5592490259f0 in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30837
 
    #33 0x55924ae5ebe8 in subselect_single_select_engine::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:4666
 
    #34 0x55924ae805b3 in Item_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:1081
 
    #35 0x55924ae81db6 in Item_in_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:3526
 
    #36 0x55924a96d486 in Item_func::print_args(String*, unsigned int, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:624
 
    #37 0x55924a96dc88 in Item_func::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:613
 
    #38 0x55924a6a2142 in Item_in_optimizer::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:1249
 
    #39 0x55924902239e in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30948
 
    #40 0x559248a9a2c4 in st_select_lex_unit::print(String*, enum_query_type) /test/11.0_dbg_san/sql/sql_lex.cc:3697
 
    #41 0x55924901a786 in TABLE_LIST::print(THD*, unsigned long long, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30443
 
    #42 0x55924901e4a0 in print_table_array /test/11.0_dbg_san/sql/sql_select.cc:30181
 
    #43 0x55924901e4a0 in print_join /test/11.0_dbg_san/sql/sql_select.cc:30337
 
    #44 0x559249025ad2 in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30850
 
    #45 0x55924ae5ebe8 in subselect_single_select_engine::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:4666
 
    #46 0x55924ae805b3 in Item_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:1081
 
    #47 0x55924ae81db6 in Item_in_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:3526
 
    #48 0x55924a96d486 in Item_func::print_args(String*, unsigned int, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:624
 
    #49 0x55924a96dc88 in Item_func::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:613
 
    #50 0x55924a6a2142 in Item_in_optimizer::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:1249
 
    #51 0x55924a51724f in Item_cache_wrapper::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item.cc:8806
 
    #52 0x55924901f913 in st_select_lex::print_item_list(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30618
 
    #53 0x5592490259f0 in st_select_lex::print(THD*, String*, enum_query_type) /test/11.0_dbg_san/sql/sql_select.cc:30837
 
    #54 0x55924ae5ebe8 in subselect_single_select_engine::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:4666
 
    #55 0x55924ae805b3 in Item_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:1081
 
    #56 0x55924ae81db6 in Item_in_subselect::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_subselect.cc:3526
 
    #57 0x55924a96d486 in Item_func::print_args(String*, unsigned int, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:624
 
    #58 0x55924a96dc88 in Item_func::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:613
 
    #59 0x55924a6a2142 in Item_in_optimizer::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:1249
 
    #60 0x55924a51724f in Item_cache_wrapper::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item.cc:8806
 
    #61 0x55924a4828c3 in Item::print_parenthesised(String*, enum_query_type, precedence) /test/11.0_dbg_san/sql/item.cc:498
 
    #62 0x55924a96f873 in Item_func::print_op(String*, enum_query_type) /test/11.0_dbg_san/sql/item_func.cc:638
 
    #63 0x5592484d391a in Item_bool_rowready_func2::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.h:549
 
    #64 0x55924a482afc in Item::print_parenthesised(String*, enum_query_type, precedence) /test/11.0_dbg_san/sql/item.cc:498
 
    #65 0x55924a63d77d in Item_func_not::print(String*, enum_query_type) /test/11.0_dbg_san/sql/item_cmpfunc.cc:211
 
    #66 0x559248e71d38 in change_to_use_tmp_fields /test/11.0_dbg_san/sql/sql_select.cc:28421
 
    #67 0x559248fec94e in JOIN::make_aggr_tables_info() /test/11.0_dbg_san/sql/sql_select.cc:3744
 
    #68 0x55924906cc07 in JOIN::optimize_stage2() /test/11.0_dbg_san/sql/sql_select.cc:3350
 
    #69 0x55924907f97c in JOIN::optimize_inner() /test/11.0_dbg_san/sql/sql_select.cc:2603
 
    #70 0x55924908164e in JOIN::optimize() /test/11.0_dbg_san/sql/sql_select.cc:1905
 
    #71 0x559249081dd7 in 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*) /test/11.0_dbg_san/sql/sql_select.cc:5144
 
    #72 0x55924908651c in handle_select(THD*, LEX*, select_result*, unsigned long long) /test/11.0_dbg_san/sql/sql_select.cc:616
 
    #73 0x559248bf8a01 in execute_sqlcom_select /test/11.0_dbg_san/sql/sql_parse.cc:6279
 
    #74 0x559248c59ef5 in mysql_execute_command(THD*, bool) /test/11.0_dbg_san/sql/sql_parse.cc:3949
 
    #75 0x559248c89973 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /test/11.0_dbg_san/sql/sql_parse.cc:8014
 
    #76 0x559248c99707 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /test/11.0_dbg_san/sql/sql_parse.cc:1894
 
    #77 0x559248ca7542 in do_command(THD*, bool) /test/11.0_dbg_san/sql/sql_parse.cc:1407
 
    #78 0x55924967c8b5 in do_handle_one_connection(CONNECT*, bool) /test/11.0_dbg_san/sql/sql_connect.cc:1416
 
    #79 0x55924967ddd0 in handle_one_connection /test/11.0_dbg_san/sql/sql_connect.cc:1318
 
    #80 0x14c50e494b42 in start_thread nptl/pthread_create.c:442
 
    #81 0x14c50e5269ff  (/lib/x86_64-linux-gnu/libc.so.6+0x1269ff)
 
 
 
0x61900007cbb8 is located 56 bytes inside of 1040-byte region [0x61900007cb80,0x61900007cf90)
 
freed by thread T36 here:
 
    #0 0x55924830dfe7 in __interceptor_free (/test/UBASAN_MD120523-mariadb-11.0.2-linux-x86_64-dbg/bin/mariadbd+0x7963fe7)
 
    #1 0x55924ca25a61 in my_free /test/11.0_dbg_san/mysys/my_malloc.c:213
 
    #2 0x55924ca04091 in root_free /test/11.0_dbg_san/mysys/my_alloc.c:83
 
    #3 0x55924ca064e5 in free_root /test/11.0_dbg_san/mysys/my_alloc.c:513
 
    #4 0x559248f5b8f6 in free_tmp_table(THD*, TABLE*) /test/11.0_dbg_san/sql/sql_select.cc:22532
 
    #5 0x55924ae7af10 in subselect_hash_sj_engine::cleanup() /test/11.0_dbg_san/sql/item_subselect.cc:5486
 
    #6 0x55924ae5941b in Item_subselect::cleanup() /test/11.0_dbg_san/sql/item_subselect.cc:160
 
    #7 0x55924ae5f151 in Item_in_subselect::cleanup() /test/11.0_dbg_san/sql/item_subselect.cc:201
 
    #8 0x559248f5deb3 in st_join_table::cleanup() /test/11.0_dbg_san/sql/sql_select.cc:15693
 
    #9 0x5592490356d8 in JOIN::cleanup(bool) /test/11.0_dbg_san/sql/sql_select.cc:16168
 
    #10 0x5592493d04ff in st_select_lex::cleanup_all_joins(bool) /test/11.0_dbg_san/sql/sql_union.cc:2825
 
    #11 0x5592493d06c1 in st_select_lex::cleanup_all_joins(bool) /test/11.0_dbg_san/sql/sql_union.cc:2832
 
    #12 0x5592493d06c1 in st_select_lex::cleanup_all_joins(bool) /test/11.0_dbg_san/sql/sql_union.cc:2832
 
    #13 0x5592493d06c1 in st_select_lex::cleanup_all_joins(bool) /test/11.0_dbg_san/sql/sql_union.cc:2832
 
    #14 0x5592493d06c1 in st_select_lex::cleanup_all_joins(bool) /test/11.0_dbg_san/sql/sql_union.cc:2832
 
    #15 0x559249038f1d in JOIN::join_free() /test/11.0_dbg_san/sql/sql_select.cc:16090
 
    #16 0x55924909276d in do_select /test/11.0_dbg_san/sql/sql_select.cc:22827
 
    #17 0x55924909276d in JOIN::exec_inner() /test/11.0_dbg_san/sql/sql_select.cc:4900
 
    #18 0x559249093916 in JOIN::exec() /test/11.0_dbg_san/sql/sql_select.cc:4677
 
    #19 0x5592490820c1 in 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*) /test/11.0_dbg_san/sql/sql_select.cc:5158
 
    #20 0x5592489aa074 in mysql_derived_fill /test/11.0_dbg_san/sql/sql_derived.cc:1282
 
    #21 0x5592489aba30 in mysql_derived_optimize /test/11.0_dbg_san/sql/sql_derived.cc:1073
 
    #22 0x5592489a7ccf in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /test/11.0_dbg_san/sql/sql_derived.cc:200
 
    #23 0x55924908086a in JOIN::optimize_inner() /test/11.0_dbg_san/sql/sql_select.cc:2395
 
    #24 0x55924908164e in JOIN::optimize() /test/11.0_dbg_san/sql/sql_select.cc:1905
 
    #25 0x559248ac38ac in st_select_lex::optimize_unflattened_subqueries(bool) /test/11.0_dbg_san/sql/sql_lex.cc:4903
 
    #26 0x559249939119 in JOIN::optimize_constant_subqueries() /test/11.0_dbg_san/sql/opt_subselect.cc:5837
 
    #27 0x559249077c19 in JOIN::optimize_inner() /test/11.0_dbg_san/sql/sql_select.cc:2235
 
    #28 0x55924908164e in JOIN::optimize() /test/11.0_dbg_san/sql/sql_select.cc:1905
 
    #29 0x559248ac38ac in st_select_lex::optimize_unflattened_subqueries(bool) /test/11.0_dbg_san/sql/sql_lex.cc:4903
 
    #30 0x559249939119 in JOIN::optimize_constant_subqueries() /test/11.0_dbg_san/sql/opt_subselect.cc:5837
 
 
 
previously allocated by thread T36 here:
 
    #0 0x55924830e337 in __interceptor_malloc (/test/UBASAN_MD120523-mariadb-11.0.2-linux-x86_64-dbg/bin/mariadbd+0x7964337)
 
    #1 0x55924ca25703 in my_malloc /test/11.0_dbg_san/mysys/my_malloc.c:91
 
    #2 0x55924ca03f22 in root_alloc /test/11.0_dbg_san/mysys/my_alloc.c:71
 
    #3 0x55924ca05372 in alloc_root /test/11.0_dbg_san/mysys/my_alloc.c:337
 
    #4 0x559249b2efbf in Field::operator new(unsigned long, st_mem_root*) /test/11.0_dbg_san/sql/field.h:775
 
    #5 0x559249b2efbf in Type_handler_long::make_table_field_from_def(TABLE_SHARE*, st_mem_root*, st_mysql_const_lex_string const*, Record_addr const&, Bit_addr const&, Column_definition_attributes const*, unsigned int) const /test/11.0_dbg_san/sql/sql_type.cc:8139
 
    #6 0x559249b27c76 in Type_handler_int_result::make_table_field(st_mem_root*, st_mysql_const_lex_string const*, Record_addr const&, Type_all_attributes const&, TABLE_SHARE*) const /test/11.0_dbg_san/sql/sql_type.cc:3574
 
    #7 0x559249b9d602 in Type_handler::make_and_init_table_field(st_mem_root*, st_mysql_const_lex_string const*, Record_addr const&, Type_all_attributes const&, TABLE*) const /test/11.0_dbg_san/sql/sql_type.cc:3559
 
    #8 0x559248f37721 in Item::tmp_table_field_from_field_type(st_mem_root*, TABLE*) /test/11.0_dbg_san/sql/item.h:914
 
    #9 0x559248f37721 in Item::tmp_table_field_from_field_type_maybe_null(st_mem_root*, TABLE*, Tmp_field_src*, Tmp_field_param const*, bool) /test/11.0_dbg_san/sql/sql_select.cc:20353
 
    #10 0x55924838616a in Item_basic_value::create_tmp_field_ex(st_mem_root*, TABLE*, Tmp_field_src*, Tmp_field_param const*) /test/11.0_dbg_san/sql/item.h:3031
 
    #11 0x559248f3f1de in create_tmp_field(TABLE*, Item*, Item***, Field**, Field**, bool, bool, bool, bool) /test/11.0_dbg_san/sql/sql_select.cc:20642
 
    #12 0x559248f49bcd in Create_tmp_table::add_fields(THD*, TABLE*, TMP_TABLE_PARAM*, List<Item>&) /test/11.0_dbg_san/sql/sql_select.cc:21080
 
    #13 0x559248f6cdda in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool) /test/11.0_dbg_san/sql/sql_select.cc:21739
 
    #14 0x5592488f4946 in select_materialize_with_stats::create_result_table(THD*, List<Item>*, bool, unsigned long long, st_mysql_const_lex_string const*, bool, bool, bool, unsigned int) /test/11.0_dbg_san/sql/sql_class.cc:4291
 
    #15 0x55924aef7555 in subselect_hash_sj_engine::init(List<Item>*, unsigned int) /test/11.0_dbg_san/sql/item_subselect.cc:5247
 
    #16 0x55924aefac30 in Item_in_subselect::setup_mat_engine() /test/11.0_dbg_san/sql/item_subselect.cc:3667
 
    #17 0x55924994f455 in JOIN::choose_subquery_plan(unsigned long long) /test/11.0_dbg_san/sql/opt_subselect.cc:6844
 
    #18 0x55924904dbc1 in make_join_statistics /test/11.0_dbg_san/sql/sql_select.cc:6122
 
    #19 0x55924907f738 in JOIN::optimize_inner() /test/11.0_dbg_san/sql/sql_select.cc:2577
 
    #20 0x55924908164e in JOIN::optimize() /test/11.0_dbg_san/sql/sql_select.cc:1905
 
    #21 0x55924aefc11f in Item_in_subselect::optimize(double*, double*) /test/11.0_dbg_san/sql/item_subselect.cc:850
 
    #22 0x55924994a4fa in setup_jtbm_semi_joins(JOIN*, List<TABLE_LIST>*, List<Item>&) /test/11.0_dbg_san/sql/opt_subselect.cc:6532
 
    #23 0x55924907c8e5 in JOIN::optimize_inner() /test/11.0_dbg_san/sql/sql_select.cc:2356
 
    #24 0x55924908164e in JOIN::optimize() /test/11.0_dbg_san/sql/sql_select.cc:1905
 
    #25 0x559248ac38ac in st_select_lex::optimize_unflattened_subqueries(bool) /test/11.0_dbg_san/sql/sql_lex.cc:4903
 
    #26 0x559249939119 in JOIN::optimize_constant_subqueries() /test/11.0_dbg_san/sql/opt_subselect.cc:5837
 
    #27 0x559249077c19 in JOIN::optimize_inner() /test/11.0_dbg_san/sql/sql_select.cc:2235
 
    #28 0x55924908164e in JOIN::optimize() /test/11.0_dbg_san/sql/sql_select.cc:1905
 
    #29 0x559248ac38ac in st_select_lex::optimize_unflattened_subqueries(bool) /test/11.0_dbg_san/sql/sql_lex.cc:4903
 
    #30 0x559249939119 in JOIN::optimize_constant_subqueries() /test/11.0_dbg_san/sql/opt_subselect.cc:5837
 
    #31 0x559249077c19 in JOIN::optimize_inner() /test/11.0_dbg_san/sql/sql_select.cc:2235
 
 
 
Thread T36 created by T0 here:
 
    #0 0x5592482b2175 in pthread_create (/test/UBASAN_MD120523-mariadb-11.0.2-linux-x86_64-dbg/bin/mariadbd+0x7908175)
 
    #1 0x55924836898b in create_thread_to_handle_connection(CONNECT*) /test/11.0_dbg_san/sql/mysqld.cc:6129
 
    #2 0x559248375e67 in create_new_thread(CONNECT*) /test/11.0_dbg_san/sql/mysqld.cc:6191
 
    #3 0x5592483766e7 in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /test/11.0_dbg_san/sql/mysqld.cc:6253
 
    #4 0x559248377738 in handle_connections_sockets() /test/11.0_dbg_san/sql/mysqld.cc:6377
 
    #5 0x55924837eee7 in mysqld_main(int, char**) /test/11.0_dbg_san/sql/mysqld.cc:6024
 
    #6 0x559248353eca in main /test/11.0_dbg_san/sql/main.cc:34
 
    #7 0x14c50e429d8f in __libc_start_call_main ../sysdeps/nptl/libc_start_call_main.h:58
 
 
 
SUMMARY: AddressSanitizer: heap-use-after-free /test/11.0_dbg_san/sql/item.cc:7898 in Item_field::print(String*, enum_query_type)
 
Shadow bytes around the buggy address:
 
  0x0c3280007920: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c3280007930: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c3280007940: 00 00 00 fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280007950: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280007960: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
=>0x0c3280007970: fd fd fd fd fd fd fd[fd]fd fd fd fd fd fd fd fd
 
  0x0c3280007980: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280007990: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c32800079a0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c32800079b0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c32800079c0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
Shadow byte legend (one shadow byte represents 8 application bytes):
 
  Addressable:           00
 
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
 
  Freed heap region:       fd
 
  Stack left redzone:      f1
 
  Stack mid redzone:       f2
 
  Stack right redzone:     f3
 
  Stack after return:      f5
 
  Stack use after scope:   f8
 
  Global redzone:          f9
 
  Global init order:       f6
 
  Poisoned by user:        f7
 
  Container overflow:      fc
 
  Array cookie:            ac
 
  Intra object redzone:    bb
 
  ASan internal:           fe
 
  Left alloca redzone:     ca
 
  Right alloca redzone:    cb
 
  Shadow gap:              cc
 
==1035094==ABORTING

Setup:

Compiled with GCC >=7.5.0 (I use GCC 11.3.0) and:
 
    -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_UBSAN=ON -DWITH_RAPID=OFF -DWSREP_LIB_WITH_ASAN=ON
 
Set before execution:
 
    export ASAN_OPTIONS=quarantine_size_mb=512:atexit=0:detect_invalid_pointer_pairs=3:dump_instruction_bytes=1:abort_on_error=1:allocator_may_return_null=1

Both issues are confirmed present in:
MariaDB: 10.9.7 (dbg), 10.10.5 (dbg), 10.11.4 (dbg), 11.0.2 (dbg), 11.1.0 (dbg)

 Comments   
Comment by Roel Van de Paar [ 2023-06-08 ]

Second version of the testcase:

SELECT * FROM (SELECT x,0 FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x ) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x IN ((SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x ) AS x GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT NOT x)) AS x) AS x GROUP BY x,x IN (SELECT 1 WHERE x IN (SELECT 1 WHERE NOT x IN (1)))) AS x ) AS x GROUP BY NOT x IN (SELECT (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x GROUP BY x HAVING NOT 1) AS x GROUP BY x,x) AS x) AS x) IN ((SELECT (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x ) AS x GROUP BY x HAVING NOT x) IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x) GROUP BY x,x HAVING x IN (SELECT x IN (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x ) AS x GROUP BY x HAVING x IN (SELECT NOT (SELECT * FROM (SELECT * FROM (SELECT x IN (SELECT 1 AS x WHERE x IN ((SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 IN (1) AS x)))) AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x ) AS x GROUP BY x HAVING NOT NOT x)) AS x) AS x) AS x) AS x) AS x ) AS x) FROM (SELECT 1 AS x) AS x)))) WHERE NOT x IN (1))) AS x ) AS x) AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x ) AS x) AS x)));

Leads to:

11.1.0 4e5b771e980edfdad5c5414aa62c81d409d585a4 (Debug)

 
Core was generated by `/test/MD120523-mariadb-11.1.0-linux-x86_64-dbg/bin/mariadbd --no-defaults --cor'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  0x0000555a0b9c141e in Item_field::print (this=0x14a9080e1a18, 
    str=0x14a94c0aa360, query_type=QT_ORDINARY)
 
    at /test/11.1_dbg/sql/item.cc:7909
 
[Current thread is 1 (Thread 0x14a94c0ac640 (LWP 1750805))]
 
(gdb) bt
 
#0  0x0000555a0b9c141e in Item_field::print (this=0x14a9080e1a18, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item.cc:7909
 
#1  0x0000555a0b9e92ca in Item_equal::print (this=<optimized out>, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:7361
 
#2  0x0000555a0b746a35 in st_select_lex::print (this=0x14a908042f40, thd=thd@entry=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30361
 
#3  0x0000555a0baa2236 in subselect_single_select_engine::print (this=0x14a90805c048, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#4  0x0000555a0baa2bed in Item_subselect::print (this=0x14a90805beb8, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#5  0x0000555a0b9c1010 in Item::print_parenthesised (this=this@entry=0x14a90805beb8, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY, parent_prec=NEG_PRECEDENCE) at /test/11.1_dbg/sql/item.cc:498
 
#6  0x0000555a0b9e8bb3 in Item_func_not::print (this=0x14a90805c088, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:211
 
#7  0x0000555a0b746eab in st_select_lex::print (this=0x14a908042a88, thd=thd@entry=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30315
 
#8  0x0000555a0baa2236 in subselect_single_select_engine::print (this=0x14a90805d288, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#9  0x0000555a0baa2bed in Item_subselect::print (this=this@entry=0x14a90805d058, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#10 0x0000555a0baa2fa5 in Item_in_subselect::print (this=0x14a90805d058, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#11 0x0000555a0ba2c3b7 in Item_func::print_args (this=this@entry=0x14a9080d47e0, str=str@entry=0x14a94c0aa360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#12 0x0000555a0ba2c5a4 in Item_func::print (this=this@entry=0x14a9080d47e0, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#13 0x0000555a0b9ee5a8 in Item_in_optimizer::print (this=0x14a9080d47e0, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#14 0x0000555a0b746a8d in st_select_lex::print (this=0x14a90803f868, thd=thd@entry=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30393
 
#15 0x0000555a0baa2236 in subselect_single_select_engine::print (this=0x14a90805d508, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#16 0x0000555a0b745c4e in TABLE_LIST::print (this=0x14a9080deba0, thd=thd@entry=0x14a908000d58, eliminated_tables=eliminated_tables@entry=0, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30087
 
#17 0x0000555a0b7465fe in print_table_array (query_type=QT_ORDINARY, end=0x14a908333160, table=0x14a908333150, str=0x14a94c0aa360, eliminated_tables=0, thd=0x14a908000d58) at /test/11.1_dbg/sql/sql_select.cc:29860
 
#18 print_join (thd=thd@entry=0x14a908000d58, eliminated_tables=0, str=str@entry=0x14a94c0aa360, tables=0x14a90803c290, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30016
 
#19 0x0000555a0b746f75 in st_select_lex::print (this=0x14a90803c0d0, thd=thd@entry=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30342
 
#20 0x0000555a0baa2236 in subselect_single_select_engine::print (this=0x14a908060270, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#21 0x0000555a0baa2bed in Item_subselect::print (this=this@entry=0x14a908060040, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#22 0x0000555a0baa2fa5 in Item_in_subselect::print (this=0x14a908060040, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#23 0x0000555a0ba2c3b7 in Item_func::print_args (this=this@entry=0x14a9080d4d38, str=str@entry=0x14a94c0aa360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#24 0x0000555a0ba2c5a4 in Item_func::print (this=this@entry=0x14a9080d4d38, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#25 0x0000555a0b9ee5a8 in Item_in_optimizer::print (this=0x14a9080d4d38, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#26 0x0000555a0b746eab in st_select_lex::print (this=0x14a90803bb08, thd=thd@entry=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30315
 
#27 0x0000555a0baa2236 in subselect_single_select_engine::print (this=0x14a908061430, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#28 0x0000555a0baa2bed in Item_subselect::print (this=this@entry=0x14a908061200, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#29 0x0000555a0baa2fa5 in Item_in_subselect::print (this=0x14a908061200, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#30 0x0000555a0ba2c3b7 in Item_func::print_args (this=this@entry=0x14a9080d5110, str=str@entry=0x14a94c0aa360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#31 0x0000555a0ba2c5a4 in Item_func::print (this=this@entry=0x14a9080d5110, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#32 0x0000555a0b9ee5a8 in Item_in_optimizer::print (this=0x14a9080d5110, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#33 0x0000555a0b746a8d in st_select_lex::print (this=this@entry=0x14a908038990, thd=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30393
 
#34 0x0000555a0b695600 in st_select_lex_unit::print (this=0x14a908061480, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_lex.cc:3671
 
#35 0x0000555a0b745ea0 in TABLE_LIST::print (this=0x14a908061cf8, thd=thd@entry=0x14a908000d58, eliminated_tables=eliminated_tables@entry=0, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30122
 
#36 0x0000555a0b7465fe in print_table_array (query_type=QT_ORDINARY, end=0x14a908333150, table=0x14a908333148, str=0x14a94c0aa360, eliminated_tables=0, thd=0x14a908000d58) at /test/11.1_dbg/sql/sql_select.cc:29860
 
#37 print_join (thd=thd@entry=0x14a908000d58, eliminated_tables=0, str=str@entry=0x14a94c0aa360, tables=0x14a908038090, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30016
 
#38 0x0000555a0b746f75 in st_select_lex::print (this=0x14a908037ed0, thd=thd@entry=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30342
 
#39 0x0000555a0baa2236 in subselect_single_select_engine::print (this=0x14a908063e58, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#40 0x0000555a0baa2bed in Item_subselect::print (this=this@entry=0x14a908063c28, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#41 0x0000555a0baa2fa5 in Item_in_subselect::print (this=0x14a908063c28, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#42 0x0000555a0ba2c3b7 in Item_func::print_args (this=this@entry=0x14a9080d8588, str=str@entry=0x14a94c0aa360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#43 0x0000555a0ba2c5a4 in Item_func::print (this=this@entry=0x14a9080d8588, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#44 0x0000555a0b9ee5a8 in Item_in_optimizer::print (this=0x14a9080d8588, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#45 0x0000555a0b9d0283 in Item_cache_wrapper::print (this=0x14a908206660, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item.cc:8814
 
#46 0x0000555a0b746eab in st_select_lex::print (this=0x14a908033fd8, thd=thd@entry=0x14a908000d58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/sql_select.cc:30315
 
#47 0x0000555a0baa2236 in subselect_single_select_engine::print (this=0x14a908069468, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:4668
 
#48 0x0000555a0baa2bed in Item_subselect::print (this=this@entry=0x14a908069e58, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:1081
 
#49 0x0000555a0baa2fa5 in Item_in_subselect::print (this=0x14a908069e58, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_subselect.cc:3528
 
#50 0x0000555a0ba2c3b7 in Item_func::print_args (this=this@entry=0x14a9080d8b40, str=str@entry=0x14a94c0aa360, from=from@entry=0, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:624
 
#51 0x0000555a0ba2c5a4 in Item_func::print (this=this@entry=0x14a9080d8b40, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:613
 
#52 0x0000555a0b9ee5a8 in Item_in_optimizer::print (this=0x14a9080d8b40, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:1249
 
#53 0x0000555a0b9d0283 in Item_cache_wrapper::print (this=0x14a9083326e0, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item.cc:8814
 
#54 0x0000555a0b9c1010 in Item::print_parenthesised (this=this@entry=0x14a9083326e0, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY, parent_prec=BETWEEN_PRECEDENCE) at /test/11.1_dbg/sql/item.cc:498
 
#55 0x0000555a0ba2c7a3 in Item_func::print_op (this=0x14a9080dc780, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_func.cc:638
 
#56 0x0000555a0b5d7e97 in Item_bool_rowready_func2::print (this=<optimized out>, str=<optimized out>, query_type=<optimized out>) at /test/11.1_dbg/sql/item_cmpfunc.h:550
 
#57 0x0000555a0b9c1044 in Item::print_parenthesised (this=this@entry=0x14a9080dc780, str=str@entry=0x14a94c0aa360, query_type=query_type@entry=QT_ORDINARY, parent_prec=<optimized out>) at /test/11.1_dbg/sql/item.cc:498
 
#58 0x0000555a0b9e8bb3 in Item_func_not::print (this=0x14a90806ab00, str=0x14a94c0aa360, query_type=QT_ORDINARY) at /test/11.1_dbg/sql/item_cmpfunc.cc:211
 
#59 0x0000555a0b70c3f1 in change_to_use_tmp_fields (thd=0x14a908000d58, ref_pointer_array=<optimized out>, res_selected_fields=@0x14a90806b940: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x555a0cbb3c80 <end_of_list>, last = 0x14a90806b940, elements = 0}, <No data fields>}, res_all_fields=@0x14a90806b8f8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x555a0cbb3c80 <end_of_list>, last = 0x14a90806b8f8, elements = 0}, <No data fields>}, elements=2, all_fields=@0x14a90806b8e0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14a9080dc9c0, last = 0x14a9080145f8, elements = 3}, <No data fields>}) at /test/11.1_dbg/sql/sql_select.cc:28103
 
#60 0x0000555a0b73f75d in JOIN::make_aggr_tables_info (this=this@entry=0x14a90806b530) at /test/11.1_dbg/sql/sql_select.cc:3743
 
#61 0x0000555a0b7510fb in JOIN::optimize_stage2 (this=this@entry=0x14a90806b530) at /test/11.1_dbg/sql/sql_select.cc:3349
 
#62 0x0000555a0b753367 in JOIN::optimize_inner (this=this@entry=0x14a90806b530) at /test/11.1_dbg/sql/sql_select.cc:2602
 
#63 0x0000555a0b753814 in JOIN::optimize (this=this@entry=0x14a90806b530) at /test/11.1_dbg/sql/sql_select.cc:1902
 
#64 0x0000555a0b75391d in mysql_select (thd=thd@entry=0x14a908000d58, tables=<optimized out>, fields=@0x14a9080142e0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14a9080145f8, last = 0x14a908083d48, elements = 2}, <No data fields>}, conds=0x0, og_num=1, order=0x0, group=0x14a90806abb8, having=0x0, proc_param=0x0, select_options=2164525824, result=0x14a90806b508, unit=0x14a908004fa8, select_lex=0x14a908014020) at /test/11.1_dbg/sql/sql_select.cc:5143
 
#65 0x0000555a0b754102 in handle_select (thd=thd@entry=0x14a908000d58, lex=lex@entry=0x14a908004ec8, result=result@entry=0x14a90806b508, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/11.1_dbg/sql/sql_select.cc:611
 
#66 0x0000555a0b6ba3bc in execute_sqlcom_select (thd=thd@entry=0x14a908000d58, all_tables=0x14a90802d9a8) at /test/11.1_dbg/sql/sql_parse.cc:6024
 
#67 0x0000555a0b6c5a1c in mysql_execute_command (thd=thd@entry=0x14a908000d58, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/11.1_dbg/sql/sql_parse.cc:3944
 
#68 0x0000555a0b6cbfad in mysql_parse (thd=thd@entry=0x14a908000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14a94c0ab230) at /test/11.1_dbg/sql/sql_parse.cc:7760
 
#69 0x0000555a0b6ce141 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14a908000d58, packet=packet@entry=0x14a90800ae49 "SELECT * FROM (SELECT x,0 FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x ) AS x WHERE x IN (SELECT * F"..., packet_length=packet_length@entry=1633, blocking=blocking@entry=true) at /test/11.1_dbg/sql/sql_class.h:242
 
#70 0x0000555a0b6cff9d in do_command (thd=0x14a908000d58, blocking=blocking@entry=true) at /test/11.1_dbg/sql/sql_parse.cc:1405
 
#71 0x0000555a0b821e5a in do_handle_one_connection (connect=<optimized out>, connect@entry=0x555a0dab2ec8, put_in_cache=put_in_cache@entry=true) at /test/11.1_dbg/sql/sql_connect.cc:1416
 
#72 0x0000555a0b8220b9 in handle_one_connection (arg=0x555a0dab2ec8) at /test/11.1_dbg/sql/sql_connect.cc:1318
 
#73 0x000014a963094b43 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#74 0x000014a963126a00 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Both issues are confirmed present in:
MariaDB: 10.9.7 (dbg), 10.10.5 (dbg), 10.11.4 (dbg), 11.0.2 (dbg), 11.1.0 (dbg)

Comment by Roel Van de Paar [ 2023-06-08 ]

All UniqueID's observed across versions 10.9+

SIGSEGV|Item_field::print|Item_equal::print|st_select_lex::print|subselect_single_select_engine::print
 
SIGSEGV|Item_field::print|Item::print_parenthesised|Item_func::print_op|Item_bool_rowready_func2::print
 
SIGSEGV|Item_field::print|Item_func::print_op|Item_cond::print|st_select_lex::print
 
ASAN|heap-use-after-free|sql/item.cc|Item_field::print|Item_equal::print|st_select_lex::print|subselect_single_select_engine::print
 
ASAN|heap-use-after-free|sql/item.cc|Item_field::print|Item::print_parenthesised|Item_func::print_op|Item_bool_rowready_func2::print
 
ASAN|heap-use-after-free|sql/item.cc|Item_field::print|Item_equal::print|st_select_lex::print|Item_subselect::print

Comment by Roel Van de Paar [ 2023-06-26 ]

An additional testcase which also crashes optimized builds:

SET optimizer_trace=1;
 
SELECT 1 FROM (SELECT x,0 FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x IN ((SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT NOT x)) AS x) AS x GROUP BY x,x IN (SELECT 1 WHERE x IN (SELECT 1 WHERE NOT x IN (1)))) AS x) AS x GROUP BY NOT x IN (SELECT (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x GROUP BY x HAVING NOT 1) AS x GROUP BY x,x) AS x) AS x) IN ((SELECT (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING NOT x) IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x) GROUP BY x,x HAVING x IN (SELECT x IN (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING x IN (SELECT NOT (SELECT * FROM (SELECT * FROM (SELECT x IN (SELECT 1 AS x WHERE x IN ((SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 IN (1) AS x)))) AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING NOT NOT x)) AS x) AS x) AS x) AS x) AS x) AS x) FROM (SELECT 1 AS x) AS x)))) WHERE NOT x IN (1))) AS x) AS x) AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x) AS x) AS x)));

Which leads to one of the previously seen SIGSEGV's on debug, and to this new stack on optimized builds:

11.1.2 3883eb63dc5e663558571c33d086c9fd3aa0cf8f (Optimized)

 
Core was generated by `/test/MD220623-mariadb-11.1.2-linux-x86_64-opt/bin/mariadbd --no-defaults --cor'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  0x000055fb5b2264b2 in Item_field::print (this=0x148d440fb5a0, 
    str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item.cc:7909
 
[Current thread is 1 (Thread 0x148ddc0ab640 (LWP 584263))]
 
(gdb) bt
 
#0  0x000055fb5b2264b2 in Item_field::print (this=0x148d440fb5a0, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item.cc:7909
 
#1  0x000055fb5b27eef6 in Item_func::print_op (this=0x148d44126f18, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_func.cc:633
 
#2  0x000055fb5b245a38 in Item_cond::print (this=0x148d44125478, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_cmpfunc.cc:5388
 
#3  0x000055fb5b01a1ef in st_select_lex::print (this=<optimized out>, thd=0x148d44000c68, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/sql_select.cc:31138
 
#4  0x000055fb5b2f0af1 in Item_subselect::print (this=0x148d4405f200, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_subselect.cc:1081
 
#5  0x000055fb5b27eb6b in Item_func::print_args (this=this@entry=0x148d440de768, str=str@entry=0x148ddc0a8c70, from=from@entry=0, query_type=query_type@entry=1033) at /test/11.1_opt/sql/item_func.cc:624
 
#6  0x000055fb5b27ec6e in Item_func::print (this=0x148d440de768, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_func.cc:613
 
#7  0x000055fb5b019b43 in st_select_lex::print_item_list (this=this@entry=0x148d4403add0, thd=thd@entry=0x148d44000c68, str=str@entry=0x148ddc0a8c70, query_type=query_type@entry=1033) at /test/11.1_opt/sql/sql_select.cc:30855
 
#8  0x000055fb5b01a429 in st_select_lex::print (this=<optimized out>, thd=0x148d44000c68, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/sql_select.cc:31059
 
#9  0x000055fb5b2f0af1 in Item_subselect::print (this=0x148d440603d0, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_subselect.cc:1081
 
#10 0x000055fb5b27eb6b in Item_func::print_args (this=this@entry=0x148d440debb8, str=str@entry=0x148ddc0a8c70, from=from@entry=0, query_type=query_type@entry=1033) at /test/11.1_opt/sql/item_func.cc:624
 
#11 0x000055fb5b27ec6e in Item_func::print (this=0x148d440debb8, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_func.cc:613
 
#12 0x000055fb5b01a248 in st_select_lex::print (this=<optimized out>, thd=0x148d44000c68, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/sql_select.cc:31170
 
#13 0x000055fb5af7d743 in st_select_lex_unit::print (this=0x148d44060650, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/sql_lex.cc:3672
 
#14 0x000055fb5b018d1b in TABLE_LIST::print (this=0x148d44060ec8, thd=0x148d44000c68, str=0x148ddc0a8c70, query_type=1033, eliminated_tables=<optimized out>) at /test/11.1_opt/sql/sql_select.cc:30665
 
#15 0x000055fb5b019571 in print_table_array (query_type=1033, end=<optimized out>, table=0x148d4412dc60, str=0x148ddc0a8c70, eliminated_tables=0, thd=0x148d44000c68) at /test/11.1_opt/sql/sql_select.cc:30403
 
#16 print_join (thd=thd@entry=0x148d44000c68, eliminated_tables=0, str=str@entry=0x148ddc0a8c70, tables=0x148d44037348, query_type=query_type@entry=1033) at /test/11.1_opt/sql/sql_select.cc:30559
 
#17 0x000055fb5b01a477 in st_select_lex::print (this=<optimized out>, thd=0x148d44000c68, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/sql_select.cc:31072
 
#18 0x000055fb5b2f0af1 in Item_subselect::print (this=0x148d44062dd8, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_subselect.cc:1081
 
#19 0x000055fb5b27eb6b in Item_func::print_args (this=this@entry=0x148d440e21a8, str=str@entry=0x148ddc0a8c70, from=from@entry=0, query_type=query_type@entry=1033) at /test/11.1_opt/sql/item_func.cc:624
 
#20 0x000055fb5b27ec6e in Item_func::print (this=0x148d440e21a8, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_func.cc:613
 
#21 0x000055fb5b245a38 in Item_cond::print (this=0x148d4412cb10, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_cmpfunc.cc:5388
 
#22 0x000055fb5b27eb6b in Item_func::print_args (this=this@entry=0x148d4412cc08, str=str@entry=0x148ddc0a8c70, from=from@entry=0, query_type=query_type@entry=1033) at /test/11.1_opt/sql/item_func.cc:624
 
#23 0x000055fb5b27ec6e in Item_func::print (this=0x148d4412cc08, str=0x148ddc0a8c70, query_type=1033) at /test/11.1_opt/sql/item_func.cc:613
 
#24 0x000055fb5b1850c9 in Json_writer::add_str (this=0x148d44007790, item=0x148d4412cc08) at /test/11.1_opt/sql/opt_trace.cc:743
 
#25 0x000055fb5b18525c in Json_value_helper::add_str (item=0x148d4412cc08, this=<synthetic pointer>) at /test/11.1_opt/sql/my_json_writer.h:335
 
#26 Json_writer_object::add (value=0x148d4412cc08, name=0x55fb5b88b545 "resulting_condition", this=<synthetic pointer>) at /test/11.1_opt/sql/my_json_writer.h:546
 
#27 trace_condition (thd=<optimized out>, name=name@entry=0x55fb5b88bdcc "WHERE", transform_type=transform_type@entry=0x55fb5b88bd86 "substitute_best_equal", item=0x148d4412cc08, table_name=table_name@entry=0x0) at /test/11.1_opt/sql/opt_trace.cc:623
 
#28 0x000055fb5b01fe02 in JOIN::optimize_stage2 (this=0x148d440954a0) at /test/11.1_opt/sql/sql_select.cc:2777
 
#29 0x000055fb5b023fa9 in JOIN::optimize_inner (this=0x148d440954a0) at /test/11.1_opt/sql/sql_select.cc:2642
 
#30 0x000055fb5b02451a in JOIN::optimize (this=this@entry=0x148d440954a0) at /test/11.1_opt/sql/sql_select.cc:1942
 
#31 0x000055fb5af82134 in st_select_lex::optimize_unflattened_subqueries (this=0x148d440114b0, const_only=const_only@entry=true) at /test/11.1_opt/sql/sql_lex.cc:4870
 
#32 0x000055fb5b112345 in JOIN::optimize_constant_subqueries (this=this@entry=0x148d4406a690) at /test/11.1_opt/sql/opt_subselect.cc:5898
 
#33 0x000055fb5b022fbf in JOIN::optimize_inner (this=0x148d4406a690) at /test/11.1_opt/sql/sql_select.cc:2272
 
#34 0x000055fb5b02451a in JOIN::optimize (this=this@entry=0x148d4406a690) at /test/11.1_opt/sql/sql_select.cc:1942
 
#35 0x000055fb5b024611 in mysql_select (thd=0x148d44000c68, tables=0x148d4402cd48, fields=<optimized out>, conds=0x0, og_num=1, order=0x0, group=0x148d44069d58, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x148d4406a668, unit=0x148d44004cf8, select_lex=0x148d440114b0) at /test/11.1_opt/sql/sql_select.cc:5225
 
#36 0x000055fb5b024dc4 in handle_select (thd=thd@entry=0x148d44000c68, lex=lex@entry=0x148d44004c18, result=result@entry=0x148d4406a668, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/11.1_opt/sql/sql_select.cc:627
 
#37 0x000055fb5af9c745 in execute_sqlcom_select (thd=0x148d44000c68, all_tables=0x148d4402cd48) at /test/11.1_opt/sql/sql_parse.cc:6030
 
#38 0x000055fb5afab069 in mysql_execute_command (thd=0x148d44000c68, is_called_from_prepared_stmt=<optimized out>) at /test/11.1_opt/sql/sql_parse.cc:3944
 
#39 0x000055fb5afac4a4 in mysql_parse (rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, thd=0x148d44000c68) at /test/11.1_opt/sql/sql_parse.cc:7769
 
#40 mysql_parse (thd=0x148d44000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/11.1_opt/sql/sql_parse.cc:7691
 
#41 0x000055fb5afaeaf2 in dispatch_command (command=COM_QUERY, thd=0x148d44000c68, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/11.1_opt/sql/sql_parse.cc:1989
 
#42 0x000055fb5afb0370 in do_command (thd=0x148d44000c68, blocking=blocking@entry=true) at /test/11.1_opt/sql/sql_parse.cc:1405
 
#43 0x000055fb5b0cbf27 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55fb5de7bf48, put_in_cache=put_in_cache@entry=true) at /test/11.1_opt/sql/sql_connect.cc:1416
 
#44 0x000055fb5b0cc1fd in handle_one_connection (arg=0x55fb5de7bf48) at /test/11.1_opt/sql/sql_connect.cc:1318
 
#45 0x0000148df3c94b43 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#46 0x0000148df3d26a00 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

And we also get this new ASAN issue UniqueID/stack on optimized builds:

11.0.2 368dd22a816f3b437bccd0b9ff28b9de9b1abf0a (Optimized, UBASAN)

 
ASAN|heap-use-after-free|sql/item.cc|Item_field::print|Item_equal::print|st_select_lex::print|Item_subselect::print

Comment by Roel Van de Paar [ 2023-06-26 ] 

SET SESSION optimizer_trace=1;
 
SELECT * FROM (SELECT x,0 FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x IN ((SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT NOT x)) AS x) AS x GROUP BY x,x IN (SELECT 1 WHERE x IN (SELECT 1 WHERE NOT x IN (1)))) AS x) AS x GROUP BY NOT x IN (SELECT (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x GROUP BY x HAVING NOT 1) AS x GROUP BY x,x) AS x) AS x) IN ((SELECT (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING NOT x) IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x) GROUP BY x,x HAVING x IN (SELECT x IN (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING x IN (SELECT NOT (SELECT * FROM (SELECT * FROM (SELECT x IN (SELECT 1 AS x WHERE x IN ((SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 IN (1) AS x)))) AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x GROUP BY x HAVING NOT NOT x)) AS x) AS x) AS x) AS x) AS x) AS x) FROM (SELECT 1 AS x) AS x)))) WHERE NOT x IN (1))) AS x) AS x) AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x) AS x) AS x)));

Provides similar UniqueID's/stacks, with this new one added (optimized build):

11.1.2 3883eb63dc5e663558571c33d086c9fd3aa0cf8f (Optimized)

 
SIGSEGV|Item_field::print|Item_equal::print|st_select_lex::print|Item_subselect::print

Comment by Sergei Petrunia [ 2023-08-02 ]

Looking at the patch...
One thing that is not good is that after the patch, EXPLAIN EXTENDED output shows references to tables that are not printed.

For example, in subselect_no_opts.result: 

Note	1003	/* select#1 */ select 1 AS `1` from dual having (/* select#3 */ select `b`.`a`) = 1

Note select b.a but there is no table "a".

Another case:

EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12;
 
...
 
Warnings:
 
Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`1` AS `1` from `test`.`t1` `t11`

There are references to table t12 but the table is not present in the query.

Comment by Sergei Petrunia [ 2023-08-02 ]

A smaller example which still causes the use-after-free:

(EDIT: "still causes" here meant "causes the error before the patch". After the patch, it doesn't cause it.)

SELECT 
  1 IN 
  ((
 
     SELECT 
       1 IN (SELECT 1 AS x0
 
             FROM 
               (
 
                SELECT * 
                FROM (SELECT 1 AS x) AS x5 
                GROUP BY x,x 
                HAVING
 
                  x IN (
 
                    SELECT * 
                    FROM one AS x1 
                    WHERE 
                      x IN (SELECT 1 AS x 
                            FROM one AS x3 
                            GROUP BY x 
                            HAVING 
                              x IN (SELECT 0 FROM one AS x4)
 
                           )
 
                  )
 
               ) AS x6
 
            ) 
     FROM
 
       one
 
  ))

Comment by Sergei Petrunia [ 2023-08-02 ]

The interesting part is that if I change the aliases in the innermost query like this:

--- /tmp/query-with-error.sql   2023-08-02 14:18:50.733410130 +0300
 
+++ /tmp/query-no-error.sql     2023-08-02 14:18:24.617222867 +0300
 
@@ -11,15 +11,15 @@
 
                 HAVING
 
                   x IN (
 
                     SELECT * 
                     FROM one AS x1 
                     WHERE 
-                      x IN (SELECT 1 AS x 
+                      x IN (SELECT 1 AS x10 
                             FROM one AS x3 
-                            GROUP BY x 
+                            GROUP BY x10 
                             HAVING 
-                              x IN (SELECT 0 FROM one AS x4)
 
+                              x10 IN (SELECT 0 FROM one AS x4)
 
                            )
 
                   )
 
                ) AS x6
 
             ) 
      FROM

I don't get errors anymore even if this should be an equivalent change.

Comment by Sergei Petrunia [ 2023-08-03 ]

The above queries use table "one": 

create table one ( x int);
 
insert into one values(1);

Comment by Sergei Petrunia [ 2023-08-03 ]

We crash when printing this condition:

  "`<subquery7>`.x = x1.x and <cache>(1) = x1.x"

"<subquery7>" is the temporary table that's already free'd.

The condition itself is the WHERE clause for this select:

  ... (
 
    SELECT * 
    FROM one AS x1 
    WHERE 
      x IN (SELECT 1 AS x  -- this is subquery7
 
            FROM one AS x3 
            GROUP BY x 
            HAVING 
              x IN (SELECT 0 FROM one AS x4)
 
           )
 
  )

the x IN (...) is converted into a non-merged semi-join.
The select is a two-table join with this WHERE condition:

  {
 
    "best_join_order": ["x1", "<subquery7>"]
 
  },
 
  {
 
    "substitute_best_equal": {
 
      "condition": "WHERE",
 
      "resulting_condition": "`<subquery7>`.x = x1.x and <cache>(1) = x1.x"
 
    }
 
  },

Comment by Sergei Petrunia [ 2023-08-03 ]

mdev31432-debug-temptables.sql - debugged a few examples.
Temporary tables representing derived tables are freed "late", when the query's tables are closed.
Temporary tables representing semi-join nest are freed at an earlier stage in JOIN::cleanup(). The same is done for certain other kinds of temptables.

But then it does look like QT_DONT_ACCESS_TMP_TABLES should be used (almost?) at any point in the query...

We learn that some tables are constant at optimization phase. But optimization phase can compute item values which means executing subqueries which means calling JOIN::cleanup(full=true) for uncorrelated subqueries, which may drop temp.tables.

That is, not passing QT_DONT_ACCESS_TMP_TABLES doesn't make any difference before the optimization phase. During the optimization phase, QT_DONT_ACCESS_TMP_TABLES must be passed already...

Comment by Sergei Petrunia [ 2023-08-03 ]

One odd thing in Johnston's patch: it changes the way fields of non-temporary tables are printed.
Example:

--- a/mysql-test/main/derived_cond_pushdown.result
 
+++ b/mysql-test/main/derived_cond_pushdown.result
 
@@ -8159,7 +8159,7 @@ EXPLAIN
 
 {
 
   "query_block": {
 
     "select_id": 1,
 
-    "const_condition": "0 or <in_optimizer>(2,<exists>(subquery#3))",
 
+    "const_condition": "0 or <in_optimizer>(t1.a,<exists>(subquery#3))",
 
     "nested_loop": [
 
       {
 
         "table": {
 
           "table_name": "t1",
 
           "access_type": "system",
 
           "rows": 1,
 
           "filtered": 100
 
         }
 
      },

Here, t1 is a regular MyISAM table, non temporary... why is that...

(I'll continue to review)

Comment by Sergei Petrunia [ 2023-08-04 ]

Indeed, debugging the above query and checking how

 "const_condition": "0 or <in_optimizer>(2,<exists>(subquery#3))",

is printed:

  Thread 6 "mysqld" hit Breakpoint 3, Item_field::print(...
 
(gdb) print refers_to_temp_table
 
  $18 = false
 
(gdb) print field
 
  $19 = (Field_long *) 0x7fffe0169788
 
(gdb) p field->table->alias.Ptr
 
  $20 = 0x7fffe009d878 "t1"
 
(gdb) p field->table->const_table
 
  $21 = true
 
(gdb) p query_type & (QT_NO_DATA_EXPANSION | QT_VIEW_INTERNAL | QT_ACCESS_TMP_TABLES)
 
  $22 = 8192

Comment by Sergei Petrunia [ 2023-08-04 ]

Fixed the logic in Item_field::print(): 

commit 2795e221b7f5447971d86ecf68173c25dc740337 (HEAD -> bb-10.9-MDEV-31432, origin/bb-10.9-MDEV-31432, origin/HEAD)
 
Author: Sergei Petrunia <sergey@mariadb.com>
 
Date:   Fri Aug 4 13:40:11 2023 +0300
 
 
 
    Fixup for patch for MDEV-31432
 
    
    Fix the logic in Item_field::print

This fixed the issue with derived_cond_pushdown mentioned above and similar issues.
But changes of this kind

-Note   1003    /* select#1 */ select 1 AS `1` from dual having (/* select#3 */ select 1) = 1
 
+Note   1003    /* select#1 */ select 1 AS `1` from dual having (/* select#3 */ select `b`.`a`) = 1

are still there.

Comment by Sergei Petrunia [ 2023-08-04 ]

What if we treat temp.tables differently depending on what kind of temp.tables they are ?

  • Derived temp.tables survive until close_thread_tables_for_query() call (see attached file mdev31432-debug-temptables.sql for example)
  • For other kinds of temptables, we can never assume that they are still there. So Item_field::print() can NOT access them at all.
    Implemented this approach in "Fixup for patch for MDEV-31432 part 2" and pushed to the branch. Let this sink in and let BB run...
Comment by Roel Van de Paar [ 2023-08-07 ]

Please also test this optimizer_trace=1 testcase against any fix as it produces an additional stack:

SET optimizer_trace=1;
 
SELECT * FROM (SELECT x,0 FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x IN ((SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT x)) GROUP BY x HAVING NOT NOT x)) AS x) AS x GROUP BY x,x IN (SELECT 1 WHERE x IN (SELECT 1 WHERE NOT x IN (1)))) AS x WHERE x IN (1)) AS x GROUP BY NOT x IN (SELECT (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x GROUP BY x HAVING NOT 1) AS x WHERE x IN (1) GROUP BY x,x) AS x) AS x) IN ((SELECT (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x GROUP BY x HAVING NOT x) IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT x) GROUP BY x,x HAVING x IN (SELECT x IN (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT * FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x WHERE x IN (1) GROUP BY x HAVING x IN (SELECT NOT (SELECT * FROM (SELECT * FROM (SELECT x IN (SELECT 1 AS x WHERE x IN ((SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 IN (1) AS x)))) AS x FROM (SELECT 1 AS x FROM (SELECT * FROM (SELECT * FROM (SELECT x,0 FROM (SELECT 1 AS x) AS x WHERE x IN (SELECT 1 AS x FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x WHERE x IN (1)) AS x WHERE x IN (1) GROUP BY x HAVING NOT NOT x)) AS x) AS x) AS x) AS x) AS x WHERE x IN (1)) AS x) FROM (SELECT 1 AS x) AS x)))) WHERE NOT x IN (1))) AS x WHERE x IN (1)) AS x WHERE x IN (1) GROUP BY x HAVING NOT NOT x) AS x FROM (SELECT * FROM (SELECT * FROM (SELECT 1 AS x FROM (SELECT 1 AS x) AS x) AS x WHERE x IN (1)) AS x) AS x WHERE x IN (1))));


ASAN|heap-use-after-free|sql/item.cc|Item_field::print|Item_func::print_op|Item_cond::print|st_select_lex::print

Comment by Sergei Petrunia [ 2023-08-08 ]

... why is the fix for 10.9 and not for 10.8?

Debugging a simpler example on 10.8 (https://gist.github.com/spetrunia/f7370a6e8a0c113e47b2623dab971a02) , I can see that the field in temp table is printed like so

  void Item_temptable_field::print(String *str, enum_query_type query_type)
 
  {
 
    /*
 
      Item_ident doesn't have references to the underlying Field/TABLE objects,
 
      so it's ok to use the following:
 
    */
 
=>  Item_ident::print(str, query_type);                                                                                              
  }

so on 10.8 we don't enter Item_field::print for temptable fields.

while on 10.9 has no Item_temptable_field, it uses regular Item_field with refers_to_temp_table member.

Comment by Sergei Petrunia [ 2023-08-08 ]

Please also test this optimizer_trace=1 testcase against ...

Roel, the current fix passes it.

Comment by Roel Van de Paar [ 2023-08-09 ]

Thank you psergei for confirming!

Comment by Sergei Petrunia [ 2023-08-14 ]

https://github.com/MariaDB/server/tree/bb-10.9-MDEV-31432-v2 has commit 32e0f7d4e3c316ff83ea5eba575705b350140bcb - the final alternative patch.

Comment by Michael Widenius [ 2023-08-14 ]

Review done, minor comment typos should be fixed

Comment by Sergei Petrunia [ 2023-08-14 ]

Pushed the fix.
Note: the fix is not related to the linked MDEV-24658 (still crashes,I've checked), or to MDEV-28622

Comment by Sergei Petrunia [ 2023-08-17 ]

(link to final commit: https://github.com/MariaDB/server/commit/8aaacb5509a7981062d3ad0331cef212e3d79d5d )

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