Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28621

group by optimization incorrectly removing subquery where subject buried in a function

Details

    Description

      poc:

      CREATE TABLE v1169 ( v1170 FLOAT NOT NULL ) ;
       INSERT INTO v1169 ( v1170 ) VALUES ( 40 ) ;
       UPDATE v1169 SET v1170 = -2147483648 WHERE v1170 = 5 ;
       INSERT INTO v1169 ( v1170 ) VALUES ( -128 ) , ( 52 ) ;
       WITH v1172 AS ( SELECT v1170 FROM ( SELECT v1170 FROM v1169 GROUP BY v1170 ) AS v1171 ) SELECT v1170 FROM v1172 WHERE v1170 BETWEEN FALSE AND ( ( ( v1170 OR NOT v1170 ) BETWEEN ( ( ( NOT ( ( 90778113.000000 ^ 90656932.000000 AND ( v1170 NOT IN ( NOT ( NOT ( 'x' = TRUE AND v1170 = 61 ) ) ) AND v1170 IN ( ( SELECT v1170 FROM v1169 WHERE ( FALSE <= -128 BETWEEN 10 AND 34 , v1170 ) IN ( WITH v1176 AS ( SELECT v1174 FROM ( SELECT ( SELECT v1170 FROM ( SELECT ( v1170 NOT IN ( 11097710.000000 , 12206111.000000 NOT BETWEEN 'x' AND 'x' ) AND v1170 NOT IN ( 2147483647 % v1170 ) ) , CASE WHEN v1170 % 30004927.000000 THEN 'x' ELSE v1170 END / 46 FROM v1169 UNION SELECT v1170 , v1170 FROM v1169 ) AS v1173 ) * 0 AS v1174 FROM v1169 ) AS v1175 ) SELECT ( v1174 NOT IN ( ( NOT ( 'x' / v1174 = v1174 + CASE v1174 WHEN 0 THEN v1174 ELSE ( WITH RECURSIVE v1177 ( v1178 ) AS ( SELECT v1170 FROM v1169 ) SELECT 81155100.000000 AS v1179 FROM v1177 ORDER BY v1178 DESC LIMIT 1 OFFSET 1 ) - 0 END AND v1174 = 'x' ) ) IS NULL ) AND v1174 NOT IN ( 33 ^ v1174 ) ) , v1174 + v1174 FROM v1176 GROUP BY v1174 ) ) * 2147483647 ) ) = -32768 ) * NULL ) ) ) ) AND 719937.000000 ) ) ;
      

      output:
      mysqld: /sql/item_subselect.cc:766: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.

      The full error log is in the attachment.

      Attachments

        Issue Links

          Activity

            nobody Shihao Wen created issue -
            danblack Daniel Black added a comment -

            Confirmed on 10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243 . UPDATE not needed.

            danblack Daniel Black added a comment - Confirmed on 10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243 . UPDATE not needed.
            danblack Daniel Black made changes -
            Field Original Value New Value
            Fix Version/s 10.3 [ 22126 ]
            danblack Daniel Black made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            Security Developers [ 10400 ]
            serg Sergei Golubchik made changes -
            Status Confirmed [ 10101 ] Open [ 1 ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa added a comment -

            repeatable on 10.2-10.9 with InnoDB/Myisam

            CREATE TABLE t1 (i int) ;
            INSERT INTO t1 VALUES (1),(2),(3);
             
            SELECT 1 FROM t1 
            WHERE i in 
            (	SELECT  a+1 
            	FROM 
             	(SELECT (SELECT i FROM (SELECT 1 FROM t1) dt) AS a FROM t1) dt2 
             	GROUP BY a
            );
            

            10.3 7d3d3838c1b8af98a9704

            mysqld: /10.3/src/sql/item_subselect.cc:766: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
            220525 10:31:53 [ERROR] mysqld got signal 6 ;
             
            Server version: 10.3.36-MariaDB-debug-log
             
            mysys/stacktrace.c:174(my_print_stacktrace)[0x556ab37f6af1]
            sql/signal_handler.cc:221(handle_fatal_signal)[0x556ab2453742]
            sql/item_subselect.cc:768(Item_subselect::exec())[0x556ab266b6fa]
            sql/item_subselect.cc:1415(Item_singlerow_subselect::val_int())[0x556ab2671c77]
            sql/item.cc:8646(Item_direct_ref::val_int())[0x556ab24e403f]
            sql/item.h:5434(Item_direct_view_ref::val_int())[0x556ab250269b]
            sql/item_func.cc:1212(Item_func_plus::int_op())[0x556ab25a4a4d]
            sql/item_func.h:510(Item_func_hybrid_field_type::val_int_from_int_op())[0x556ab2204b98]
            sql/sql_type.cc:3537(Type_handler_int_result::Item_func_hybrid_field_type_val_int(Item_func_hybrid_field_type*) const)[0x556ab21ee3f8]
            sql/item_func.h:574(Item_func_hybrid_field_type::val_int())[0x556ab1ca2433]
            sql/item.h:1629(Item::update_null_value())[0x556ab1a48bdc]
            sql/item_func.h:185(Item_func::is_null())[0x556ab1bf8935]
            sql/sql_select.cc:11811(end_sj_materialize(JOIN*, st_join_table*, bool))[0x556ab1ddb06f]
            sql/sql_select.cc:20112(evaluate_join_record(JOIN*, st_join_table*, int))[0x556ab1e145b1]
            sql/sql_select.cc:19885(sub_select(JOIN*, st_join_table*, bool))[0x556ab1e12ec9]
            sql/opt_subselect.cc:5445(join_tab_execution_startup(st_join_table*))[0x556ab2189e25]
            sql/sql_select.cc:19874(sub_select(JOIN*, st_join_table*, bool))[0x556ab1e12bc9]
            sql/sql_select.cc:20112(evaluate_join_record(JOIN*, st_join_table*, int))[0x556ab1e145b1]
            sql/sql_select.cc:19885(sub_select(JOIN*, st_join_table*, bool))[0x556ab1e12ec9]
            sql/sql_select.cc:19423(do_select(JOIN*, Procedure*))[0x556ab1e10e88]
            sql/sql_select.cc:4151(JOIN::exec_inner())[0x556ab1da3a6d]
            sql/sql_select.cc:3946(JOIN::exec())[0x556ab1da13ea]
            sql/sql_select.cc:4356(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*))[0x556ab1da4ee8]
            sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x556ab1d7b449]
            sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x556ab1cec191]
            sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x556ab1cda1cc]
            sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x556ab1cf5eee]
            sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x556ab1cccdcb]
            sql/sql_parse.cc:1398(do_command(THD*))[0x556ab1cc990e]
            sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x556ab209cef1]
            sql/sql_connect.cc:1309(handle_one_connection)[0x556ab209c7ab]
            perfschema/pfs.cc:1871(pfs_spawn_thread)[0x556ab36cc209]
            nptl/pthread_create.c:478(start_thread)[0x7f794b8a2609]
            
            

            220525  8:33:56 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.5.16-MariaDB
             
            sql/signal_handler.cc:225(handle_fatal_signal)[0x55b05562d287]
            sigaction.c:0(__restore_rt)[0x7f3cf0d17630]
            sql/sql_lex.cc:3409(st_select_lex::get_offset())[0x55b0553f3f10]
            sql/sql_lex.cc:4187(st_select_lex_unit::set_limit(st_select_lex*))[0x55b0553f5f6b]
            sql/item_subselect.cc:4013(subselect_single_select_engine::exec())[0x55b0556e7c17]
            sql/item_subselect.cc:842(Item_subselect::exec())[0x55b0556e640a]
            sql/item_subselect.cc:1461(Item_singlerow_subselect::val_int())[0x55b0556e6c6e]
            sql/item.cc:8571(Item_direct_ref::val_int())[0x55b05564225f]
            sql/item_func.cc:1114(Item_func_plus::int_op())[0x55b05569f77d]
            sql/item_func.h:185(Item_func::is_null())[0x55b0553d4075]
            sql/sql_select.cc:12638(end_sj_materialize(JOIN*, st_join_table*, bool))[0x55b05545a449]
            sql/sql_class.h:4115(THD::get_stmt_da())[0x55b05544badb]
            sql/sql_select.cc:20929(sub_select(JOIN*, st_join_table*, bool))[0x55b05545121f]
            sql/opt_subselect.cc:5677(join_tab_execution_startup(st_join_table*))[0x55b0555559a1]
            sql/sql_select.cc:20905(sub_select(JOIN*, st_join_table*, bool))[0x55b0554511ad]
            sql/sql_class.h:4115(THD::get_stmt_da())[0x55b05544badb]
            sql/sql_select.cc:20929(sub_select(JOIN*, st_join_table*, bool))[0x55b05545121f]
            sql/sql_select.cc:20454(do_select)[0x55b0554806d7]
            sql/sql_select.cc:4321(JOIN::exec())[0x55b055480a83]
            sql/sql_select.cc:4799(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*))[0x55b05547e9ee]
            sql/sql_select.cc:456(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55b05547f5aa]
            sql/sql_parse.cc:6315(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55b0552decfd]
            sql/sql_parse.cc:4005(mysql_execute_command(THD*))[0x55b055422c9e]
            sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55b05542540c]
            sql/sql_audit.h:169(mysql_audit_general)[0x55b0554283a4]
            sql/sql_parse.cc:1376(do_command(THD*))[0x55b055429c97]
            sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55b055517082]
            sql/sql_connect.cc:1318(handle_one_connection)[0x55b055517354]
            perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55b0558ae137]
            pthread_create.c:0(start_thread)[0x7f3cf0d0fea5]
            ??:0(__clone)[0x7f3cf022ab0d]
             
            Query (0x7f3cb8010ae0): SELECT 1 FROM t1 
            WHERE i in 
            (SELECT  a+1 
            FROM 
             (SELECT (SELECT i FROM (SELECT 1 FROM t1) dt) AS a FROM t1) dt2 
             GROUP BY a
            )
            

            alice Alice Sherepa added a comment - repeatable on 10.2-10.9 with InnoDB/Myisam CREATE TABLE t1 (i int ) ; INSERT INTO t1 VALUES (1),(2),(3);   SELECT 1 FROM t1 WHERE i in ( SELECT a+1 FROM ( SELECT ( SELECT i FROM ( SELECT 1 FROM t1) dt) AS a FROM t1) dt2 GROUP BY a ); 10.3 7d3d3838c1b8af98a9704 mysqld: /10.3/src/sql/item_subselect.cc:766: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed. 220525 10:31:53 [ERROR] mysqld got signal 6 ;   Server version: 10.3.36-MariaDB-debug-log   mysys/stacktrace.c:174(my_print_stacktrace)[0x556ab37f6af1] sql/signal_handler.cc:221(handle_fatal_signal)[0x556ab2453742] sql/item_subselect.cc:768(Item_subselect::exec())[0x556ab266b6fa] sql/item_subselect.cc:1415(Item_singlerow_subselect::val_int())[0x556ab2671c77] sql/item.cc:8646(Item_direct_ref::val_int())[0x556ab24e403f] sql/item.h:5434(Item_direct_view_ref::val_int())[0x556ab250269b] sql/item_func.cc:1212(Item_func_plus::int_op())[0x556ab25a4a4d] sql/item_func.h:510(Item_func_hybrid_field_type::val_int_from_int_op())[0x556ab2204b98] sql/sql_type.cc:3537(Type_handler_int_result::Item_func_hybrid_field_type_val_int(Item_func_hybrid_field_type*) const)[0x556ab21ee3f8] sql/item_func.h:574(Item_func_hybrid_field_type::val_int())[0x556ab1ca2433] sql/item.h:1629(Item::update_null_value())[0x556ab1a48bdc] sql/item_func.h:185(Item_func::is_null())[0x556ab1bf8935] sql/sql_select.cc:11811(end_sj_materialize(JOIN*, st_join_table*, bool))[0x556ab1ddb06f] sql/sql_select.cc:20112(evaluate_join_record(JOIN*, st_join_table*, int))[0x556ab1e145b1] sql/sql_select.cc:19885(sub_select(JOIN*, st_join_table*, bool))[0x556ab1e12ec9] sql/opt_subselect.cc:5445(join_tab_execution_startup(st_join_table*))[0x556ab2189e25] sql/sql_select.cc:19874(sub_select(JOIN*, st_join_table*, bool))[0x556ab1e12bc9] sql/sql_select.cc:20112(evaluate_join_record(JOIN*, st_join_table*, int))[0x556ab1e145b1] sql/sql_select.cc:19885(sub_select(JOIN*, st_join_table*, bool))[0x556ab1e12ec9] sql/sql_select.cc:19423(do_select(JOIN*, Procedure*))[0x556ab1e10e88] sql/sql_select.cc:4151(JOIN::exec_inner())[0x556ab1da3a6d] sql/sql_select.cc:3946(JOIN::exec())[0x556ab1da13ea] sql/sql_select.cc:4356(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*))[0x556ab1da4ee8] sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x556ab1d7b449] sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x556ab1cec191] sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x556ab1cda1cc] sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x556ab1cf5eee] sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x556ab1cccdcb] sql/sql_parse.cc:1398(do_command(THD*))[0x556ab1cc990e] sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x556ab209cef1] sql/sql_connect.cc:1309(handle_one_connection)[0x556ab209c7ab] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x556ab36cc209] nptl/pthread_create.c:478(start_thread)[0x7f794b8a2609] 220525 8:33:56 [ERROR] mysqld got signal 11 ;   Server version: 10.5.16-MariaDB   sql/signal_handler.cc:225(handle_fatal_signal)[0x55b05562d287] sigaction.c:0(__restore_rt)[0x7f3cf0d17630] sql/sql_lex.cc:3409(st_select_lex::get_offset())[0x55b0553f3f10] sql/sql_lex.cc:4187(st_select_lex_unit::set_limit(st_select_lex*))[0x55b0553f5f6b] sql/item_subselect.cc:4013(subselect_single_select_engine::exec())[0x55b0556e7c17] sql/item_subselect.cc:842(Item_subselect::exec())[0x55b0556e640a] sql/item_subselect.cc:1461(Item_singlerow_subselect::val_int())[0x55b0556e6c6e] sql/item.cc:8571(Item_direct_ref::val_int())[0x55b05564225f] sql/item_func.cc:1114(Item_func_plus::int_op())[0x55b05569f77d] sql/item_func.h:185(Item_func::is_null())[0x55b0553d4075] sql/sql_select.cc:12638(end_sj_materialize(JOIN*, st_join_table*, bool))[0x55b05545a449] sql/sql_class.h:4115(THD::get_stmt_da())[0x55b05544badb] sql/sql_select.cc:20929(sub_select(JOIN*, st_join_table*, bool))[0x55b05545121f] sql/opt_subselect.cc:5677(join_tab_execution_startup(st_join_table*))[0x55b0555559a1] sql/sql_select.cc:20905(sub_select(JOIN*, st_join_table*, bool))[0x55b0554511ad] sql/sql_class.h:4115(THD::get_stmt_da())[0x55b05544badb] sql/sql_select.cc:20929(sub_select(JOIN*, st_join_table*, bool))[0x55b05545121f] sql/sql_select.cc:20454(do_select)[0x55b0554806d7] sql/sql_select.cc:4321(JOIN::exec())[0x55b055480a83] sql/sql_select.cc:4799(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*))[0x55b05547e9ee] sql/sql_select.cc:456(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55b05547f5aa] sql/sql_parse.cc:6315(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55b0552decfd] sql/sql_parse.cc:4005(mysql_execute_command(THD*))[0x55b055422c9e] sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55b05542540c] sql/sql_audit.h:169(mysql_audit_general)[0x55b0554283a4] sql/sql_parse.cc:1376(do_command(THD*))[0x55b055429c97] sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55b055517082] sql/sql_connect.cc:1318(handle_one_connection)[0x55b055517354] perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55b0558ae137] pthread_create.c:0(start_thread)[0x7f3cf0d0fea5] ??:0(__clone)[0x7f3cf022ab0d]   Query (0x7f3cb8010ae0): SELECT 1 FROM t1 WHERE i in (SELECT a+1 FROM (SELECT (SELECT i FROM (SELECT 1 FROM t1) dt) AS a FROM t1) dt2 GROUP BY a )
            alice Alice Sherepa added a comment - - edited

            ~ MDEV-24898

            CREATE TABLE t1 (i int) ;
            INSERT INTO t1 VALUES (1),(2),(3);
             
            SELECT 1 FROM t1 
            WHERE i in 
            (	SELECT  a+1 
            	FROM 
             	(SELECT (SELECT 1 FROM t1) AS a FROM t1) dt2 
             	GROUP BY a
            );
            

            10.3 7d3d3838c1b8af98a9704

            220525 10:37:18 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.3.36-MariaDB-debug-log
             
            sql/signal_handler.cc:221(handle_fatal_signal)[0x564d33492742]
            sql/sql_lex.h:1201(st_select_lex::next_select())[0x564d32c5c3e3]
            sql/item_subselect.cc:610(Item_subselect::is_expensive())[0x564d336a90ed]
            sql/item_subselect.h:256(Item_subselect::is_expensive_processor(void*))[0x564d336e3ab2]
            sql/item_subselect.cc:756(Item_subselect::walk(bool (Item::*)(void*), bool, void*))[0x564d336aa394]
            sql/item.h:5398(Item_direct_view_ref::walk(bool (Item::*)(void*), bool, void*))[0x564d335412cb]
            sql/item.h:2278(Item_args::walk_args(bool (Item::*)(void*), bool, void*))[0x564d32bca77d]
            sql/item.h:4825(Item_func_or_sum::walk(bool (Item::*)(void*), bool, void*))[0x564d32bcb67a]
            sql/item.h:2080(Item::is_expensive())[0x564d32a88e46]
            sql/sql_select.cc:14085(check_simple_equality(THD*, Value_source::Context const&, Item*, Item*, COND_EQUAL*))[0x564d32e2960a]
            sql/sql_select.cc:14309(Item_func_eq::check_equality(THD*, COND_EQUAL*, List<Item>*))[0x564d32e2ac2a]
            sql/sql_select.cc:14413(Item_cond_and::build_equal_items(THD*, COND_EQUAL*, bool, COND_EQUAL**))[0x564d32e2afdf]
            sql/sql_select.cc:14695(build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool))[0x564d32e2d0d4]
            sql/sql_select.cc:16341(optimize_cond(JOIN*, Item*, List<TABLE_LIST>*, bool, Item::cond_result*, COND_EQUAL**, int))[0x564d32e3684e]
            sql/sql_select.cc:1764(JOIN::optimize_inner())[0x564d32dc9395]
            sql/sql_select.cc:1519(JOIN::optimize())[0x564d32dc68b9]
            sql/sql_select.cc:4340(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*))[0x564d32de3cf7]
            sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564d32dba449]
            sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564d32d2b191]
            sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x564d32d191cc]
            sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564d32d34eee]
            sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564d32d0bdcb]
            sql/sql_parse.cc:1398(do_command(THD*))[0x564d32d0890e]
            sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x564d330dbef1]
            sql/sql_connect.cc:1309(handle_one_connection)[0x564d330db7ab]
            perfschema/pfs.cc:1871(pfs_spawn_thread)[0x564d3470b209]
            nptl/pthread_create.c:478(start_thread)[0x7f0ff2910609]
            addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530ea0)
            /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f0ff2833133]
             
            Query (0x62b000000290): SELECT 1 FROM t1 
            WHERE i in 
            (	SELECT  a+1 
            FROM 
            (SELECT (SELECT 1 FROM t1) AS a FROM t1) dt2 
            GROUP BY a
            )
            
            

            alice Alice Sherepa added a comment - - edited ~ MDEV-24898 CREATE TABLE t1 (i int ) ; INSERT INTO t1 VALUES (1),(2),(3);   SELECT 1 FROM t1 WHERE i in ( SELECT a+1 FROM ( SELECT ( SELECT 1 FROM t1) AS a FROM t1) dt2 GROUP BY a ); 10.3 7d3d3838c1b8af98a9704 220525 10:37:18 [ERROR] mysqld got signal 11 ;   Server version: 10.3.36-MariaDB-debug-log   sql/signal_handler.cc:221(handle_fatal_signal)[0x564d33492742] sql/sql_lex.h:1201(st_select_lex::next_select())[0x564d32c5c3e3] sql/item_subselect.cc:610(Item_subselect::is_expensive())[0x564d336a90ed] sql/item_subselect.h:256(Item_subselect::is_expensive_processor(void*))[0x564d336e3ab2] sql/item_subselect.cc:756(Item_subselect::walk(bool (Item::*)(void*), bool, void*))[0x564d336aa394] sql/item.h:5398(Item_direct_view_ref::walk(bool (Item::*)(void*), bool, void*))[0x564d335412cb] sql/item.h:2278(Item_args::walk_args(bool (Item::*)(void*), bool, void*))[0x564d32bca77d] sql/item.h:4825(Item_func_or_sum::walk(bool (Item::*)(void*), bool, void*))[0x564d32bcb67a] sql/item.h:2080(Item::is_expensive())[0x564d32a88e46] sql/sql_select.cc:14085(check_simple_equality(THD*, Value_source::Context const&, Item*, Item*, COND_EQUAL*))[0x564d32e2960a] sql/sql_select.cc:14309(Item_func_eq::check_equality(THD*, COND_EQUAL*, List<Item>*))[0x564d32e2ac2a] sql/sql_select.cc:14413(Item_cond_and::build_equal_items(THD*, COND_EQUAL*, bool, COND_EQUAL**))[0x564d32e2afdf] sql/sql_select.cc:14695(build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool))[0x564d32e2d0d4] sql/sql_select.cc:16341(optimize_cond(JOIN*, Item*, List<TABLE_LIST>*, bool, Item::cond_result*, COND_EQUAL**, int))[0x564d32e3684e] sql/sql_select.cc:1764(JOIN::optimize_inner())[0x564d32dc9395] sql/sql_select.cc:1519(JOIN::optimize())[0x564d32dc68b9] sql/sql_select.cc:4340(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*))[0x564d32de3cf7] sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564d32dba449] sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564d32d2b191] sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x564d32d191cc] sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564d32d34eee] sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564d32d0bdcb] sql/sql_parse.cc:1398(do_command(THD*))[0x564d32d0890e] sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x564d330dbef1] sql/sql_connect.cc:1309(handle_one_connection)[0x564d330db7ab] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x564d3470b209] nptl/pthread_create.c:478(start_thread)[0x7f0ff2910609] addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530ea0) /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f0ff2833133]   Query (0x62b000000290): SELECT 1 FROM t1 WHERE i in ( SELECT a+1 FROM (SELECT (SELECT 1 FROM t1) AS a FROM t1) dt2 GROUP BY a )
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Assignee Sergei Petrunia [ psergey ]
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.7 [ 24805 ]
            Affects Version/s 10.8 [ 26121 ]
            Affects Version/s 10.9 [ 26905 ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Labels fuzzer
            Johnston Rex Johnston made changes -
            Assignee Sergei Petrunia [ psergey ] Rex Johnston [ JIRAUSER52533 ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.10 [ 27530 ]
            Affects Version/s 10.11 [ 27614 ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8 [ 26121 ]
            Johnston Rex Johnston made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa added a comment - - edited

            Test case from MDEV-32311

            SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) ) ) ) ;
            

            Version: '10.4.32-MariaDB-debug-log'  
            mysqld: /10.4/src/sql/item_subselect.cc:733: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
            231003 15:33:13 [ERROR] mysqld got signal 6 ;
             
            Server version: 10.4.32-MariaDB-debug-log source revision: 50a2e8b1892b6b8a276d4bd75a1a02148f9e6ff2
             
            /lib/x86_64-linux-gnu/libc.so.6(+0x22729)[0x7fba7d088729]
            /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7fba7d099fd6]
            sql/item_subselect.cc:735(Item_subselect::exec())[0x5622c94590f0]
            sql/item_subselect.cc:1404(Item_singlerow_subselect::val_str(String*))[0x5622c945f991]
            sql/item.h:1558(Item::str_result(String*))[0x5622c87a6135]
            sql/item.cc:9381(Item_direct_view_ref::str_result(String*))[0x5622c92c60e2]
            sql/item.cc:10367(Item_cache_str::cache_value())[0x5622c92cf0ee]
            sql/item_cmpfunc.cc:1371(Item_in_optimizer::fix_left(THD*))[0x5622c92fb02e]
            sql/item_subselect.cc:3340(Item_in_subselect::select_in_like_transformer(JOIN*))[0x5622c9477bff]
            sql/item_subselect.cc:2649(Item_in_subselect::select_transformer(JOIN*))[0x5622c947057f]
            sql/opt_subselect.cc:742(check_and_do_in_subquery_rewrites(JOIN*))[0x5622c8f410e4]
            sql/sql_select.cc:1434(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b1947d]
            sql/item_subselect.cc:3804(subselect_single_select_engine::prepare(THD*))[0x5622c947c280]
            sql/item_subselect.cc:289(Item_subselect::fix_fields(THD*, Item**))[0x5622c9455cd3]
            sql/item_subselect.cc:3466(Item_in_subselect::fix_fields(THD*, Item**))[0x5622c9479047]
            sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5622c87c45cd]
            sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5622c87c4607]
            sql/item.h:975(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5622c88fca05]
            sql/sql_base.cc:8545(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x5622c88f3e94]
            sql/sql_select.cc:744(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*))[0x5622c8b10955]
            sql/sql_select.cc:1335(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b180b9]
            sql/item_subselect.cc:3804(subselect_single_select_engine::prepare(THD*))[0x5622c947c280]
            sql/item_subselect.cc:289(Item_subselect::fix_fields(THD*, Item**))[0x5622c9455cd3]
            sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5622c87c45cd]
            sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5622c87c4607]
            sql/sql_base.cc:7744(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, List<Item>*, bool))[0x5622c88ede03]
            sql/sql_select.cc:1330(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b17d8d]
            sql/sql_select.cc:4789(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*))[0x5622c8b3d98d]
            sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5622c8b0e922]
            sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5622c8a7a72c]
            sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x5622c8a67ea3]
            sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5622c8a83c07]
            sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5622c8a5a02d]
            sql/sql_parse.cc:1378(do_command(THD*))[0x5622c8a56b58]
            sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5622c8e647fd]
            sql/sql_connect.cc:1325(handle_one_connection)[0x5622c8e640a1]
            perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5622c9b0e99a]
            nptl/pthread_create.c:478(start_thread)[0x7fba7d5b4609]
             
            Query (0x62b0000a1420): SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) ) ) )
            

            test from MDEV-32390:

            CREATE TABLE t0 ( c43 DECIMAL ( 31 ) DEFAULT ( 45 ) ) ;
            INSERT INTO t0 VALUES ( 13 ) , ( 29 ) ;
            ALTER TABLE t0 ADD COLUMN c24 INT AFTER c43 ;
            INSERT INTO t0 VALUES ( DEFAULT , DEFAULT ) , ( DEFAULT , DEFAULT ) ;
            SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) << LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND -108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 ) ) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22 , ':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m"
            P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN -103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) = CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22 WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) ) ;
            

            Version: '10.4.32-MariaDB-debug-log'
            mysqld: /10.4/src/sql/item_subselect.cc:733: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
            231010 16:22:18 [ERROR] mysqld got signal 6 ;
             
            Server version: 10.4.32-MariaDB-debug-log source revision: 0c7af6a2a19343cb9d4fedbd7165b8f73bc4cf96
             
            /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f5e60504fd6]
            sql/item_subselect.cc:735(Item_subselect::exec())[0x56093e7de89e]
            sql/item_subselect.cc:1382(Item_singlerow_subselect::val_int())[0x56093e7e4b9f]
            sql/item.h:1557(Item::val_int_result())[0x56093db2b0be]
            sql/item.cc:9373(Item_direct_view_ref::val_int_result())[0x56093e64b6a3]
            sql/item.cc:8380(Item_ref::val_int())[0x56093e640050]
            sql/item_cmpfunc.cc:969(Arg_comparator::compare_int_unsigned())[0x56093e67bc65]
            sql/item_cmpfunc.h:104(Arg_comparator::compare())[0x56093e6bd64e]
            sql/item_cmpfunc.cc:1821(Item_func_ge::val_int())[0x56093e685867]
            sql/item_func.cc:763(Item_int_func::val_str(String*))[0x56093e70a2c6]
            sql/item_strfunc.cc:2096(Item_func_trim::val_str(String*))[0x56093e7a6539]
            sql/item_strfunc.cc:159(Item_str_func::val_int())[0x56093e79383c]
            sql/sql_select.cc:22086(end_send(JOIN*, st_join_table*, bool))[0x56093df3f25e]
            sql/sql_select.cc:21129(evaluate_join_record(JOIN*, st_join_table*, int))[0x56093df378b9]
            sql/sql_select.cc:20902(sub_select(JOIN*, st_join_table*, bool))[0x56093df361ee]
            sql/sql_select.cc:20423(do_select(JOIN*, Procedure*))[0x56093df33f94]
            sql/sql_select.cc:4605(JOIN::exec_inner())[0x56093dec1adc]
            sql/sql_select.cc:4388(JOIN::exec())[0x56093debf10c]
            sql/sql_select.cc:4828(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*))[0x56093dec32e8]
            sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56093de93de0]
            sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x56093ddffbe4]
            sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x56093dded35b]
            sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56093de090bf]
            sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56093dddf4e5]
            sql/sql_parse.cc:1378(do_command(THD*))[0x56093dddc010]
            sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x56093e1e9deb]
            sql/sql_connect.cc:1325(handle_one_connection)[0x56093e1e968f]
            perfschema/pfs.cc:1871(pfs_spawn_thread)[0x56093ee94274]
            nptl/pthread_create.c:478(start_thread)[0x7f5e60a1f609]
             
            Query (0x62b0000a1290): SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) << LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND -108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 ) ) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22 , ':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m"
            P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN -103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) = CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22 WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) )
             
            
            

            MDEV-32309

            SELECT ( WITH x ( x ) AS ( WITH x ( x ) AS ( SELECT 1 ) SELECT ( SELECT x ) FROM x ) SELECT x FROM x WHERE x IN ( SELECT NULL GROUP BY x ) ) ;
            

            MDEV-32391

            CREATE TABLE t0 ( c15 INT , c33 INT ) engine=innodb;
            INSERT INTO t0 ( c15 ) WITH t1 AS ( SELECT SQRT ( 123 ) NOT REGEXP MOD ( 91 , -121 ) = ALL ( SELECT c15 AS c33 FROM t0 ) AS c49 FROM t0 ) SELECT t1 . c49 IS UNKNOWN AS c59 FROM t1 CROSS JOIN t0 AS t2 WHERE t1 . c49 = + EXISTS ( SELECT -5839312620871436105 AS c17 GROUP BY c49 ) BETWEEN -109 AND CHAR_LENGTH ( 2694839150676403988 ) - - LOWER ( -13 ) ;
            

            alice Alice Sherepa added a comment - - edited Test case from MDEV-32311 SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) ) ) ) ; Version: '10.4.32-MariaDB-debug-log' mysqld: /10.4/src/sql/item_subselect.cc:733: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed. 231003 15:33:13 [ERROR] mysqld got signal 6 ;   Server version: 10.4.32-MariaDB-debug-log source revision: 50a2e8b1892b6b8a276d4bd75a1a02148f9e6ff2   /lib/x86_64-linux-gnu/libc.so.6(+0x22729)[0x7fba7d088729] /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7fba7d099fd6] sql/item_subselect.cc:735(Item_subselect::exec())[0x5622c94590f0] sql/item_subselect.cc:1404(Item_singlerow_subselect::val_str(String*))[0x5622c945f991] sql/item.h:1558(Item::str_result(String*))[0x5622c87a6135] sql/item.cc:9381(Item_direct_view_ref::str_result(String*))[0x5622c92c60e2] sql/item.cc:10367(Item_cache_str::cache_value())[0x5622c92cf0ee] sql/item_cmpfunc.cc:1371(Item_in_optimizer::fix_left(THD*))[0x5622c92fb02e] sql/item_subselect.cc:3340(Item_in_subselect::select_in_like_transformer(JOIN*))[0x5622c9477bff] sql/item_subselect.cc:2649(Item_in_subselect::select_transformer(JOIN*))[0x5622c947057f] sql/opt_subselect.cc:742(check_and_do_in_subquery_rewrites(JOIN*))[0x5622c8f410e4] sql/sql_select.cc:1434(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b1947d] sql/item_subselect.cc:3804(subselect_single_select_engine::prepare(THD*))[0x5622c947c280] sql/item_subselect.cc:289(Item_subselect::fix_fields(THD*, Item**))[0x5622c9455cd3] sql/item_subselect.cc:3466(Item_in_subselect::fix_fields(THD*, Item**))[0x5622c9479047] sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5622c87c45cd] sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5622c87c4607] sql/item.h:975(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5622c88fca05] sql/sql_base.cc:8545(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x5622c88f3e94] sql/sql_select.cc:744(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*))[0x5622c8b10955] sql/sql_select.cc:1335(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b180b9] sql/item_subselect.cc:3804(subselect_single_select_engine::prepare(THD*))[0x5622c947c280] sql/item_subselect.cc:289(Item_subselect::fix_fields(THD*, Item**))[0x5622c9455cd3] sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5622c87c45cd] sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5622c87c4607] sql/sql_base.cc:7744(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, List<Item>*, bool))[0x5622c88ede03] sql/sql_select.cc:1330(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b17d8d] sql/sql_select.cc:4789(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*))[0x5622c8b3d98d] sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5622c8b0e922] sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5622c8a7a72c] sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x5622c8a67ea3] sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5622c8a83c07] sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5622c8a5a02d] sql/sql_parse.cc:1378(do_command(THD*))[0x5622c8a56b58] sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5622c8e647fd] sql/sql_connect.cc:1325(handle_one_connection)[0x5622c8e640a1] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5622c9b0e99a] nptl/pthread_create.c:478(start_thread)[0x7fba7d5b4609]   Query (0x62b0000a1420): SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) ) ) ) test from MDEV-32390 : CREATE TABLE t0 ( c43 DECIMAL ( 31 ) DEFAULT ( 45 ) ) ; INSERT INTO t0 VALUES ( 13 ) , ( 29 ) ; ALTER TABLE t0 ADD COLUMN c24 INT AFTER c43 ; INSERT INTO t0 VALUES ( DEFAULT , DEFAULT ) , ( DEFAULT , DEFAULT ) ; SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) << LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND -108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 ) ) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22 , ':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m" P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN -103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) = CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22 WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) ) ; Version: '10.4.32-MariaDB-debug-log' mysqld: /10.4/src/sql/item_subselect.cc:733: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed. 231010 16:22:18 [ERROR] mysqld got signal 6 ;   Server version: 10.4.32-MariaDB-debug-log source revision: 0c7af6a2a19343cb9d4fedbd7165b8f73bc4cf96   /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f5e60504fd6] sql/item_subselect.cc:735(Item_subselect::exec())[0x56093e7de89e] sql/item_subselect.cc:1382(Item_singlerow_subselect::val_int())[0x56093e7e4b9f] sql/item.h:1557(Item::val_int_result())[0x56093db2b0be] sql/item.cc:9373(Item_direct_view_ref::val_int_result())[0x56093e64b6a3] sql/item.cc:8380(Item_ref::val_int())[0x56093e640050] sql/item_cmpfunc.cc:969(Arg_comparator::compare_int_unsigned())[0x56093e67bc65] sql/item_cmpfunc.h:104(Arg_comparator::compare())[0x56093e6bd64e] sql/item_cmpfunc.cc:1821(Item_func_ge::val_int())[0x56093e685867] sql/item_func.cc:763(Item_int_func::val_str(String*))[0x56093e70a2c6] sql/item_strfunc.cc:2096(Item_func_trim::val_str(String*))[0x56093e7a6539] sql/item_strfunc.cc:159(Item_str_func::val_int())[0x56093e79383c] sql/sql_select.cc:22086(end_send(JOIN*, st_join_table*, bool))[0x56093df3f25e] sql/sql_select.cc:21129(evaluate_join_record(JOIN*, st_join_table*, int))[0x56093df378b9] sql/sql_select.cc:20902(sub_select(JOIN*, st_join_table*, bool))[0x56093df361ee] sql/sql_select.cc:20423(do_select(JOIN*, Procedure*))[0x56093df33f94] sql/sql_select.cc:4605(JOIN::exec_inner())[0x56093dec1adc] sql/sql_select.cc:4388(JOIN::exec())[0x56093debf10c] sql/sql_select.cc:4828(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*))[0x56093dec32e8] sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56093de93de0] sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x56093ddffbe4] sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x56093dded35b] sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56093de090bf] sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56093dddf4e5] sql/sql_parse.cc:1378(do_command(THD*))[0x56093dddc010] sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x56093e1e9deb] sql/sql_connect.cc:1325(handle_one_connection)[0x56093e1e968f] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x56093ee94274] nptl/pthread_create.c:478(start_thread)[0x7f5e60a1f609]   Query (0x62b0000a1290): SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) << LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND -108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 ) ) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22 , ':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m" P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN -103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) = CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22 WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) )   MDEV-32309 SELECT ( WITH x ( x ) AS ( WITH x ( x ) AS ( SELECT 1 ) SELECT ( SELECT x ) FROM x ) SELECT x FROM x WHERE x IN ( SELECT NULL GROUP BY x ) ) ; MDEV-32391 CREATE TABLE t0 ( c15 INT , c33 INT ) engine=innodb; INSERT INTO t0 ( c15 ) WITH t1 AS ( SELECT SQRT ( 123 ) NOT REGEXP MOD ( 91 , -121 ) = ALL ( SELECT c15 AS c33 FROM t0 ) AS c49 FROM t0 ) SELECT t1 . c49 IS UNKNOWN AS c59 FROM t1 CROSS JOIN t0 AS t2 WHERE t1 . c49 = + EXISTS ( SELECT -5839312620871436105 AS c17 GROUP BY c49 ) BETWEEN -109 AND CHAR_LENGTH ( 2694839150676403988 ) - - LOWER ( -13 ) ;
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.2 [ 28603 ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            psergei Sergei Petrunia made changes -
            Summary Server crash in /sql/item_subselect.cc:766: virtual bool Item_subselect::exec() eliminated subquery: Server crash in /sql/item_subselect.cc:766: virtual bool Item_subselect::exec()
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.9 [ 26905 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.10 [ 27530 ]
            Johnston Rex Johnston made changes -
            Affects Version/s 11.4 [ 29301 ]
            Johnston Rex Johnston made changes -
            Fix Version/s 11.4 [ 29301 ]
            Johnston Rex Johnston made changes -
            Fix Version/s 11.3 [ 28565 ]
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            Security Developers [ 10400 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Blocker [ 1 ]
            Johnston Rex Johnston made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            Johnston Rex Johnston made changes -
            Summary eliminated subquery: Server crash in /sql/item_subselect.cc:766: virtual bool Item_subselect::exec() group by optimization incorrectly removing subquery where subject buried in a function
            Johnston Rex Johnston made changes -
            Comment [ This/these queries undergo a series of optimizations resulting in the eliminated flag being set in subqueries.

            Will be fixed in MDEV-28622. ]
            Johnston Rex Johnston added a comment - - edited

            One of the query optimization routines, remove_redundant_subquery_clauses() evaluates whether to remove the group by clause by searching the argument list of the relevant select_lex for references within the group by expression.

            A note in the code details when this might occur.

                Permanently remove redundant parts from the query if
                  1) This is a subquery
                  2) This is the first time this query is optimized (since the
                     transformation is permanent
                  3) Not normalizing a view. Removal should take place when a
                     query involving a view is optimized, not when the view
                     is created
            

            1) is actually more restrictive than 'This is a subquery'. The actual test is if the unit containing this select_lex has an attached Item.
            This only occurs under limited circumstances.

            In one problem query

            SELECT 1 FROM t1
            WHERE a in
            (
              SELECT  i+1
              FROM (SELECT (SELECT 1 FROM t1) AS i FROM t1) dt2
              GROUP BY i
            );
            

            WHERE a in (expression) causes an Item to be generated for (expression). This is the type of subquery being optimized.

            What is happening here is that, because 'i' (which is itself another subquery), doesn't directly appear in the select list (it is under a function +(i,1)) this optimization considers removing the group by expression. As part of this cleanup, because 'i' is a subquery itself, it can be removed.

            This causes a problem later on when the "SELECT i+1" part is to be evaluated and this subquery has now been removed.

            Another note inside remove_redundant_subquery_clauses() states

                    Do not remove the item if it is used in select list and then referred
                    from GROUP BY clause by its name or number. Example:
             
                      select (select ... ) as SUBQ ...  group by SUBQ
             
                    Here SUBQ cannot be removed.
            

            The flag as to whether any order item is in the select list is 'in_field_list', and is populated by find_item_in_list(). Here, a simple top level scan is performed.

            The logic is fine, but the search needed extending to find any occurrences of 'i' anywhere in the select list.

            Johnston Rex Johnston added a comment - - edited One of the query optimization routines, remove_redundant_subquery_clauses() evaluates whether to remove the group by clause by searching the argument list of the relevant select_lex for references within the group by expression. A note in the code details when this might occur. Permanently remove redundant parts from the query if 1) This is a subquery 2) This is the first time this query is optimized (since the transformation is permanent 3) Not normalizing a view. Removal should take place when a query involving a view is optimized, not when the view is created 1) is actually more restrictive than 'This is a subquery'. The actual test is if the unit containing this select_lex has an attached Item. This only occurs under limited circumstances. In one problem query SELECT 1 FROM t1 WHERE a in ( SELECT i+1 FROM ( SELECT ( SELECT 1 FROM t1) AS i FROM t1) dt2 GROUP BY i ); WHERE a in (expression) causes an Item to be generated for (expression). This is the type of subquery being optimized. What is happening here is that, because 'i' (which is itself another subquery), doesn't directly appear in the select list (it is under a function +(i,1)) this optimization considers removing the group by expression. As part of this cleanup, because 'i' is a subquery itself, it can be removed. This causes a problem later on when the "SELECT i+1" part is to be evaluated and this subquery has now been removed. Another note inside remove_redundant_subquery_clauses() states Do not remove the item if it is used in select list and then referred from GROUP BY clause by its name or number. Example:   select (select ... ) as SUBQ ... group by SUBQ   Here SUBQ cannot be removed. The flag as to whether any order item is in the select list is 'in_field_list', and is populated by find_item_in_list(). Here, a simple top level scan is performed. The logic is fine, but the search needed extending to find any occurrences of 'i' anywhere in the select list.
            psergei Sergei Petrunia added a comment - - edited

            I see one more objectionable thing in this example. The call

            item->walk(Item::eliminate_subselect_processor);
            

            walks into Item_direct_view_ref objects.

            A background on what Item_direct_view_ref is: If you have a view column

            create view v1 as select  (select ....) as SUBQ from ...
            

            and then use it multiple times:

            select  v1.SUBQ ... from v1 WHERE v1.SUBQ = ... 
            

            then each use of "v1.SUBQ" is a separate Item_direct_view_ref object. All of these objects point to the same Item_subselect() created based on the view definition.

            Now, we see that walk(eliminate_subselect_processor) walks into Item_direct_view_ref. This doesn't look like a good idea - we know that one "v1.SUBQ" was eliminated, but what if the others were not?

            psergei Sergei Petrunia added a comment - - edited I see one more objectionable thing in this example. The call item->walk(Item::eliminate_subselect_processor); walks into Item_direct_view_ref objects. A background on what Item_direct_view_ref is: If you have a view column create view v1 as select ( select ....) as SUBQ from ... and then use it multiple times: select v1.SUBQ ... from v1 WHERE v1.SUBQ = ... then each use of "v1.SUBQ" is a separate Item_direct_view_ref object. All of these objects point to the same Item_subselect() created based on the view definition. Now, we see that walk(eliminate_subselect_processor) walks into Item_direct_view_ref. This doesn't look like a good idea - we know that one "v1.SUBQ" was eliminated, but what if the others were not?
            psergei Sergei Petrunia added a comment - - edited

            Generally, it's fairly easy to construct queries where all use of subquery is removed but the subquery is still in the query plan:

            create table t10 (a int , b int);
            insert into t10 values (1,1),(2,2);
             
            create table t11 like t10;
            insert into t11 select seq, seq from seq_1_to_1000;
            

            explain format=json 
            select * from t10 
            where 
              t10.a=1 or (1>2 and  3> (select max(a) from  t11));
            

            explain format=json 
            select * from t10 
            where 
              t10.a=1 or (1>2 and  3> (select max(a) from  t11 where t11.b<t10.b));
            

            Both will show the subquery as part of query plan. Both will show no references to it.

            psergei Sergei Petrunia added a comment - - edited Generally, it's fairly easy to construct queries where all use of subquery is removed but the subquery is still in the query plan: create table t10 (a int , b int ); insert into t10 values (1,1),(2,2);   create table t11 like t10; insert into t11 select seq, seq from seq_1_to_1000; explain format=json select * from t10 where t10.a=1 or (1>2 and 3> ( select max (a) from t11)); explain format=json select * from t10 where t10.a=1 or (1>2 and 3> ( select max (a) from t11 where t11.b<t10.b)); Both will show the subquery as part of query plan. Both will show no references to it.
            psergei Sergei Petrunia added a comment - - edited

            ... continuing the question of subquery elimination walking into Item_direct_view_ref objects: Here is an example with a different kind of elimination:

            create table t20 (a int, b int);
            insert into t20 select seq, seq from seq_1_to_10;
            create table t21 as select * from t20;
             
            create view v21 as 
            select 
              a, (select max(t20.b) from t20 where t20.a=t21.a) as SUBQ
            from t21;
             
            create table t22 (
              pk int primary key,
              a int
            );
            insert into t22 select * from t20;
            

            explain 
            select v21.a, v21.SUBQ from v21 left join t22 on t22.pk=v21.a and v21.SUBQ=t22.a;
            

            EXPLAIN shows the t22 is eliminated with the ON expression:

            +------+-------------+-------+------+---------------+------+---------+------+------+-------+
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
            +------+-------------+-------+------+---------------+------+---------+------+------+-------+
            |    1 | PRIMARY     | t21   | ALL  | NULL          | NULL | NULL    | NULL | 10   |       |
            +------+-------------+-------+------+---------------+------+---------+------+------+-------+
            

            running the query will fail an assertion here:

              mysqld: /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:786: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
             
              Thread 14 "mysqld" received signal SIGABRT, Aborted.
              __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
              51    ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
            (gdb) wher
              #0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
              #1  0x00007ffff54397f1 in __GI_abort () at abort.c:79
              #2  0x00007ffff54293fa in __assert_fail_base (fmt=0x7ffff55b06c0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x555556f11c29 "!        eliminated", file=file@entry=0x555556f11a48 "/home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc", line=line@entry=786, function=function@entry=0x555556f13980   <Item_subselect::exec()::__PRETTY_FUNCTION__> "virtual bool Item_subselect::exec()") at assert.c:92
              #3  0x00007ffff5429472 in __GI___assert_fail (assertion=0x555556f11c29 "!eliminated", file=0x555556f11a48 "/home/psergey/dev-git2/10.6-dbg/sql/item_subselect.   cc", line=786, function=0x555556f13980 <Item_subselect::exec()::__PRETTY_FUNCTION__> "virtual bool Item_subselect::exec()") at assert.c:101
              #4  0x000055555637d8d9 in Item_subselect::exec (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:786
              #5  0x000055555637fbbd in Item_singlerow_subselect::val_int (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:1473
              #6  0x000055555616c5a3 in Type_handler::Item_send_long (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320,                  buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.cc:7604
              #7  0x0000555556179a0a in Type_handler_long::Item_send (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320,                  buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.h:5768
              #8  0x0000555555d99722 in Item::send (this=0x7fff2001fc20, protocol=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.h:1254
              #9  0x00005555562c3ece in Item_ref::send (this=0x7fff20083d60, prot=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.cc:8500
            
            

            psergei Sergei Petrunia added a comment - - edited ... continuing the question of subquery elimination walking into Item_direct_view_ref objects: Here is an example with a different kind of elimination: create table t20 (a int , b int ); insert into t20 select seq, seq from seq_1_to_10; create table t21 as select * from t20;   create view v21 as select a, ( select max (t20.b) from t20 where t20.a=t21.a) as SUBQ from t21;   create table t22 ( pk int primary key , a int ); insert into t22 select * from t20; explain select v21.a, v21.SUBQ from v21 left join t22 on t22.pk=v21.a and v21.SUBQ=t22.a; EXPLAIN shows the t22 is eliminated with the ON expression: +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | t21 | ALL | NULL | NULL | NULL | NULL | 10 | | +------+-------------+-------+------+---------------+------+---------+------+------+-------+ running the query will fail an assertion here: mysqld: /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:786: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.   Thread 14 "mysqld" received signal SIGABRT, Aborted. __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51 51 ../sysdeps/unix/sysv/linux/raise.c: No such file or directory. (gdb) wher #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51 #1 0x00007ffff54397f1 in __GI_abort () at abort.c:79 #2 0x00007ffff54293fa in __assert_fail_base (fmt=0x7ffff55b06c0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x555556f11c29 "! eliminated", file=file@entry=0x555556f11a48 "/home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc", line=line@entry=786, function=function@entry=0x555556f13980 <Item_subselect::exec()::__PRETTY_FUNCTION__> "virtual bool Item_subselect::exec()") at assert.c:92 #3 0x00007ffff5429472 in __GI___assert_fail (assertion=0x555556f11c29 "!eliminated", file=0x555556f11a48 "/home/psergey/dev-git2/10.6-dbg/sql/item_subselect. cc", line=786, function=0x555556f13980 <Item_subselect::exec()::__PRETTY_FUNCTION__> "virtual bool Item_subselect::exec()") at assert.c:101 #4 0x000055555637d8d9 in Item_subselect::exec (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:786 #5 0x000055555637fbbd in Item_singlerow_subselect::val_int (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:1473 #6 0x000055555616c5a3 in Type_handler::Item_send_long (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320, buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.cc:7604 #7 0x0000555556179a0a in Type_handler_long::Item_send (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320, buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.h:5768 #8 0x0000555555d99722 in Item::send (this=0x7fff2001fc20, protocol=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.h:1254 #9 0x00005555562c3ece in Item_ref::send (this=0x7fff20083d60, prot=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.cc:8500

            Note that there are actually two kinds of subquery elimination:

            1. Item::eliminate_subselect_processor

            Introduced in 2010 by Timour, commit 18ad3bdc2fa
            This removes the subquery from the st_select_lex tree.
            Called by

            • st_select_lex_unit::prepare_join
            • remove_redundant_subquery_clauses

            2. Item::mark_as_eliminated_processor
            Introduced in 2009 by me, commit 9e65634b233
            This just sets Item_subselect::eliminated=true.
            Called by eliminate_tables().

            psergei Sergei Petrunia added a comment - Note that there are actually two kinds of subquery elimination: 1. Item::eliminate_subselect_processor Introduced in 2010 by Timour, commit 18ad3bdc2fa This removes the subquery from the st_select_lex tree. Called by st_select_lex_unit::prepare_join remove_redundant_subquery_clauses 2. Item::mark_as_eliminated_processor Introduced in 2009 by me, commit 9e65634b233 This just sets Item_subselect::eliminated=true. Called by eliminate_tables().
            psergei Sergei Petrunia added a comment - - edited

            Doing some tests: 10.6-MDEV-28621-no-elimination.

            Will also need to check what happens on attempt to move this to the optimization phase.

            psergei Sergei Petrunia added a comment - - edited Doing some tests: 10.6- MDEV-28621 -no-elimination. Will also need to check what happens on attempt to move this to the optimization phase.

            Ok the approach of

            "Let's mark everything that's still reachable at query end"

            turned out to result in a quite intrusive patch.

            • There are constant subqueries that are computed and then removed from
              any clause.
            • There is code that "moves" Item_subselect* pointers from one select to
              another without updating SELECT_LEX* structures
            • There is code in JOIN::optimize_constant_subqueries() to handle subqueries that are not reachable through Item trees are present in SELECT_LEX graph:

                    if (!subquery_predicate->fixed())
                    {
                      /*
                       This subquery was excluded as part of some expression so it is
                       invisible from all prepared expression.
                     */
                      next_unit= un->next_unit();
              

            • etc etc.
            psergei Sergei Petrunia added a comment - Ok the approach of "Let's mark everything that's still reachable at query end" turned out to result in a quite intrusive patch. There are constant subqueries that are computed and then removed from any clause. There is code that "moves" Item_subselect* pointers from one select to another without updating SELECT_LEX* structures There is code in JOIN::optimize_constant_subqueries() to handle subqueries that are not reachable through Item trees are present in SELECT_LEX graph: if (!subquery_predicate->fixed()) { /* This subquery was excluded as part of some expression so it is invisible from all prepared expression. */ next_unit= un->next_unit(); etc etc.

            Trying a different approach: this seems to be much less intrusive:

            commit b33f744ea58124c38aa97b597ddd48547777be9e (HEAD -> bb-10.6-MDEV-28621-v3-eliminate-carefully)
            Author: Sergei Petrunia <sergey@mariadb.com>
            Date:   Mon Mar 25 13:53:55 2024 +0300
             
                MDEV-28621: group by optimization incorrectly removing subquery
                
                Try a different approach at removal: remove only "local" subqueries
                for which we can't find references in other parts of the query.
            

            (Doesn't handle the Table Elimination case yet, but it's trivial to extend it to)

            So far it fixes testcases for MDEV-28621 MDEV-32311 MDEV-32390 MDEV-32309 MDEV-32391 MDEV-28620 MDEV-30842 MDEV-28622

            psergei Sergei Petrunia added a comment - Trying a different approach: this seems to be much less intrusive: commit b33f744ea58124c38aa97b597ddd48547777be9e (HEAD -> bb-10.6-MDEV-28621-v3-eliminate-carefully) Author: Sergei Petrunia <sergey@mariadb.com> Date: Mon Mar 25 13:53:55 2024 +0300   MDEV-28621: group by optimization incorrectly removing subquery Try a different approach at removal: remove only "local" subqueries for which we can't find references in other parts of the query. (Doesn't handle the Table Elimination case yet, but it's trivial to extend it to) So far it fixes testcases for MDEV-28621 MDEV-32311 MDEV-32390 MDEV-32309 MDEV-32391 MDEV-28620 MDEV-30842 MDEV-28622
            Johnston Rex Johnston added a comment -

            bb-10.6-MDEV-28621-delayed-elimination implements marking a unit to be eliminated but not actually eliminating it until the end of the top level optimize. If we mark something as to be eliminated then try and evaluate it elsewhere, it unmarks this unit for elimination. There are 2nd execution complications that show up as slightly ugly flag checks in item methods, but the idea can work.

            Johnston Rex Johnston added a comment - bb-10.6- MDEV-28621 -delayed-elimination implements marking a unit to be eliminated but not actually eliminating it until the end of the top level optimize. If we mark something as to be eliminated then try and evaluate it elsewhere, it unmarks this unit for elimination. There are 2nd execution complications that show up as slightly ugly flag checks in item methods, but the idea can work.
            psergei Sergei Petrunia added a comment - - edited

            arguments why the approach of bb-10.6-MDEV-28621-v3-eliminate-carefully is better than the approach of bb-10.6-MDEV-28621-delayed-elimination:

            It is much nicer when a rewrite is an atomic action. We have the GROUP BY clause and/or a subquery, then Baam and it is completely gone.

            The approach where there is a state where the subquery is still there but is being removed adds a lot of complexity.

            One may point out that ...-eliminate-carefully does Item tree walks which take CPU cycles. My reply is that the walking is done when there's a redundant ORDER BY clause so walking is not on the common code path.

            psergei Sergei Petrunia added a comment - - edited arguments why the approach of bb-10.6- MDEV-28621 -v3-eliminate-carefully is better than the approach of bb-10.6- MDEV-28621 -delayed-elimination : It is much nicer when a rewrite is an atomic action. We have the GROUP BY clause and/or a subquery, then Baam and it is completely gone. The approach where there is a state where the subquery is still there but is being removed adds a lot of complexity. One may point out that ...-eliminate-carefully does Item tree walks which take CPU cycles. My reply is that the walking is done when there's a redundant ORDER BY clause so walking is not on the common code path.

            Takeaways from yesterday's call:

            There are two approaches to fixing this

            "Count references when eliminating"

            This is what bb-10.6-MDEV-28621-v3-eliminate-carefully implements.

            The idea is:

            When we're about to eliminate the ORDER BY clause:

            • Locate the subqueries there
            • For each subquery, check if number of references from the
              ORDER BY clause (there can be multiple) is equal to the
              number of references from this SELECT's select_list.
            • If it is equal, there are no other references and it can be eliminated.

            Advantage of this approach:

            • All the code is used only inside remove_redundant_subquery_clauses().
              There is no logic outside.

            "Reference counters"

            This is what Johnston is trying to implement.

            (my recollection from the call):

            Item_subselect objects should have reference counters.

            Reference counter is incremented at fix_fields() stage. Whenever we
            start referring from somewhere to a select list entry $SEL_LIST_COL,
            we walk $SEL_LIST_COL and increment reference counters for all
            Item_subselect objects we encounter.

            When we eliminate a clause, we walk it and decrement usage counters.
            When a usage counter of a subselect reaches zero, it is eliminated.

            Advantage:

            • this is a more general architecture.

            Disadvantage (SergeP's opinion):

            • maintaining reference counters may be complex, particularly when Item_subselect objects are "wrapped" inside multiple Item_direct_view_ref objects.
            psergei Sergei Petrunia added a comment - Takeaways from yesterday's call: There are two approaches to fixing this "Count references when eliminating" This is what bb-10.6- MDEV-28621 -v3-eliminate-carefully implements. The idea is: When we're about to eliminate the ORDER BY clause: Locate the subqueries there For each subquery, check if number of references from the ORDER BY clause (there can be multiple) is equal to the number of references from this SELECT's select_list. If it is equal, there are no other references and it can be eliminated. Advantage of this approach: All the code is used only inside remove_redundant_subquery_clauses(). There is no logic outside. "Reference counters" This is what Johnston is trying to implement. (my recollection from the call): Item_subselect objects should have reference counters. Reference counter is incremented at fix_fields() stage. Whenever we start referring from somewhere to a select list entry $SEL_LIST_COL, we walk $SEL_LIST_COL and increment reference counters for all Item_subselect objects we encounter. When we eliminate a clause, we walk it and decrement usage counters. When a usage counter of a subselect reaches zero, it is eliminated. Advantage: this is a more general architecture. Disadvantage (SergeP's opinion): maintaining reference counters may be complex, particularly when Item_subselect objects are "wrapped" inside multiple Item_direct_view_ref objects.
            psergei Sergei Petrunia made changes -
            Attachment mdev28621-remaining-case.test [ 73328 ]

            Johnston Please find another testcase I've mentioned yesterday: mdev28621-remaining-case.test

            psergei Sergei Petrunia added a comment - Johnston Please find another testcase I've mentioned yesterday: mdev28621-remaining-case.test
            Johnston Rex Johnston added a comment -

            psergei, i've added the test case (and more) to bb-10.4-MDEV-28621-reference-counter please have a look.
            Counters are updated during object construction and name resolution, so overhead should be minimal.

            Johnston Rex Johnston added a comment - psergei , i've added the test case (and more) to bb-10.4- MDEV-28621 -reference-counter please have a look. Counters are updated during object construction and name resolution, so overhead should be minimal.
            Johnston Rex Johnston made changes -
            Assignee Rex Johnston [ JIRAUSER52533 ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment - - edited

            Hi Johnston

            Input piece #1: I was trying to see how the code would behave for ORDER BY subquery_we_can_eliminate, subquery_we_cannot_eliminate so I've modified the previous example I've posted and ran it:

            create table t1 (a int, b int, c int);
            insert into t1 select seq, seq, seq from seq_1_to_10;
            create table t2 as select * from t1;
            create table t20 as select * from t1;
            create table t21 as select * from t1;
            create table t3 as select * from t1;
             
            create view v2 as
            select
              a, b, 
                (select max(c) from t20 where t20.a<=t2.a) as SUBQ1,
                (select max(c) from t21 where t21.a<=t2.a) as SUBQ2
            from t2;
            

            explain
            select 
              a, a in (select a from v2 where a>3 and v2.subq2>=0 group by v2.subq1, v2.subq2)
            from 
              t1
            

            it crashes at

              Thread 37 "mysqld" received signal SIGSEGV, Segmentation fault.
              [Switching to Thread 0x7fffdcad9700 (LWP 19440)]
              0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510, las
            (gdb) wher  
              #0  0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510,
              #1  0x0000555555e07c3f in JOIN::create_postjoin_aggr_table (this=0x7ffee4068350, tab=0x7ffee4070218, table_fields=0x7ffee4068670, table_group=0x7ffee4017458, save_sum_fields=false, di
              #2  0x0000555555e06176 in JOIN::make_aggr_tables_info (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3540
              #3  0x0000555555e049e8 in JOIN::optimize_stage2 (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3164
              #4  0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450
              #5  0x0000555555dff89e in JOIN::optimize (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763
              #6  0x0000555555d7f3fd in st_select_lex::optimize_unflattened_subqueries (this=0x7ffee40155e0, const_only=false) at /home/psergey/dev-git2/10.4-look2/sql/sql_lex.cc:4347
              #7  0x0000555555fb3f72 in JOIN::optimize_unflattened_subqueries (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/opt_subselect.cc:5610
              #8  0x0000555555e04295 in JOIN::optimize_stage2 (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2998
              #9  0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450
              #10 0x0000555555dff89e in JOIN::optimize (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763
              #11 0x0000555555e0b157 in mysql_select (thd=0x7ffee4000d50, tables=0x7ffee4018208, wild_num=0, fields=@0x7ffee4015740: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffe
            

            I don't really understand what happens or why it crashes there.

            Is this related to this fix at all? Well if I go into remove_redundant_subquery_clauses() and prevent elimination by setting can_eliminate=false with debugger, the crash doesn't occur. I think it's a sufficiently strong indication that the problem might be related to this patch.

            psergei Sergei Petrunia added a comment - - edited Hi Johnston Input piece #1: I was trying to see how the code would behave for ORDER BY subquery_we_can_eliminate, subquery_we_cannot_eliminate so I've modified the previous example I've posted and ran it: create table t1 (a int , b int , c int ); insert into t1 select seq, seq, seq from seq_1_to_10; create table t2 as select * from t1; create table t20 as select * from t1; create table t21 as select * from t1; create table t3 as select * from t1;   create view v2 as select a, b, ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1, ( select max (c) from t21 where t21.a<=t2.a) as SUBQ2 from t2; explain select a, a in ( select a from v2 where a>3 and v2.subq2>=0 group by v2.subq1, v2.subq2) from t1 it crashes at Thread 37 "mysqld" received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7fffdcad9700 (LWP 19440)] 0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510, las (gdb) wher #0 0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510, #1 0x0000555555e07c3f in JOIN::create_postjoin_aggr_table (this=0x7ffee4068350, tab=0x7ffee4070218, table_fields=0x7ffee4068670, table_group=0x7ffee4017458, save_sum_fields=false, di #2 0x0000555555e06176 in JOIN::make_aggr_tables_info (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3540 #3 0x0000555555e049e8 in JOIN::optimize_stage2 (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3164 #4 0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450 #5 0x0000555555dff89e in JOIN::optimize (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763 #6 0x0000555555d7f3fd in st_select_lex::optimize_unflattened_subqueries (this=0x7ffee40155e0, const_only=false) at /home/psergey/dev-git2/10.4-look2/sql/sql_lex.cc:4347 #7 0x0000555555fb3f72 in JOIN::optimize_unflattened_subqueries (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/opt_subselect.cc:5610 #8 0x0000555555e04295 in JOIN::optimize_stage2 (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2998 #9 0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450 #10 0x0000555555dff89e in JOIN::optimize (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763 #11 0x0000555555e0b157 in mysql_select (thd=0x7ffee4000d50, tables=0x7ffee4018208, wild_num=0, fields=@0x7ffee4015740: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffe I don't really understand what happens or why it crashes there. Is this related to this fix at all? Well if I go into remove_redundant_subquery_clauses() and prevent elimination by setting can_eliminate=false with debugger, the crash doesn't occur. I think it's a sufficiently strong indication that the problem might be related to this patch.
            psergei Sergei Petrunia added a comment - - edited

            Another observation is about VIEWs. The patch handles the example from mdev28621-remaining-case.test

            ...
            create view v2 as
            select
              a, b, (select max(c) from t20 where t20.a<=t2.a) as SUBQ1
            from t2;
            

            explain
            select 
              a, a in (select a from v2 where a>3 group by v2.subq1)
            from t1;
            

            shows selects #1 and #2.

            But if copy-paste the VIEW definition into the main query:

            explain
            select 
              a, a in (select a 
                       from
                           (select
                              a, b, (select max(c) from t20 where t20.a<=t2.a) as SUBQ1
                            from t2
                           ) as v2
                        group by v2.subq1
                       )
            from t1;
            

            it shows selects #1, #2, #4.

            UPD: one can achieve the same with a nested VIEW:

            -- Original query. Subquery is eliminated:
            explain
            select 
              a, a in (select a from v2 where a>3 group by v2.SUBQ1)
            from t1;
            

            -- Wrap v2 into v3
            create view v3 as 
            select 
              a, b, SUBQ1 as SUBQ1A
            from v2;
            -- Same query against v3:
            explain
            select 
              a, a in (select a from v3 where a>3 group by v3.SUBQ1A)
            from t1;
            

            psergei Sergei Petrunia added a comment - - edited Another observation is about VIEWs. The patch handles the example from mdev28621-remaining-case.test ... create view v2 as select a, b, ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1 from t2; explain select a, a in ( select a from v2 where a>3 group by v2.subq1) from t1; shows selects #1 and #2. But if copy-paste the VIEW definition into the main query: explain select a, a in ( select a from ( select a, b, ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1 from t2 ) as v2 group by v2.subq1 ) from t1; it shows selects #1, #2, #4. UPD: one can achieve the same with a nested VIEW: -- Original query. Subquery is eliminated: explain select a, a in ( select a from v2 where a>3 group by v2.SUBQ1) from t1; -- Wrap v2 into v3 create view v3 as select a, b, SUBQ1 as SUBQ1A from v2; -- Same query against v3: explain select a, a in ( select a from v3 where a>3 group by v3.SUBQ1A) from t1;

            This piece looks scary:

            @@ -266,7 +272,12 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
                 */
               }
             
            -  eliminated= FALSE;
            +  if (eliminated)               // 2nd execution
            +  {
            +    fixed= 1;
            +    goto end;
            +  }
            +
            

            Actually, we go into the if(eliminated) branch on the first execution (and only there). The rewrite itself seems to be done on PREPARE even for prepared statements. I'm wondering if we could remove this if we've made the rewrite to be done on the first execution...
            I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item?

            psergei Sergei Petrunia added a comment - This piece looks scary: @@ -266,7 +272,12 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) */ }   - eliminated= FALSE; + if (eliminated) // 2nd execution + { + fixed= 1; + goto end; + } + Actually, we go into the if(eliminated) branch on the first execution (and only there). The rewrite itself seems to be done on PREPARE even for prepared statements. I'm wondering if we could remove this if we've made the rewrite to be done on the first execution... I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item?

            diff --git a/sql/item.cc b/sql/item.cc
            index 07463b202f9..1b324456d3b 100644
            --- a/sql/item.cc
            +++ b/sql/item.cc
            @@ -6085,6 +6085,9 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
                                               select->context_analysis_place == IN_GROUP_BY &&
                                              alias_name_used  ?  *rf->ref : rf);
             
            +            if ((*res)->type() == Item::SUBSELECT_ITEM)
            +              ((Item_subselect*)(*res))->reference_count++;
            +
                         /*
                           We can not "move" aggregate function in the place where
                           its arguments are not defined.
            

            Why does this modify the top-level item instead of doing the walk() call?
            I took the testcase that uses it, made the top-level to be multiplication:

            create table t1 (a int, b int, c int);
            insert into t1 select seq, seq, seq from seq_1_to_10;
            create table t2 as select * from t1;
            create table t20 as select * from t1;
            select a, a in
            (
             select
             (
               select max(c) from t20 where t20.a<=t2.a
             )*2 as SUBQ1 from t2 group by SUBQ1+1
            )
            

            and it fails assert like here:

              
              Thread 36 "mysqld" hit Breakpoint 2, Item_field::fix_fields (this=0x7ffee80187e0, thd=0x7ffee8000d50,      reference=0x7ffee8018a18) at /home/psergey/dev-git2/10.4-look2/sql/item.cc:6089
            (gdb) p *res
              $7 = (Item_func_mul *) 0x7ffee8017fa0
            ### if branch not taken
            (gdb) c
              Continuing.
            mysqld: /home/psergey/dev-git2/10.4-look2/sql/item_subselect.cc:757: virtual bool Item_subselect::exec():  Assertion `!eliminated' failed.
            

            psergei Sergei Petrunia added a comment - diff --git a/sql/item.cc b/sql/item.cc index 07463b202f9..1b324456d3b 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6085,6 +6085,9 @@ bool Item_field::fix_fields(THD *thd, Item **reference) select->context_analysis_place == IN_GROUP_BY && alias_name_used ? *rf->ref : rf);   + if ((*res)->type() == Item::SUBSELECT_ITEM) + ((Item_subselect*)(*res))->reference_count++; + /* We can not "move" aggregate function in the place where its arguments are not defined. Why does this modify the top-level item instead of doing the walk() call? I took the testcase that uses it, made the top-level to be multiplication: create table t1 (a int , b int , c int ); insert into t1 select seq, seq, seq from seq_1_to_10; create table t2 as select * from t1; create table t20 as select * from t1; select a, a in ( select ( select max (c) from t20 where t20.a<=t2.a )*2 as SUBQ1 from t2 group by SUBQ1+1 ) and it fails assert like here: Thread 36 "mysqld" hit Breakpoint 2, Item_field::fix_fields (this=0x7ffee80187e0, thd=0x7ffee8000d50, reference=0x7ffee8018a18) at /home/psergey/dev-git2/10.4-look2/sql/item.cc:6089 (gdb) p *res $7 = (Item_func_mul *) 0x7ffee8017fa0 ### if branch not taken (gdb) c Continuing. mysqld: /home/psergey/dev-git2/10.4-look2/sql/item_subselect.cc:757: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Rex Johnston [ JIRAUSER52533 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            Johnston Rex Johnston added a comment - - edited

            I don't really understand what happens or why it crashes there.

            What is happening is that the temporary table being created still has the pointers to the reference to the eliminated item.

                for (; cur_group ; cur_group= cur_group->next, key_part_info++)
                {
                   Field *field=(*cur_group->item)->get_tmp_table_field();
            

            here field is null for the eliminated item.
            There are a few ways we could fix this. We could fabricate another group list for this join, or chuck in an ugly test like this

                for (; cur_group ; cur_group= cur_group->next, key_part_info++)
                {
                  if (((*cur_group->item)->type() == Item::REF_ITEM) &&
                      ((*((Item_ref *)(*cur_group->item))->ref)->type() ==
                                                                   Item::SUBSELECT_ITEM) &&
                      ((Item_subselect *)(*((Item_ref *)(*cur_group->item))->ref))->
                                                                               eliminated )
                    continue;
                  Field *field=(*cur_group->item)->get_tmp_table_field();
            

            Which solves this test case. Thoughts?

            EDIT, much better to correct join->group_list i think.

            Johnston Rex Johnston added a comment - - edited I don't really understand what happens or why it crashes there. What is happening is that the temporary table being created still has the pointers to the reference to the eliminated item. for (; cur_group ; cur_group= cur_group->next, key_part_info++) { Field *field=(*cur_group->item)->get_tmp_table_field(); here field is null for the eliminated item. There are a few ways we could fix this. We could fabricate another group list for this join, or chuck in an ugly test like this for (; cur_group ; cur_group= cur_group->next, key_part_info++) { if (((*cur_group->item)->type() == Item::REF_ITEM) && ((*((Item_ref *)(*cur_group->item))-> ref )->type() == Item::SUBSELECT_ITEM) && ((Item_subselect *)(*((Item_ref *)(*cur_group->item))-> ref ))-> eliminated ) continue ; Field *field=(*cur_group->item)->get_tmp_table_field(); Which solves this test case. Thoughts? EDIT, much better to correct join->group_list i think.
            Johnston Rex Johnston added a comment -

            UPD: one can achieve the same with a nested VIEW:

            if one defines v2 with another column after the subquery, i.e.

            create view v2 as select   a, b, (select max(c) from t20 where t20.a<=t2.a) as SUBQ1, a+b as c from t2;
            

            then

            explain
            select 
              a, a in (select a from v2 where a>3 group by v2.SUBQ1)
            from t1;
            

            we also see an additional "DEPENDENT SUBQUERY" in the explain output.

            Johnston Rex Johnston added a comment - UPD: one can achieve the same with a nested VIEW: if one defines v2 with another column after the subquery, i.e. create view v2 as select a, b, ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1, a+b as c from t2; then explain select a, a in ( select a from v2 where a>3 group by v2.SUBQ1) from t1; we also see an additional "DEPENDENT SUBQUERY" in the explain output.
            Johnston Rex Johnston added a comment - - edited

            Actually, we go into the if(eliminated) branch on the first execution (and only there).
            The rewrite itself seems to be done on PREPARE even for prepared statements.
            I'm wondering if we could remove this if we've made the rewrite to be done on the first execution...
            I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item?

            Test query :

            prepare s from 'select  a, a in (select a from v2 where a>3 group by v2.SUBQ1) from t1';
            execute s;
            

            during check_prepared_statement() as part of "prepare", remove_redundant_subqueries() evaluates the select_lex associated with

            select t2.a from v2 where t2.a > 3 group by (subquery#4)
            

            subquery#4 is referred to only once here, so is removed (from the item_list, join->all_fields and join->group_list and marked eliminated).

            During execution, setup_fields() once again initiates a fix_field cascade of calls, rippling through prepare, setup_fields on subquery#4, Item::fix_fields_if_needed() is called once again on this eliminated item.
            Of course there is now no unit associated with this item and during prepare on the subselect_engine results in a call to uninitialized memory in Item_subselect::fix_fields()/engine->prepare(thd).
            The simplest solution was to check if the item was eliminated and exit Item_subselect::fix_fields() once upper_refs.empty() has been called, which is needed elsewhere.

            Johnston Rex Johnston added a comment - - edited Actually, we go into the if(eliminated) branch on the first execution (and only there). The rewrite itself seems to be done on PREPARE even for prepared statements. I'm wondering if we could remove this if we've made the rewrite to be done on the first execution... I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item? Test query : prepare s from 'select a, a in (select a from v2 where a>3 group by v2.SUBQ1) from t1' ; execute s; during check_prepared_statement() as part of "prepare", remove_redundant_subqueries() evaluates the select_lex associated with select t2.a from v2 where t2.a > 3 group by (subquery#4) subquery#4 is referred to only once here, so is removed (from the item_list, join->all_fields and join->group_list and marked eliminated). During execution, setup_fields() once again initiates a fix_field cascade of calls, rippling through prepare , setup_fields on subquery#4 , Item::fix_fields_if_needed() is called once again on this eliminated item. Of course there is now no unit associated with this item and during prepare on the subselect_engine results in a call to uninitialized memory in Item_subselect::fix_fields()/engine->prepare(thd) . The simplest solution was to check if the item was eliminated and exit Item_subselect::fix_fields() once upper_refs.empty() has been called, which is needed elsewhere.
            Johnston Rex Johnston added a comment -

            Why does this modify the top-level item instead of doing the walk() call?

            Silly me. Good catch, thanks. Fixed in bb-10.4-MDEV-28621-reference-counter

            Johnston Rex Johnston added a comment - Why does this modify the top-level item instead of doing the walk() call? Silly me. Good catch, thanks. Fixed in bb-10.4- MDEV-28621 -reference-counter
            Johnston Rex Johnston made changes -
            Johnston Rex Johnston made changes -
            psergei Sergei Petrunia added a comment - - edited

            Another possible alternative fix: bb-10.6-MDEV-28621-eliminate-in-optimization

            EDIT

            This patch attempted to remove GROUP BY at the query optimization phase.
            It doesn't work in all cases.

            The optimizer has code to remove constant items from GROUP BY expression, but that doesn't remove the grouping operation.
            Attempting to remove the grouping operation itself causes wrong results on the second execution.
            We hit something in Name Resolution code where subquery's references to outside get incorrect attributes.
            This query from subselect_elimination.test:

            select 
              a, 
              a in (select
                      (select max(c) from t20 where t20.a<=t2.a) as SUBQ1 
                    from t2 
                    group by SUBQ1+1
                   )
            from 
              t1
            

            here, "group by SUBQ1+1" is removed, and then the second execution starts to assume that
            (select max(c) from t20 where t20.a<=t2.a is not correlated because t2.a doesn't have OUTER_REF_TABLE_BIT.

            I could narrow it down only to Item_field::fix_outer_field. It has a check in select->group_list.elements which is non-zero on the first PS execution and zero on the second.
            When it is zero, Item_field::fix_outer_field produces an Item object without OUTER_REF_TABLE_BIT.

            psergei Sergei Petrunia added a comment - - edited Another possible alternative fix: bb-10.6- MDEV-28621 -eliminate-in-optimization EDIT This patch attempted to remove GROUP BY at the query optimization phase. It doesn't work in all cases. The optimizer has code to remove constant items from GROUP BY expression, but that doesn't remove the grouping operation. Attempting to remove the grouping operation itself causes wrong results on the second execution. We hit something in Name Resolution code where subquery's references to outside get incorrect attributes. This query from subselect_elimination.test: select a, a in ( select ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1 from t2 group by SUBQ1+1 ) from t1 here, "group by SUBQ1+1" is removed, and then the second execution starts to assume that (select max(c) from t20 where t20.a<=t2.a is not correlated because t2.a doesn't have OUTER_REF_TABLE_BIT. I could narrow it down only to Item_field::fix_outer_field . It has a check in select->group_list.elements which is non-zero on the first PS execution and zero on the second. When it is zero, Item_field::fix_outer_field produces an Item object without OUTER_REF_TABLE_BIT.

            also stumbled on this ...

              /*
                Constant ORDER and/or GROUP expressions that contain subqueries. Such
                expressions need to evaluated to verify that the subquery indeed
                returns a single row. The evaluation of such expressions is delayed
                until query execution.
              */
              List<Item> exec_const_order_group_cond;
            

            Introduced in https://github.com/MariaDB/server/commit/620aea4fde7d40f3870bebdcfd66d2b0b556db2f
            How does this approach agree with what we're trying to implement in this MDEV...

            psergei Sergei Petrunia added a comment - also stumbled on this ... /* Constant ORDER and/or GROUP expressions that contain subqueries. Such expressions need to evaluated to verify that the subquery indeed returns a single row. The evaluation of such expressions is delayed until query execution. */ List<Item> exec_const_order_group_cond; Introduced in https://github.com/MariaDB/server/commit/620aea4fde7d40f3870bebdcfd66d2b0b556db2f How does this approach agree with what we're trying to implement in this MDEV...
            Johnston Rex Johnston added a comment - - edited

            There seems to be a disagreement in parts of the code as to whether removal of the group by statement is permanent or temporary.

            A comment in JOIN::prepare

                 Permanently remove redundant parts from the query if
                   1) This is a subquery
                   2) This is the first time this query is optimized (since the
                      transformation is permanent
                   3) Not normalizing a view. Removal should take place when a
                      query involving a view is optimized, not when the view
                      is created

            implies that it is permanent, yet in the Item tree, the eliminated flag is reset at the end of execution.

            The very simplest solution I've found lies in bb-10.4-MDEV-28621-no-remove-unit, where we simply reset the group list and do not exclude the removed unit from the query graph.

            Johnston Rex Johnston added a comment - - edited There seems to be a disagreement in parts of the code as to whether removal of the group by statement is permanent or temporary. A comment in JOIN::prepare Permanently remove redundant parts from the query if 1) This is a subquery 2) This is the first time this query is optimized (since the transformation is permanent 3) Not normalizing a view. Removal should take place when a query involving a view is optimized, not when the view is created implies that it is permanent, yet in the Item tree, the eliminated flag is reset at the end of execution. The very simplest solution I've found lies in bb-10.4- MDEV-28621 -no-remove-unit, where we simply reset the group list and do not exclude the removed unit from the query graph.
            Johnston Rex Johnston made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            Johnston Rex Johnston made changes -
            Assignee Rex Johnston [ JIRAUSER52533 ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            Johnston Rex Johnston made changes -
            Johnston Rex Johnston made changes -
            Johnston Rex Johnston made changes -
            Johnston Rex Johnston made changes -
            Johnston Rex Johnston made changes -
            psergei Sergei Petrunia made changes -

            On the question in the comment above https://jira.mariadb.org/browse/MDEV-28621?focusedCommentId=284062&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-284062

            about whether an SQL processor can drop the subqueries or has to compute them to verify that they do not produce the "Subquery returns more than one row" error.

            Trying various databases:

            create table t1 (a int);
            insert into t1 values (1),(2),(3);
             
            create table t2 (a int, b int);
            insert into t2 values (1,1), (2,2),(2,3);
            select count(*) from t1 group by (select b from t2 where a=2);
            

            SQLite 3.39: succeeds.
            PostgreSQL 13.7: error
            MySQL 8: error
            Yugabyte, Timesten: error.
            Oracle, SQL Server, DB2 - "subquery is not allowed in GROUP BY".

            I suspect that the optimization "Don't compute constant GROUP BY value" is not common.

            Trying something more common:

            create table t1 (a int);
            insert into t1 values (1),(2),(3);
            create table t2 (a int, b int);
            insert into t2 values (1,1), (2,2),(2,3);
            with T as 
            (
              select 
                a, 
                (select t1.a from t2 where a=2) as b
              from t1
             
            )
            select a from T;
            

            SQL Server : OK
            Oracle: OK
            PostgreSQL: OK
            SQLite: Ok
            MariaDB 10.6: OK but if I add e.g. "LIMIT 10" the CTE, it starts to fail.
            MySQL: error (because CTE is not merged? If I rewrite as derived table, the behavior is same as in MariaDB).

            psergei Sergei Petrunia added a comment - On the question in the comment above https://jira.mariadb.org/browse/MDEV-28621?focusedCommentId=284062&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-284062 about whether an SQL processor can drop the subqueries or has to compute them to verify that they do not produce the "Subquery returns more than one row" error. Trying various databases: create table t1 (a int ); insert into t1 values (1),(2),(3);   create table t2 (a int , b int ); insert into t2 values (1,1), (2,2),(2,3); select count (*) from t1 group by ( select b from t2 where a=2); SQLite 3.39: succeeds. PostgreSQL 13.7: error MySQL 8: error Yugabyte, Timesten: error. Oracle, SQL Server, DB2 - "subquery is not allowed in GROUP BY". I suspect that the optimization "Don't compute constant GROUP BY value" is not common. Trying something more common: create table t1 (a int ); insert into t1 values (1),(2),(3); create table t2 (a int , b int ); insert into t2 values (1,1), (2,2),(2,3); with T as ( select a, ( select t1.a from t2 where a=2) as b from t1   ) select a from T; SQL Server : OK Oracle: OK PostgreSQL: OK SQLite: Ok MariaDB 10.6: OK but if I add e.g. "LIMIT 10" the CTE, it starts to fail. MySQL: error (because CTE is not merged? If I rewrite as derived table, the behavior is same as in MariaDB).
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.3 [ 28565 ]
            Johnston Rex Johnston made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4 [ 22408 ]

            Please also see this comment showing a UBSAN runtime error: member access within null pointer of type 'struct st_select_lex' in st_select_lex_unit::set_limit, discovered in 11.5 optimized.

            Roel Roel Van de Paar added a comment - Please also see this comment showing a UBSAN runtime error: member access within null pointer of type 'struct st_select_lex' in st_select_lex_unit::set_limit, discovered in 11.5 optimized.

            Getting this pushed:
            https://github.com/MariaDB/server/commit/40b3525fcc79aef62ea1ae057ec5687a55c0630b

            MDEV-28621: group by optimization incorrectly removing subquery where…
            … subject buried in a function
             
            Workaround patch: Do not remove GROUP BY clause when it has
            subquer(ies) in it.
            

            This will be followed by a more extensive fix.

            psergei Sergei Petrunia added a comment - Getting this pushed: https://github.com/MariaDB/server/commit/40b3525fcc79aef62ea1ae057ec5687a55c0630b MDEV-28621: group by optimization incorrectly removing subquery where… … subject buried in a function   Workaround patch: Do not remove GROUP BY clause when it has subquer(ies) in it. This will be followed by a more extensive fix.
            serg Sergei Golubchik made changes -
            Priority Blocker [ 1 ] Critical [ 2 ]
            psergei Sergei Petrunia made changes -

            Filed MDEV-34202 to do a full fix (do remove GROUP BY always, even if it has subqueries in it).

            psergei Sergei Petrunia added a comment - Filed MDEV-34202 to do a full fix (do remove GROUP BY always, even if it has subqueries in it).
            psergei Sergei Petrunia made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            psergei Sergei Petrunia made changes -
            Component/s Optimizer [ 10200 ]
            Fix Version/s 11.2.4 [ 29631 ]
            Fix Version/s 11.1.5 [ 29629 ]
            Fix Version/s 11.0.6 [ 29628 ]
            Fix Version/s 10.11.8 [ 29630 ]
            Fix Version/s 10.6.18 [ 29627 ]
            Fix Version/s 10.5.25 [ 29626 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 11.4 [ 29301 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 11.4.2 [ 29633 ]
            psergei Sergei Petrunia made changes -
            Fix Version/s 11.4.1 [ 29523 ]
            Fix Version/s 11.4.2 [ 29633 ]
            Johnston Rex Johnston made changes -

            People

              psergei Sergei Petrunia
              nobody Shihao Wen
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.