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

Expensive subqueries may be evaluated during optimization in merge_key_fields

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.25
    • 5.5.28
    • None
    • None

    Description

      The following example extracted from https://mariadb.atlassian.net/browse/MDEV-430 results in evaluation of expensive subqueries during query optimization.

      SET optimizer_switch = 'materialization=on,semijoin=on';

      CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (8),(0);

      CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (4,'j'),(6,'v');

      CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES ('b'),('c');

      EXPLAIN
      SELECT * FROM t1 WHERE a = (
      SELECT MAX(b) FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 )
      ) OR a = 10;

      The relevant call stack is:

      #6 0x084866e9 in Item_singlerow_subselect::val_int (this=0x966d438) at /home/psergey/dev2/5.5-look7/sql/item_subselect.cc:1196
      #7 0x0819053c in Item::update_null_value (this=0x966d438) at /home/psergey/dev2/5.5-look7/sql/item.h:1055
      #8 0x08493a94 in Item_subselect::is_null (this=0x966d438) at /home/psergey/dev2/5.5-look7/sql/item_subselect.h:172
      #9 0x082879fd in merge_key_fields (start=0x970e410, new_fields=0x970e42c, end=0x970e448, and_level=3) at /home/psergey/dev2/5.5-look7/sql/sql_select.cc:3874

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          timour Timour Katchaounov (Inactive) added a comment -

          Analysis:

          1.
          During EXPLAIN the middle subquery is executed during optimization of the outer query.
          At the end of this execution, the server calls JOIN_TAB::cleanup as follows. This call
          sets JOIN_TAB::table = NULL.

          #0 st_join_table::cleanup (this=0x334cbd8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:10287
          #1 0x000000000067171d in JOIN::cleanup (this=0x3344548, full=true) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:10633
          #2 0x0000000000671425 in JOIN::join_free (this=0x3344548) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:10550
          #3 0x000000000067ddc3 in do_select (join=0x3344548, fields=0x33448d8, table=0x0, procedure=0x0) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:15823
          #4 0x000000000065ebce in JOIN::exec (this=0x3344548) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:2833
          #5 0x000000000086a2eb in subselect_single_select_engine::exec (this=0x3340388) at /home/tsk/mprog/src/5.5/sql/item_subselect.cc:3167
          #6 0x0000000000862fdf in Item_subselect::exec (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item_subselect.cc:640
          #7 0x00000000008646d8 in Item_singlerow_subselect::val_int (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item_subselect.cc:1196
          #8 0x0000000000595059 in Item::update_null_value (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item.h:1055
          #9 0x000000000087012d in Item_subselect::is_null (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item_subselect.h:172
          #10 0x0000000000661917 in merge_key_fields (start=0x334e1b0, new_fields=0x334e1e0, end=0x334e210, and_level=3) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:3884
          #11 0x000000000066277c in add_key_fields (join=0x3343db8, key_fields=0x7fffcdc481a0, and_level=0x7fffcdc481ac, cond=0x3340848, usable_tables=18446744073709551615, sargables=0x7fffcdc48288) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:4264
          #12 0x00000000006640af in update_ref_and_keys (thd=0x322f080, keyuse=0x3344098, join_tab=0x334d880, tables=1, cond=0x3340848, normal_tables=18446744073709551615, select_lex=0x3231ae0, sargables=0x7fffcdc48288) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:4812
          #13 0x000000000066012f in make_join_statistics (join=0x3343db8, tables_list=..., conds=0x3340848, keyuse_array=0x3344098) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:3339
          #14 0x0000000000658bdd in JOIN::optimize (this=0x3343db8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:1209

          2. Next optimization/execution proceeds, until it reaches the second call to JOIN::exec that
          runs select_describe for the middle subquery. As explained above, the previous execution
          deleted JOIN_TAB::table. Thus the call to select_describe for this JOIN crashes at this
          line:

          TABLE_LIST *table_list= tab->table->pos_in_table_list;

          Because tab->table is NULL. The call stack is:

          #0 0x000000000068b21e in select_describe (join=0x3344548, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:21419
          #1 0x000000000065ce5b in JOIN::exec (this=0x3344548) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:2345
          #2 0x000000000065f3c5 in mysql_select (thd=0x322f080, rref_pointer_array=0x3329c20, tables=0x332a688, wild_num=0, fields=..., conds=0x334ade8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x33409f8, unit=0x3329d10, select_lex=0x33299c8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:3053
          #3 0x000000000068d583 in mysql_explain_union (thd=0x322f080, unit=0x3329d10, result=0x33409f8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:21956
          #4 0x000000000068d2ae in select_describe (join=0x3343db8, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:21913
          #5 0x000000000065ce5b in JOIN::exec (this=0x3343db8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:2345

          timour Timour Katchaounov (Inactive) added a comment - Analysis: 1. During EXPLAIN the middle subquery is executed during optimization of the outer query. At the end of this execution, the server calls JOIN_TAB::cleanup as follows. This call sets JOIN_TAB::table = NULL. #0 st_join_table::cleanup (this=0x334cbd8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:10287 #1 0x000000000067171d in JOIN::cleanup (this=0x3344548, full=true) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:10633 #2 0x0000000000671425 in JOIN::join_free (this=0x3344548) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:10550 #3 0x000000000067ddc3 in do_select (join=0x3344548, fields=0x33448d8, table=0x0, procedure=0x0) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:15823 #4 0x000000000065ebce in JOIN::exec (this=0x3344548) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:2833 #5 0x000000000086a2eb in subselect_single_select_engine::exec (this=0x3340388) at /home/tsk/mprog/src/5.5/sql/item_subselect.cc:3167 #6 0x0000000000862fdf in Item_subselect::exec (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item_subselect.cc:640 #7 0x00000000008646d8 in Item_singlerow_subselect::val_int (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item_subselect.cc:1196 #8 0x0000000000595059 in Item::update_null_value (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item.h:1055 #9 0x000000000087012d in Item_subselect::is_null (this=0x3340250) at /home/tsk/mprog/src/5.5/sql/item_subselect.h:172 #10 0x0000000000661917 in merge_key_fields (start=0x334e1b0, new_fields=0x334e1e0, end=0x334e210, and_level=3) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:3884 #11 0x000000000066277c in add_key_fields (join=0x3343db8, key_fields=0x7fffcdc481a0, and_level=0x7fffcdc481ac, cond=0x3340848, usable_tables=18446744073709551615, sargables=0x7fffcdc48288) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:4264 #12 0x00000000006640af in update_ref_and_keys (thd=0x322f080, keyuse=0x3344098, join_tab=0x334d880, tables=1, cond=0x3340848, normal_tables=18446744073709551615, select_lex=0x3231ae0, sargables=0x7fffcdc48288) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:4812 #13 0x000000000066012f in make_join_statistics (join=0x3343db8, tables_list=..., conds=0x3340848, keyuse_array=0x3344098) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:3339 #14 0x0000000000658bdd in JOIN::optimize (this=0x3343db8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:1209 2. Next optimization/execution proceeds, until it reaches the second call to JOIN::exec that runs select_describe for the middle subquery. As explained above, the previous execution deleted JOIN_TAB::table. Thus the call to select_describe for this JOIN crashes at this line: TABLE_LIST *table_list= tab->table->pos_in_table_list; Because tab->table is NULL. The call stack is: #0 0x000000000068b21e in select_describe (join=0x3344548, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:21419 #1 0x000000000065ce5b in JOIN::exec (this=0x3344548) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:2345 #2 0x000000000065f3c5 in mysql_select (thd=0x322f080, rref_pointer_array=0x3329c20, tables=0x332a688, wild_num=0, fields=..., conds=0x334ade8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x33409f8, unit=0x3329d10, select_lex=0x33299c8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:3053 #3 0x000000000068d583 in mysql_explain_union (thd=0x322f080, unit=0x3329d10, result=0x33409f8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:21956 #4 0x000000000068d2ae in select_describe (join=0x3343db8, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:21913 #5 0x000000000065ce5b in JOIN::exec (this=0x3343db8) at /home/tsk/mprog/src/5.5/sql/sql_select.cc:2345
          serg Sergei Golubchik added a comment -

          Timour can push on behalf of Sergey Petrunia, who is on vacations

          serg Sergei Golubchik added a comment - Timour can push on behalf of Sergey Petrunia, who is on vacations
          timour Timour Katchaounov (Inactive) added a comment -

          Pushed to MariaDB 5.5.28

          timour Timour Katchaounov (Inactive) added a comment - Pushed to MariaDB 5.5.28

          People

            timour Timour Katchaounov (Inactive)
            timour Timour Katchaounov (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.