[MDEV-29350] Crash when IN predicand is used in eliminated GROUP BY clause Created: 2022-08-22  Updated: 2022-09-05  Resolved: 2022-08-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.10, 10.7.6, 10.8.5, 10.9.3, 10.10.2

Type: Bug Priority: Blocker
Reporter: Roel Van de Paar Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Blocks
Relates

 Description   

The testcase was simplified to not use table DDL; the same issue is present when actual tables are used.

The crash between dbg vs opt is slightly different as of the 4th frame:

SIGSEGV|st_select_lex_node::exclude_from_tree|st_select_lex_node::exclude|Item_subselect::eliminate_subselect_processor|Item_in_subselect::walk  # opt
 
SIGSEGV|st_select_lex_node::exclude_from_tree|st_select_lex_node::exclude|Item_subselect::eliminate_subselect_processor|Item_subselect::walk  # dbg

Testcase:

SELECT 1 WHERE 1 IN (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3);

Leads to:

10.10.2 87e8463e0454a04c2bbaa38d44227c491fb07dc1 (Optimized)

 
Core was generated by `/test/MD200822-mariadb-10.10.2-linux-x86_64-opt/bin/mysqld --no-defaults --core'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  0x0000565229b1291b in st_select_lex_node::exclude_from_tree (
 
    this=this@entry=0x153e1c013298) at /test/10.10_opt/sql/sql_lex.cc:3225
 
3225	  if ((*prev= next))
 
[Current thread is 1 (Thread 0x153e5dc56700 (LWP 110797))]
 
(gdb) bt
 
#0  0x0000565229b1291b in st_select_lex_node::exclude_from_tree (this=this@entry=0x153e1c013298) at /test/10.10_opt/sql/sql_lex.cc:3225
 
#1  0x0000565229b12996 in st_select_lex_node::exclude (this=0x153e1c013298) at /test/10.10_opt/sql/sql_lex.cc:3237
 
#2  0x0000565229e5984e in Item_subselect::eliminate_subselect_processor (this=0x153e1c013a98, arg=<optimized out>) at /test/10.10_opt/sql/item_subselect.cc:395
 
#3  0x0000565229e67c78 in Item_in_subselect::walk (this=0x153e1c01a368, processor=&virtual table offset 968, walk_subquery=false, arg=0x0) at /test/10.10_opt/sql/item_subselect.h:757
 
#4  0x0000565229a681ec in Item_args::walk_args (arg=0x0, walk_subquery=false, processor=<optimized out>, this=0x153e1c022520) at /test/10.10_opt/sql/item.h:2757
 
#5  Item_func_or_sum::walk (this=0x153e1c0224b0, processor=&virtual table offset 968, walk_subquery=false, arg=0x0) at /test/10.10_opt/sql/item.h:5445
 
#6  0x0000565229daf121 in Item_direct_view_ref::walk (this=0x153e1c024c90, processor=<optimized out>, walk_subquery=<optimized out>, arg=0x0) at /test/10.10_opt/sql/item.h:6052
 
#7  0x0000565229ba4f26 in remove_redundant_subquery_clauses (subq_select_lex=0x153e1c010fb0) at /test/10.10_opt/sql/sql_select.cc:818
 
#8  JOIN::prepare (this=0x153e1c01e730, tables_init=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=skip_order_by@entry=false, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /test/10.10_opt/sql/sql_select.cc:1478
 
#9  0x0000565229e610e8 in subselect_single_select_engine::prepare (this=0x153e1c01cb38, thd=0x153e1c000c58) at /test/10.10_opt/sql/sql_lex.h:1367
 
#10 0x0000565229e60748 in Item_subselect::fix_fields (this=this@entry=0x153e1c01d4a0, thd_param=thd_param@entry=0x153e1c000c58, ref=ref@entry=0x153e1c01e310) at /test/10.10_opt/sql/item_subselect.cc:295
 
#11 0x0000565229e60ac9 in Item_in_subselect::fix_fields (this=0x153e1c01d4a0, thd_arg=0x153e1c000c58, ref=0x153e1c01e310) at /test/10.10_opt/sql/item_subselect.cc:3588
 
#12 0x0000565229ad7d5f in Item::fix_fields_if_needed (ref=0x153e1c01e310, thd=0x153e1c000c58, this=0x153e1c01d4a0) at /test/10.10_opt/sql/item.h:1142
 
#13 Item::fix_fields_if_needed (ref=0x153e1c01e310, thd=0x153e1c000c58, this=0x153e1c01d4a0) at /test/10.10_opt/sql/item.h:1142
 
#14 Item::fix_fields_if_needed_for_scalar (ref=0x153e1c01e310, thd=0x153e1c000c58, this=0x153e1c01d4a0) at /test/10.10_opt/sql/item.h:1148
 
#15 Item::fix_fields_if_needed_for_bool (ref=0x153e1c01e310, thd=0x153e1c000c58, this=0x153e1c01d4a0) at /test/10.10_opt/sql/item.h:1152
 
#16 setup_conds (thd=thd@entry=0x153e1c000c58, tables=tables@entry=0x0, leaves=@0x153e1c010c40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x56522acc06b0 <end_of_list>, last = 0x153e1c010c40, elements = 0}, <No data fields>}, conds=conds@entry=0x153e1c01e310) at /test/10.10_opt/sql/sql_base.cc:8801
 
#17 0x0000565229ba40ea in setup_without_group (reserved=0x153e1c010de4, hidden_group_fields=0x153e1c01e1d7, win_funcs=@0x153e1c010e78: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x56522acc06b0 <end_of_list>, last = 0x153e1c010e78, elements = 0}, <No data fields>}, win_specs=@0x153e1c010e60: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x56522acc06b0 <end_of_list>, last = 0x153e1c010e60, elements = 0}, <No data fields>}, group=0x0, order=0x0, conds=0x153e1c01e310, all_fields=@0x153e1c01e228: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153e1c010f20, last = 0x153e1c010f20, elements = 1}, <No data fields>}, fields=@0x153e1c010cc8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153e1c010f20, last = 0x153e1c010f20, elements = 1}, <No data fields>}, leaves=@0x153e1c010c40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x56522acc06b0 <end_of_list>, last = 0x153e1c010c40, elements = 0}, <No data fields>}, tables=0x0, ref_pointer_array=<optimized out>, thd=0x153e1c000c58) at /test/10.10_opt/sql/sql_select.cc:884
 
#18 JOIN::prepare (this=0x153e1c01de98, tables_init=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=<optimized out>, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /test/10.10_opt/sql/sql_select.cc:1456
 
#19 0x0000565229bb654f in mysql_select (thd=0x153e1c000c58, tables=0x0, fields=@0x153e1c010cc8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153e1c010f20, last = 0x153e1c010f20, elements = 1}, <No data fields>}, conds=0x153e1c01d4a0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x153e1c01cc30, unit=0x153e1c004cd0, select_lex=0x153e1c010a28) at /test/10.10_opt/sql/sql_select.cc:5045
 
#20 0x0000565229bb67f7 in handle_select (thd=thd@entry=0x153e1c000c58, lex=lex@entry=0x153e1c004bf8, result=result@entry=0x153e1c01cc30, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.10_opt/sql/sql_select.cc:581
 
#21 0x0000565229b384f1 in execute_sqlcom_select (thd=0x153e1c000c58, all_tables=0x153e1c012b90) at /test/10.10_opt/sql/sql_parse.cc:6261
 
#22 0x0000565229b46138 in mysql_execute_command (thd=0x153e1c000c58, is_called_from_prepared_stmt=<optimized out>) at /test/10.10_opt/sql/sql_parse.cc:3945
 
#23 0x0000565229b336f5 in mysql_parse (rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, thd=0x153e1c000c58) at /test/10.10_opt/sql/sql_parse.cc:8035
 
#24 mysql_parse (thd=0x153e1c000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/10.10_opt/sql/sql_parse.cc:7957
 
#25 0x0000565229b3f20a in dispatch_command (command=COM_QUERY, thd=0x153e1c000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/10.10_opt/sql/sql_class.h:1339
 
#26 0x0000565229b41132 in do_command (thd=0x153e1c000c58, blocking=blocking@entry=true) at /test/10.10_opt/sql/sql_parse.cc:1407
 
#27 0x0000565229c593af in do_handle_one_connection (connect=<optimized out>, connect@entry=0x56522c3501c8, put_in_cache=put_in_cache@entry=true) at /test/10.10_opt/sql/sql_connect.cc:1418
 
#28 0x0000565229c5968d in handle_one_connection (arg=0x56522c3501c8) at /test/10.10_opt/sql/sql_connect.cc:1312
 
#29 0x0000153e76c9a609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#30 0x0000153e76886133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.10.2 87e8463e0454a04c2bbaa38d44227c491fb07dc1 (Debug)

 
Core was generated by `/test/MD200822-mariadb-10.10.2-linux-x86_64-dbg/bin/mysqld --no-defaults --core'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  0x000055cf3655bb75 in st_select_lex_node::exclude_from_tree (
 
    this=this@entry=0x1538180167b8) at /test/10.10_dbg/sql/sql_lex.cc:3225
 
