[MDEV-435] Expensive subqueries may be evaluated during optimization in merge_key_fields Created: 2012-08-06  Updated: 2012-10-12  Due: 2012-10-12  Resolved: 2012-10-12

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.25
Fix Version/s: 5.5.28

Type: Bug Priority: Major
Reporter: Timour Katchaounov (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
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

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2012-08-21 ]

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

Comment by Sergei Golubchik [ 2012-10-12 ]

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

Comment by Timour Katchaounov (Inactive) [ 2012-10-12 ]

Pushed to MariaDB 5.5.28

Generated at Thu Feb 08 06:28:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.