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

Expensive subqueries may be evaluated during optimization in merge_key_fields

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.5.25
        • Fix Version/s: 5.5.28
        • Component/s: None
        • Labels:
          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

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                timour Timour Katchaounov 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

                Show
                timour Timour Katchaounov 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
                Hide
                Permalink
                serg Sergei Golubchik added a comment -

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

                Show
                serg Sergei Golubchik added a comment - Timour can push on behalf of Sergey Petrunia, who is on vacations
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                Pushed to MariaDB 5.5.28

                Show
                timour Timour Katchaounov added a comment - Pushed to MariaDB 5.5.28

                  People

                  • Assignee:
                    timour Timour Katchaounov
                    Reporter:
                    timour Timour Katchaounov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Due:
                      Created:
                      Updated:
                      Resolved:

                      Time Tracking

                      Estimated:
                      Original Estimate - 4 hours
                      4h
                      Remaining:
                      Time Spent - 2 hours, 45 minutes Remaining Estimate - 1 hour, 15 minutes
                      1h 15m
                      Logged:
                      Time Spent - 2 hours, 45 minutes Remaining Estimate - 1 hour, 15 minutes
                      2h 45m