3225	  if ((*prev= next))
 
[Current thread is 1 (Thread 0x15386200c700 (LWP 111085))]
 
(gdb) bt
 
#0  0x000055cf3655bb75 in st_select_lex_node::exclude_from_tree (this=this@entry=0x1538180167b8) at /test/10.10_dbg/sql/sql_lex.cc:3225
 
#1  0x000055cf3655bbed in st_select_lex_node::exclude (this=0x1538180167b8) at /test/10.10_dbg/sql/sql_lex.cc:3237
 
#2  0x000055cf3698964c in Item_subselect::eliminate_subselect_processor (this=0x153818016fb8, arg=<optimized out>) at /test/10.10_dbg/sql/item_subselect.cc:395
 
#3  0x000055cf36989b18 in Item_subselect::walk (this=0x153818016fb8, processor=<optimized out>, walk_subquery=<optimized out>, argument=0x0) at /test/10.10_dbg/sql/item_subselect.cc:782
 
#4  0x000055cf3699c5ce in Item_in_subselect::walk (this=0x15381801dbc8, processor=&virtual table offset 968, walk_subquery=false, arg=0x0) at /test/10.10_dbg/sql/item_subselect.h:757
 
#5  0x000055cf364903f9 in Item_args::walk_args (arg=0x0, walk_subquery=false, processor=<optimized out>, this=0x153818026020) at /test/10.10_dbg/sql/item.h:2757
 
