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

Expression with constant subquery causes a crash in pushdown from HAVING

Details

    Description

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

      Stack Trace:
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 0x7f8c4b87d880 thread_stack 0x5fc00
      /usr/local/mysql/bin/mariadbd(__interceptor_backtrace+0x5b)[0x781b5b]
      mysys/stacktrace.c:215(my_print_stacktrace)[0x228cfae]
      sql/signal_handler.cc:0(handle_fatal_signal)[0x12bd0d2]
      sigaction.c:0(__restore_rt)[0x7f8c6f617420]
      sql/sql_analyze_stmt.h:172(Time_and_counter_tracker::incr_loops())[0xc45ee8]
      /usr/local/mysql/bin/mariadbd(_ZN30subselect_single_select_engine4execEv+0xb26)[0x15b5176]
      sql/item_subselect.cc:817(Item_subselect::exec())[0x159115c]
      sql/item_subselect.cc:1525(Item_singlerow_subselect::val_decimal(my_decimal*))[0x1596fec]
      sql/sql_type.cc:293(VDec)[0x10a15d0]
      sql/sql_type.h:541(VDec2_lazy)[0x1443b5b]
      sql/sql_type.cc:300(VDec_op)[0x10c4c0a]
      sql/sql_type.cc:293(VDec)[0x10a15d0]
      sql/sql_type.h:541(VDec2_lazy)[0x14416cb]
      sql/sql_type.cc:300(VDec_op)[0x10c4c0a]
      sql/sql_type.cc:293(VDec)[0x10a15d0]
      sql/sql_type.h:433(Dec_ptr::is_null() const)[0x1393ea4]
      sql/item_cmpfunc.cc:1830(Item_func_eq::val_int())[0x13982b2]
      sql_select.cc:0(evaluate_join_record(JOIN*, st_join_table*, int))[0xca1131]
      /usr/local/mysql/bin/mariadbd(_Z10sub_selectP4JOINP13st_join_tableb+0x661)[0xbe6c51]
      /usr/local/mysql/bin/mariadbd(_ZN4JOIN10exec_innerEv+0x2681)[0xc48751]
      sql/sql_select.cc:4721(JOIN::exec())[0xc45f19]
      sql/sql_select.cc:5251(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*))[0xbe89b8]
      sql/sql_select.cc:628(handle_select(THD*, LEX*, select_result*, unsigned long long))[0xbe7e59]
      sql/sql_parse.cc:6041(execute_sqlcom_select(THD*, TABLE_LIST*))[0xb41bc6]
      /usr/local/mysql/bin/mariadbd(_Z21mysql_execute_commandP3THDb+0x18b7)[0xb319a7]
      sql/sql_class.h:2830(THD::enter_stage(PSI_stage_info_v1 const*, char const*, char const*, unsigned int))[0xb24c79]
      /usr/local/mysql/bin/mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x2cf8)[0xb1e648]
      sql/sql_parse.cc:1407(do_command(THD*, bool))[0xb25971]
      sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0xf0d066]
      sql/sql_connect.cc:1322(handle_one_connection)[0xf0caa9]
      perfschema/pfs.cc:2203(pfs_spawn_thread)[0x19d710b]
      nptl/pthread_create.c:478(start_thread)[0x7f8c6f60b609]
      addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530f28)
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f8c6f323133]

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34650 main.having_cond_pushdown test failure - crash server (s390x)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            Thanks! This is the same as MDEV-32424

            Version: '10.4.32-MariaDB-debug-log' 
            mysqld: /10.4/src/sql/sql_select.cc:4344: bool JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool): Assertion `select_lex->select_number == (0x7fffffff * 2U + 1U) || select_lex->select_number == 0x7fffffff || !output || !output->get_select(select_lex->select_number) || output->get_select(select_lex->select_number)->select_lex == select_lex' failed.
            		 
            231109 14:34:15 [ERROR] mysqld got signal 6 ;
            		 
             
            		 
            Server version: 10.4.32-MariaDB-debug-log source revision: 62d80652be7c19f4ad2bf68d6ffbb4e1eb1d77ea
            		 
             
            		 
            sql/sql_select.cc:4351(JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool))[0x564a4253f65a]
            		 
            sql/sql_select.cc:1680(JOIN::build_explain())[0x564a425228f5]
            		 
            sql/sql_select.cc:1737(JOIN::optimize())[0x564a425232b4]
            		 
            sql/item_subselect.cc:3948(subselect_single_select_engine::exec())[0x564a42e87092]
            		 
            sql/item_subselect.cc:758(Item_subselect::exec())[0x564a42e63392]
            		 
            sql/item_subselect.cc:1463(Item_singlerow_subselect::val_decimal(my_decimal*))[0x564a42e6a253]
            		 
            sql/sql_type.cc:195(VDec::VDec(Item*))[0x564a429d4460]
            		 
            sql/sql_type.h:361(VDec2_lazy::VDec2_lazy(Item*, Item*))[0x564a42dd6b84]
            		 
            sql/item_func.cc:1413(Item_func_mul::decimal_op(my_decimal*))[0x564a42d952c2]
            		 
            sql/sql_type.cc:202(VDec_op::VDec_op(Item_func_hybrid_field_type*))[0x564a429d466c]
            		 
            sql/sql_type.cc:4903(Type_handler_decimal_result::Item_func_hybrid_field_type_val_decimal(Item_func_hybrid_field_type*, my_decimal*) const)[0x564a429f0fcf]
            		 
            sql/item_func.h:850(Item_func_hybrid_field_type::val_decimal(my_decimal*))[0x564a4242babc]
            		 
            sql/sql_type.cc:195(VDec::VDec(Item*))[0x564a429d4460]
            		 
            sql/sql_type.h:361(VDec2_lazy::VDec2_lazy(Item*, Item*))[0x564a42dd6b84]
            		 
            sql/item_func.cc:1207(Item_func_plus::decimal_op(my_decimal*))[0x564a42d9290a]
            		 
            sql/sql_type.cc:202(VDec_op::VDec_op(Item_func_hybrid_field_type*))[0x564a429d466c]
            		 
            sql/sql_type.cc:4903(Type_handler_decimal_result::Item_func_hybrid_field_type_val_decimal(Item_func_hybrid_field_type*, my_decimal*) const)[0x564a429f0fcf]
            		 
            sql/item_func.h:850(Item_func_hybrid_field_type::val_decimal(my_decimal*))[0x564a4242babc]
            		 
            sql/sql_type.cc:195(VDec::VDec(Item*))[0x564a429d4460]
            		 
            sql/item_cmpfunc.cc:872(Arg_comparator::compare_decimal())[0x564a42cfd26b]
            		 
            sql/item_cmpfunc.h:104(Arg_comparator::compare())[0x564a42d409a6]
            		 
            sql/item_cmpfunc.cc:1790(Item_func_eq::val_int())[0x564a42d08783]
            		 
            sql/sql_type.cc:4638(Type_handler_int_result::Item_val_bool(Item*) const)[0x564a429ee92c]
            		 
            sql/item.h:1475(Item::val_bool())[0x564a421a448a]
            		 
            sql/item.h:1483(Item::eval_const_cond())[0x564a42600b78]
            		 
            sql/sql_select.cc:17887(Item_bool_func2::remove_eq_conds(THD*, Item::cond_result*, bool))[0x564a4259e110]
            		 
            sql/sql_select.cc:5477(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x564a42549f69]
            		 
            sql/sql_select.cc:2388(JOIN::optimize_inner())[0x564a4252a1ef]
            		 
            sql/sql_select.cc:1731(JOIN::optimize())[0x564a42523205]
            		 
            sql/sql_select.cc:4832(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*))[0x564a425441ef]
            		 
            sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564a42514c56]
            		 
            sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564a4247bc5c]
            		 
            sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x564a424693d3]
            		 
            sql/sql_parse.cc:8014(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564a424851d7]
            		 
            sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564a4245b563]
            		 
            sql/sql_parse.cc:1378(do_command(THD*))[0x564a4245808e]
            		 
            sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x564a4286c67e]
            		 
            sql/sql_connect.cc:1324(handle_one_connection)[0x564a4286bf22]
            		 
            perfschema/pfs.cc:1871(pfs_spawn_thread)[0x564a43509bb0]
            		 
            nptl/pthread_create.c:478(start_thread)[0x7f5d97557609]
            		 
             
            		 
            Query (0x62b0000a1290): SELECT * FROM v0 GROUP BY v2 HAVING ( SELECT v2 FROM v0 WHERE v1 > 57 OR v2 > 83 AND v2 NOT LIKE 'x' ORDER BY v1 * v2 ) = v1 AND v1 * v2 + 52 = 'x' ORDER BY v2
            		 
             

            alice Alice Sherepa added a comment - Thanks! This is the same as MDEV-32424 Version: '10.4.32-MariaDB-debug-log' mysqld: /10.4/src/sql/sql_select.cc:4344: bool JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool): Assertion `select_lex->select_number == (0x7fffffff * 2U + 1U) || select_lex->select_number == 0x7fffffff || !output || !output->get_select(select_lex->select_number) || output->get_select(select_lex->select_number)->select_lex == select_lex' failed. 231109 14:34:15 [ERROR] mysqld got signal 6 ;   Server version: 10.4.32-MariaDB-debug-log source revision: 62d80652be7c19f4ad2bf68d6ffbb4e1eb1d77ea   sql/sql_select.cc:4351(JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool))[0x564a4253f65a] sql/sql_select.cc:1680(JOIN::build_explain())[0x564a425228f5] sql/sql_select.cc:1737(JOIN::optimize())[0x564a425232b4] sql/item_subselect.cc:3948(subselect_single_select_engine::exec())[0x564a42e87092] sql/item_subselect.cc:758(Item_subselect::exec())[0x564a42e63392] sql/item_subselect.cc:1463(Item_singlerow_subselect::val_decimal(my_decimal*))[0x564a42e6a253] sql/sql_type.cc:195(VDec::VDec(Item*))[0x564a429d4460] sql/sql_type.h:361(VDec2_lazy::VDec2_lazy(Item*, Item*))[0x564a42dd6b84] sql/item_func.cc:1413(Item_func_mul::decimal_op(my_decimal*))[0x564a42d952c2] sql/sql_type.cc:202(VDec_op::VDec_op(Item_func_hybrid_field_type*))[0x564a429d466c] sql/sql_type.cc:4903(Type_handler_decimal_result::Item_func_hybrid_field_type_val_decimal(Item_func_hybrid_field_type*, my_decimal*) const)[0x564a429f0fcf] sql/item_func.h:850(Item_func_hybrid_field_type::val_decimal(my_decimal*))[0x564a4242babc] sql/sql_type.cc:195(VDec::VDec(Item*))[0x564a429d4460] sql/sql_type.h:361(VDec2_lazy::VDec2_lazy(Item*, Item*))[0x564a42dd6b84] sql/item_func.cc:1207(Item_func_plus::decimal_op(my_decimal*))[0x564a42d9290a] sql/sql_type.cc:202(VDec_op::VDec_op(Item_func_hybrid_field_type*))[0x564a429d466c] sql/sql_type.cc:4903(Type_handler_decimal_result::Item_func_hybrid_field_type_val_decimal(Item_func_hybrid_field_type*, my_decimal*) const)[0x564a429f0fcf] sql/item_func.h:850(Item_func_hybrid_field_type::val_decimal(my_decimal*))[0x564a4242babc] sql/sql_type.cc:195(VDec::VDec(Item*))[0x564a429d4460] sql/item_cmpfunc.cc:872(Arg_comparator::compare_decimal())[0x564a42cfd26b] sql/item_cmpfunc.h:104(Arg_comparator::compare())[0x564a42d409a6] sql/item_cmpfunc.cc:1790(Item_func_eq::val_int())[0x564a42d08783] sql/sql_type.cc:4638(Type_handler_int_result::Item_val_bool(Item*) const)[0x564a429ee92c] sql/item.h:1475(Item::val_bool())[0x564a421a448a] sql/item.h:1483(Item::eval_const_cond())[0x564a42600b78] sql/sql_select.cc:17887(Item_bool_func2::remove_eq_conds(THD*, Item::cond_result*, bool))[0x564a4259e110] sql/sql_select.cc:5477(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x564a42549f69] sql/sql_select.cc:2388(JOIN::optimize_inner())[0x564a4252a1ef] sql/sql_select.cc:1731(JOIN::optimize())[0x564a42523205] sql/sql_select.cc:4832(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*))[0x564a425441ef] sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x564a42514c56] sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564a4247bc5c] sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x564a424693d3] sql/sql_parse.cc:8014(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564a424851d7] sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564a4245b563] sql/sql_parse.cc:1378(do_command(THD*))[0x564a4245808e] sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x564a4286c67e] sql/sql_connect.cc:1324(handle_one_connection)[0x564a4286bf22] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x564a43509bb0] nptl/pthread_create.c:478(start_thread)[0x7f5d97557609]   Query (0x62b0000a1290): SELECT * FROM v0 GROUP BY v2 HAVING ( SELECT v2 FROM v0 WHERE v1 > 57 OR v2 > 83 AND v2 NOT LIKE 'x' ORDER BY v1 * v2 ) = v1 AND v1 * v2 + 52 = 'x' ORDER BY v2  
            shagalla Galina Shalygina (Inactive) added a comment -

            Still crashing after the fix for MDEV-32424, so I reopen the bug.

            The bug can be related to pushdown from having into where optimization, as the query doesn't crash with this optimization disabled.

            MariaDB [t]> set statement optimizer_switch='condition_pushdown_from_having=off' for
            		 
                -> SELECT * FROM v0 GROUP BY v2 HAVING ( SELECT v2 FROM v0 WHERE v1 > 57 OR v2 > 83 AND v2 NOT LIKE 'x' ORDER BY v1 * v2 ) = v1 AND v1 * v2 + 52 = 'x' ORDER BY v2 ;
            		 
            Empty set (0,001 sec)

            shagalla Galina Shalygina (Inactive) added a comment - Still crashing after the fix for MDEV-32424 , so I reopen the bug. The bug can be related to pushdown from having into where optimization, as the query doesn't crash with this optimization disabled. MariaDB [t]> set statement optimizer_switch= 'condition_pushdown_from_having=off' for -> SELECT * FROM v0 GROUP BY v2 HAVING ( SELECT v2 FROM v0 WHERE v1 > 57 OR v2 > 83 AND v2 NOT LIKE 'x' ORDER BY v1 * v2 ) = v1 AND v1 * v2 + 52 = 'x' ORDER BY v2 ; Empty set (0,001 sec)
            shagalla Galina Shalygina (Inactive) added a comment - - edited

            The query can be simplified to this:

            CREATE TABLE t1 (a INT, b INT);
            		 
            INSERT INTO t1 VALUES (1, 1) ;
            		 
             
            		 
            SELECT * FROM t1
            		 
            GROUP BY b
            		 
            HAVING (SELECT b FROM t1) = a AND a + b = 2;

            When pushdown from having into where optimization is entered, HAVING clause is looking this way:

            (subquery#2) + t1.b = 2 and multiple equal((subquery#2), t1.a)

            where (subquery#2) is a reference to the constant subquery.

            HAVING clause is looked through to understand what conditions can be pushed into WHERE. The condition that can be pushed is appeared to be (subquery#2) + t1.b = 2 as t1.b is a grouping field, and subquery is a constant.
            Then this condition is cleaned up and fixed, so the reference to the constant subquery is cleaned up and fixed. That causes a crash and should be avoided (see MDEV-21184, MDEV-29363).

            To fix this issue constant subquery should be marked with IMMUTABLE_FL to avoid cleaning up and fixing.

            shagalla Galina Shalygina (Inactive) added a comment - - edited The query can be simplified to this: CREATE TABLE t1 (a INT , b INT ); INSERT INTO t1 VALUES (1, 1) ;   SELECT * FROM t1 GROUP BY b HAVING ( SELECT b FROM t1) = a AND a + b = 2; When pushdown from having into where optimization is entered, HAVING clause is looking this way: (subquery#2) + t1.b = 2 and multiple equal((subquery#2), t1.a) where (subquery#2) is a reference to the constant subquery. HAVING clause is looked through to understand what conditions can be pushed into WHERE. The condition that can be pushed is appeared to be (subquery#2) + t1.b = 2 as t1.b is a grouping field, and subquery is a constant. Then this condition is cleaned up and fixed, so the reference to the constant subquery is cleaned up and fixed. That causes a crash and should be avoided (see MDEV-21184 , MDEV-29363 ). To fix this issue constant subquery should be marked with IMMUTABLE_FL to avoid cleaning up and fixing.
            igor Igor Babaev (Inactive) added a comment -

            Ok to push into 10.5

            igor Igor Babaev (Inactive) added a comment - Ok to push into 10.5
            shagalla Galina Shalygina (Inactive) added a comment -

            Pushed in 10.5 (commit)

            The initial query is working correctly in 10.5

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

            shagalla Galina Shalygina (Inactive) added a comment - Pushed in 10.5 ( commit ) The initial query is working correctly in 10.5 MariaDB [test]> CREATE TABLE v0 ( v1 NUMERIC NOT NULL PRIMARY KEY , v2 NUMERIC ) engine=MYISAM; MariaDB [test]> INSERT INTO v0 VALUES ( 127 , -1 ) ; MariaDB [test]> UPDATE v0 SET v1 = -128 WHERE v2 = 255 ; MariaDB [test]> UPDATE v0 SET v1 = 5 WHERE v1 = NULL ; MariaDB [test]> UPDATE v0 SET v2 = 89 WHERE v1 = -2147483648 OR v1 = 57 ; MariaDB [test]> UPDATE v0 SET v2 = 49 WHERE v1 = 95 ;   MariaDB [test]> SELECT * FROM v0 GROUP BY v2 HAVING ( SELECT v2 FROM v0 WHERE v1 > 57 OR v2 > 83 AND v2 NOT LIKE 'x' ORDER BY v1 * v2 ) = v1 AND v1 * v2 + 52 = 'x' ORDER BY v2 ; Empty set , 1 warning (0,001 sec)

            People

              shagalla Galina Shalygina (Inactive)
              csfuzz csfuzz
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.