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

Crash caused by query containing constant having clause

Details

    Description

      This query consistently causes MariaDB 10.4.15 and 10.5.6 to crash:

      SELECT * FROM (
      SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr,
      provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by,
      UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations,
      bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime
      FROM master_ap2.har_provider provider
      LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid
      LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id
      GROUP BY provider.guid ) provider
      WHERE (
      provider.roa_id IN (7)
      OR
      (SELECT 1
      from har_provider_org
      WHERE provider.guid = har_provider_org.provider_guid
      AND har_provider_org.roa_id IN (7)
      ) )
      AND (
      (guid = 'ckgbm10gb009no74dzys39ce8')
      AND descr = 'fe0c79969b89eb69c32aee361e5bef9e'
      AND type = 1)
      ORDER BY `guid` ASC LIMIT 1;
      

      Minimal configuration as generated by dbdeployer:

      [mysqld]
      user               = sami
      port               = 10415
      socket             = /tmp/mysql_sandbox10415.sock
      basedir            = /home/sami/opt/mysql/10.4.15
      datadir            = /home/sami/sandboxes/msb_10_4_15/data
      tmpdir             = /home/sami/sandboxes/msb_10_4_15/tmp
      pid-file           = /home/sami/sandboxes/msb_10_4_15/data/mysql_sandbox10415.pid
      bind-address       = 127.0.0.1
      report-host=single-10415
      report-port=10415
      log-error=/home/sami/sandboxes/msb_10_4_15/data/msandbox.err
      

      Steps to repeat: import attached dump of three tables, one empty and two have a single row, then execute above query in the master_ap2 schema. Error log shows:

      Thread pointer: 0x55ee82e961c8
      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 = 0x7f5112fb4e70 thread_stack 0x49000
      /home/sami/opt/mysql/10.4.15/bin/mysqld(my_print_stacktrace+0x2e)[0x55ee7f4300be]
      /home/sami/opt/mysql/10.4.15/bin/mysqld(handle_fatal_signal+0x30f)[0x55ee7ee40b1f]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f51383e4390]
      /home/sami/opt/mysql/10.4.15/bin/mysqld(_ZN10Item_equal7val_intEv+0x91)[0x55ee7ee79231]
      sql/item_cmpfunc.cc:7026(Item_equal::val_int())[0x55ee7ed70110]
      sql/sql_type.cc:4446(Type_handler_int_result::Item_val_bool(Item*) const)[0x55ee7ee76e7a]
      sql/item_cmpfunc.cc:5297(Item_cond_and::val_int())[0x55ee7ec86350]
      sql/sql_select.cc:21748(end_send_group(JOIN*, st_join_table*, bool))[0x55ee7ec9329e]
      sql/sql_select.cc:19957(do_select)[0x55ee7ec935d3]
      sql/sql_select.cc:4256(JOIN::exec())[0x55ee7ec91826]
      sql/sql_select.cc:4689(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*))[0x55ee7ebf90d3]
      sql/sql_derived.cc:1266(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x55ee7ebf8c74]
      sql/sql_derived.cc:206(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55ee7ec6f6a9]
      sql/sql_select.cc:13539(st_join_table::preread_init())[0x55ee7ec6f8e8]
      sql/sql_select.cc:20383(sub_select(JOIN*, st_join_table*, bool))[0x55ee7ec933a3]
      sql/sql_select.cc:19956(do_select)[0x55ee7ec935d3]
      sql/sql_select.cc:4256(JOIN::exec())[0x55ee7ec91826]
      sql/sql_select.cc:4689(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*))[0x55ee7ec92387]
      sql/sql_select.cc:422(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55ee7eb277df]
      sql/sql_parse.cc:6356(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55ee7ec367fa]
      sql/sql_parse.cc:3889(mysql_execute_command(THD*))[0x55ee7ec3c11c]
      sql/sql_parse.cc:7896(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55ee7ec3e6ae]
      sql/sql_audit.h:169(mysql_audit_general)[0x55ee7ec3fe89]
      sql/sql_parse.cc:1353(do_command(THD*))[0x55ee7ed1ef9a]
      sql/sql_connect.cc:1412(do_handle_one_connection(CONNECT*))[0x55ee7ed1f07d]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f51383da6ba]
      x86_64/clone.S:111(clone)[0x7f513723f41d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x55ee82ea4500): SELECT * FROM ( SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr, provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by, UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations, bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime FROM master_ap2.har_provider provider LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id GROUP BY provider.guid ) provider WHERE ( provider.roa_id IN (7) OR (SELECT 1 from har_provider_org WHERE provider.guid = har_provider_org.provider_guid AND har_provider_org.roa_id IN (7) ) ) AND ( (guid = 'ckgbm10gb009no74dzys39ce8') AND descr = 'fe0c79969b89eb69c32aee361e5bef9e' AND type = 1) ORDER BY `guid` ASC LIMIT 1
      Connection ID (thread ID): 8
      Status: NOT_KILLED
       
      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on
      
      

      Attachments

        Issue Links

          Activity

            If we compare processing of HAVING with processing of WHERE...
            Suppose there is a condition which has parts that become constant after we've detected and read the const tables (denote them as PART_TO_REMOVE)...
            if the condition is the WHERE condition, then PART_TO_REMOVE is removed by the code in make_join_select(). It calls make_cond_for_table() and PART_TO_REMOVE will be either removed on the spot or put into JOIN::exec_const_cond. Note that remove_eq_conds() is not used in this process.

            If the condition is in the HAVING condition... do we need to change remove_eq_conds() call into a having= make_cond_for_table(having, ...) call to get PART_TO_REMOVE to be removed?

            psergei Sergei Petrunia added a comment - If we compare processing of HAVING with processing of WHERE... Suppose there is a condition which has parts that become constant after we've detected and read the const tables (denote them as PART_TO_REMOVE)... if the condition is the WHERE condition, then PART_TO_REMOVE is removed by the code in make_join_select(). It calls make_cond_for_table() and PART_TO_REMOVE will be either removed on the spot or put into JOIN::exec_const_cond. Note that remove_eq_conds() is not used in this process. If the condition is in the HAVING condition... do we need to change remove_eq_conds() call into a having= make_cond_for_table(having, ...) call to get PART_TO_REMOVE to be removed?

            Ran the main test suite with this.

            diff --git a/sql/sql_select.cc b/sql/sql_select.cc
            index 2ab1736891c..65c01912956 100644
            --- a/sql/sql_select.cc
            +++ b/sql/sql_select.cc
            @@ -2971,7 +2971,7 @@ int JOIN::optimize_stage2()
               if (having && const_table_map && !having->with_sum_func())
               {
                 having->update_used_tables();
            -    having= having->remove_eq_conds(thd, &select_lex->having_value, true);
            +    //having= having->remove_eq_conds(thd, &select_lex->having_value, true);
                 if (select_lex->having_value == Item::COND_FALSE)
                 {
                   having= new (thd->mem_root) Item_bool(thd, false);
            

            it passed...

            psergei Sergei Petrunia added a comment - Ran the main test suite with this. diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2ab1736891c..65c01912956 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2971,7 +2971,7 @@ int JOIN::optimize_stage2() if (having && const_table_map && !having->with_sum_func()) { having->update_used_tables(); - having= having->remove_eq_conds(thd, &select_lex->having_value, true); + //having= having->remove_eq_conds(thd, &select_lex->having_value, true); if (select_lex->having_value == Item::COND_FALSE) { having= new (thd->mem_root) Item_bool(thd, false); it passed...
            igor Igor Babaev added a comment -

            Add the line about the reviewer at the end of comment for the commit and push into 10.5

            igor Igor Babaev added a comment - Add the line about the reviewer at the end of comment for the commit and push into 10.5

            Fix pushed in 10.5 (commit)

            The initial query doesn't cause a crash in 10.5+ anymore:

            MariaDB [master_ap2]> SELECT * FROM (
                -> SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr,
                -> provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by,
                -> UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations,
                -> bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime
                -> FROM master_ap2.har_provider provider
                -> LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid
                -> LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id
                -> GROUP BY provider.guid ) provider
                -> WHERE (
                -> provider.roa_id IN (7)
                -> OR
                -> (SELECT 1
                -> from har_provider_org
                -> WHERE provider.guid = har_provider_org.provider_guid
                -> AND har_provider_org.roa_id IN (7)
                -> ) )
                -> AND (
                -> (guid = 'ckgbm10gb009no74dzys39ce8')
                -> AND descr = 'fe0c79969b89eb69c32aee361e5bef9e'
                -> AND type = 1)
                -> ORDER BY `guid` ASC LIMIT 1;
            +---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+
            | guid                      | roa_id | policy_guid               | filter_guid               | contact_user_id | contact_roa_id | name                             | descr                            | type | origin_guid               | reference_filter_guid | rca_mode | search_additional_orgs | apps_in_biz | edited_by | create_date | edit_date  | additional_organizations | health | availability | risk   | collectionTime |
            +---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+
            | ckgbm10gb009no74dzys39ce8 |      7 | ckgbji3nb008knt4d57b83n2r | ckgbji3yu00lont4d22tp96sm |            NULL |              7 | c9454d783fae7e46f59455bd23611490 | fe0c79969b89eb69c32aee361e5bef9e |    1 | ckfxi252b02ou1fq5odo82ii6 | NULL                  |        0 |                      1 |        NULL |      NULL |  1602806726 | 1602806726 | NULL                     |    100 |         NULL | 0.0000 |     1602866400 |
            +---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+
            1 row in set (0,003 sec)
            
            

            shagalla Galina Shalygina (Inactive) added a comment - Fix pushed in 10.5 ( commit ) The initial query doesn't cause a crash in 10.5+ anymore: MariaDB [master_ap2]> SELECT * FROM ( -> SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider. name , provider.descr, -> provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by, -> UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations, -> bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime -> FROM master_ap2.har_provider provider -> LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid -> LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id -> GROUP BY provider.guid ) provider -> WHERE ( -> provider.roa_id IN (7) -> OR -> ( SELECT 1 -> from har_provider_org -> WHERE provider.guid = har_provider_org.provider_guid -> AND har_provider_org.roa_id IN (7) -> ) ) -> AND ( -> (guid = 'ckgbm10gb009no74dzys39ce8' ) -> AND descr = 'fe0c79969b89eb69c32aee361e5bef9e' -> AND type = 1) -> ORDER BY `guid` ASC LIMIT 1; + ---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+ | guid | roa_id | policy_guid | filter_guid | contact_user_id | contact_roa_id | name | descr | type | origin_guid | reference_filter_guid | rca_mode | search_additional_orgs | apps_in_biz | edited_by | create_date | edit_date | additional_organizations | health | availability | risk | collectionTime | + ---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+ | ckgbm10gb009no74dzys39ce8 | 7 | ckgbji3nb008knt4d57b83n2r | ckgbji3yu00lont4d22tp96sm | NULL | 7 | c9454d783fae7e46f59455bd23611490 | fe0c79969b89eb69c32aee361e5bef9e | 1 | ckfxi252b02ou1fq5odo82ii6 | NULL | 0 | 1 | NULL | NULL | 1602806726 | 1602806726 | NULL | 100 | NULL | 0.0000 | 1602866400 | + ---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+ 1 row in set (0,003 sec)

            For the release notes:

            A query could cause a crash if it has a HAVING clause with a
            construct tblX.column=column_or_constant and the optimizer was
            able to infer that table tblX is a constant table.
            Note that HAVING clause may be from the original query or
            may come from Condition Pushdown optimization (https://mariadb.com/kb/en/condition-pushdown-into-derived-table-optimization/)

            psergei Sergei Petrunia added a comment - For the release notes: A query could cause a crash if it has a HAVING clause with a construct tblX.column=column_or_constant and the optimizer was able to infer that table tblX is a constant table. Note that HAVING clause may be from the original query or may come from Condition Pushdown optimization ( https://mariadb.com/kb/en/condition-pushdown-into-derived-table-optimization/ )

            People

              shagalla Galina Shalygina (Inactive)
              sahlroos Sami Ahlroos
              Votes:
              2 Vote for this issue
              Watchers:
              13 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.