#6  Item_func_or_sum::walk (this=0x153818025fb0, processor=<optimized out>, walk_subquery=false, arg=0x0) at /test/10.10_dbg/sql/item.h:5445
 
#7  0x000055cf368b42ca in Item_direct_view_ref::walk (this=0x153818028828, processor=&virtual Item::eliminate_subselect_processor(void*), walk_subquery=<optimized out>, arg=0x0) at /test/10.10_dbg/sql/item.h:6052
 
#8  0x000055cf3660864e in remove_redundant_subquery_clauses (subq_select_lex=0x1538180144d0) at /test/10.10_dbg/sql/sql_select.cc:818
 
#9  JOIN::prepare (this=0x153818022008, tables_init=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=skip_order_by@entry=false, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /test/10.10_dbg/sql/sql_select.cc:1478
 
#10 0x000055cf3698fe91 in subselect_single_select_engine::prepare (this=0x153818020398, thd=0x153818000db8) at /test/10.10_dbg/sql/sql_lex.h:1367
 
#11 0x000055cf3698f35f in Item_subselect::fix_fields (this=this@entry=0x153818020d70, thd_param=thd_param@entry=0x153818000db8, ref=ref@entry=0x153818021be8) at /test/10.10_dbg/sql/item_subselect.cc:295
 
#12 0x000055cf3698f7a4 in Item_in_subselect::fix_fields (this=0x153818020d70, thd_arg=0x153818000db8, ref=0x153818021be8) at /test/10.10_dbg/sql/item_subselect.cc:3588
 
#13 0x000055cf36510972 in Item::fix_fields_if_needed (ref=0x153818021be8, thd=0x153818000db8, this=0x153818020d70) at /test/10.10_dbg/sql/item.h:1152
 
#14 Item::fix_fields_if_needed_for_scalar (ref=0x153818021be8, thd=0x153818000db8, this=0x153818020d70) at /test/10.10_dbg/sql/item.h:1148
 
#15 Item::fix_fields_if_needed_for_bool (ref=0x153818021be8, thd=0x153818000db8, this=0x153818020d70) at /test/10.10_dbg/sql/item.h:1152
 
#16 setup_conds (thd=thd@entry=0x153818000db8, tables=tables@entry=0x0, leaves=@0x153818014160: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55cf37b5ac20 <end_of_list>, last = 0x153818014160, elements = 0}, <No data fields>}, conds=conds@entry=0x153818021be8) at /test/10.10_dbg/sql/sql_base.cc:8801
 
#17 0x000055cf36607f0f in setup_without_group (reserved=0x153818014304, hidden_group_fields=0x153818021aaf, win_funcs=@0x153818014398: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55cf37b5ac20 <end_of_list>, last = 0x153818014398, elements = 0}, <No data fields>}, win_specs=@0x153818014380: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55cf37b5ac20 <end_of_list>, last = 0x153818014380, elements = 0}, <No data fields>}, group=0x0, order=0x0, conds=0x153818021be8, all_fields=@0x153818021b00: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153818014440, last = 0x153818014440, elements = 1}, <No data fields>}, fields=@0x1538180141e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153818014440, last = 0x153818014440, elements = 1}, <No data fields>}, leaves=@0x153818014160: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55cf37b5ac20 <end_of_list>, last = 0x153818014160, elements = 0}, <No data fields>}, tables=0x0, ref_pointer_array=<optimized out>, thd=0x153818000db8) at /test/10.10_dbg/sql/sql_select.cc:884
 
#18 JOIN::prepare (this=this@entry=0x153818021768, tables_init=tables_init@entry=0x0, conds_init=conds_init@entry=0x153818020d70, og_num=og_num@entry=0, order_init=order_init@entry=0x0, skip_order_by=skip_order_by@entry=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x153818013f48, unit_arg=0x153818004ff0) at /test/10.10_dbg/sql/sql_select.cc:1456
 
#19 0x000055cf3661f184 in mysql_select (thd=thd@entry=0x153818000db8, tables=0x0, fields=@0x1538180141e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153818014440, last = 0x153818014440, elements = 1}, <No data fields>}, conds=0x153818020d70, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x153818020490, unit=0x153818004ff0, select_lex=0x153818013f48) at /test/10.10_dbg/sql/sql_select.cc:5045
 
#20 0x000055cf3661f3a2 in handle_select (thd=thd@entry=0x153818000db8, lex=lex@entry=0x153818004f18, result=result@entry=0x153818020490, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.10_dbg/sql/sql_select.cc:581
 
#21 0x000055cf365895a6 in execute_sqlcom_select (thd=thd@entry=0x153818000db8, all_tables=0x1538180160b0) at /test/10.10_dbg/sql/sql_parse.cc:6261
 
#22 0x000055cf365958c7 in mysql_execute_command (thd=thd@entry=0x153818000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.10_dbg/sql/sql_parse.cc:3945
 
#23 0x000055cf36583882 in mysql_parse (thd=thd@entry=0x153818000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x15386200b330) at /test/10.10_dbg/sql/sql_parse.cc:8035
 
#24 0x000055cf36590e6a in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x153818000db8, packet=packet@entry=0x15381800b6e9 "SELECT 1 WHERE 1 IN (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3)", packet_length=packet_length@entry=159, blocking=blocking@entry=true) at /test/10.10_dbg/sql/sql_class.h:1339
 
#25 0x000055cf36593574 in do_command (thd=0x153818000db8, blocking=blocking@entry=true) at /test/10.10_dbg/sql/sql_parse.cc:1407
 
#26 0x000055cf366f51da in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55cf39046988, put_in_cache=put_in_cache@entry=true) at /test/10.10_dbg/sql/sql_connect.cc:1418
 
#27 0x000055cf366f56e3 in handle_one_connection (arg=0x55cf39046988) at /test/10.10_dbg/sql/sql_connect.cc:1312
 
#28 0x000015387b488609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#29 0x000015387b074133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.4.27 (dbg), 10.4.27 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.2 (dbg), 10.9.2 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (dbg), 10.11.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.3.37 (dbg), 10.3.37 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

 Comments   
Comment by Roel Van de Paar [ 2022-08-22 ]

Example testcase which uses a table:

CREATE TABLE t (c INT);
 
SELECT 1 FROM (SELECT 1 AS c) AS v12 WHERE c IN (SELECT 1 FROM (SELECT 1, (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (WITH v2 AS (SELECT 1) SELECT 1 FROM (SELECT 1) AS v3 JOIN (SELECT 1) AS v5) AS v6) AS v7 FROM (SELECT 1) AS v8) AS v9 GROUP BY v7);

Comment by Roel Van de Paar [ 2022-08-22 ]

When the query is reduced further in some areas, the MDEV-28502 crash is observed instead. For example, see this testcase.

Comment by Alice Sherepa [ 2022-08-23 ]

This is regression after commit c2300d06f7845f51db6318c2fdc 10.3 (MDEV-28617 Crash with INSERT...SELECT using derived table in GROUP BY clause).

on 10.3 also:

SELECT 1 from dual WHERE 1 IN (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3);

10.3 c7f8cfc9e733517cff4aaa6f6e

 
220823 15:04:21 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.3.37-MariaDB-debug-log
 
 
 
sigaction.c:0(__restore_rt)[0x7f76b1804420]
 
sql/sql_lex.cc:2597(st_select_lex_node::exclude_from_tree())[0x559939c09c72]
 
sql/sql_lex.cc:2617(st_select_lex_node::exclude())[0x559939c09cd6]
 
sql/item_subselect.cc:383(Item_subselect::eliminate_subselect_processor(void*))[0x55993a60115c]
 
sql/item_subselect.cc:718(Item_subselect::walk(bool (Item::*)(void*), bool, void*))[0x55993a602b5e]
 
sql/item_subselect.h:751(Item_in_subselect::walk(bool (Item::*)(void*), bool, void*))[0x55993a63daeb]
 
sql/item.h:2278(Item_args::walk_args(bool (Item::*)(void*), bool, void*))[0x559939b21e43]
 
sql/item.h:4825(Item_func_or_sum::walk(bool (Item::*)(void*), bool, void*))[0x559939b22d40]
 
sql/item.h:5398(Item_direct_view_ref::walk(bool (Item::*)(void*), bool, void*))[0x55993a499b4b]
 
sql/sql_select.cc:616(remove_redundant_subquery_clauses(st_select_lex*))[0x559939d13422]
 
sql/sql_select.cc:1198(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x559939d19c98]
 
sql/item_subselect.cc:3808(subselect_single_select_engine::prepare(THD*))[0x55993a6263ec]
 
sql/item_subselect.cc:282(Item_subselect::fix_fields(THD*, Item**))[0x55993a600217]
 
sql/item_subselect.cc:3471(Item_in_subselect::fix_fields(THD*, Item**))[0x55993a6231c1]
 
sql/item.h:829(Item::fix_fields_if_needed(THD*, Item**))[0x5599399fb793]
 
sql/item.h:833(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5599399fb7cd]
 
sql/item.h:838(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x559939b21c5f]
 
sql/sql_base.cc:8350(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x559939b19435]
 
sql/sql_select.cc:675(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x559939d13a3a]
 
sql/sql_select.cc:1172(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x559939d19a8a]
 
sql/sql_select.cc:4352(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*))[0x559939d3b65e]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x559939d119cd]
 
sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x559939c826dd]
 
sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x559939c70718]
 
sql/sql_parse.cc:7871(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x559939c8c43a]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x559939c63317]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x559939c5fe5a]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55993a033ee5]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x55993a03379f]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55993b664a17]
 
nptl/pthread_create.c:478(start_thread)[0x7f76b17f8609]
 
 
 
Query (0x62b000000410): SELECT 1 from dual WHERE 1 IN (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3)

Comment by Igor Babaev [ 2022-08-23 ]

The following queries cause crashes of the same kind:

SELECT 1 from dual WHERE 1 >= ANY (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3); 
SELECT 1 from dual WHERE 1 < ALL (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3); 
SELECT 1 from dual WHERE EXISTS (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) IN (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3);

as well as the queries

SELECT 1 from dual WHERE 1 IN (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) >= ANY (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3);
 
SELECT 1 from dual WHERE 1 IN (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) < ALL (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3);

and the queries

SELECT 1 from dual WHERE EXISTS (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) >= ANY (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3);
 
SELECT 1 from dual WHERE EXISTS (SELECT 1 FROM (SELECT (SELECT 1 FROM (SELECT 1) AS v1) < ALL (SELECT 1 FROM (SELECT 1) AS v2) AS v3 FROM (SELECT 1) AS v4) AS v5 GROUP BY v3);

Comment by Igor Babaev [ 2022-08-24 ]

Let's take the select query from the test case for bug MDEV-28617, but instead of using a derived table in the group by clause let's use an IN predicand whose left operand is a subquery.

select b from t2   where exists (select c from t3 
                                                  group by (select a from t1 where a = 1) IN (select d from t4)
 
                                                 );

Here the table t4 is defined as follows:

create table t4 (d int);

.
This query crashes for the same reason as the queries from the previous comment.

Let's see in debugger how exactly it happens.
At the prepare phase the function remove_redundant_subquery_clauses() finds out that. The group by clause can be eliminated. The function not only eliminates the clause but traverses the items from the clause excluding all subselects used in these items from the unit tree representing the query to prevent execution of these subselects.
For the above query by the time when traversal of the expression from the GROUP BY list

(select a from t1 where a = 1) IN (select d from t4)

is performed it is already transformed into the item:

<in_optimizer>((subquery#3),<exists>(subquery#4))

The subselect (select a from t1 where a = 1) is referenced in (subquery#3), and in <exists>(subquery#4). So when traversing the GROUP BY item we try to exclude (select a from t1 where a = 1) from the tree twice. The second attempt causes a crash.

Comment by Igor Babaev [ 2022-08-24 ]

This bug had been masked by the bug MDEV-28617 until a fix for the latter
that properly excluded units was pushed into 10.3.

Comment by Oleksandr Byelkin [ 2022-08-25 ]

OK to push

Comment by Igor Babaev [ 2022-08-30 ]

A fix for this bug was pushed into 10.3

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