Hi Roy,
Ok to push with minor code style changes. See below.
Regards, Evgen.
On 01/12/11 19:12, Roy Lyseng wrote:
> #At file:///home/rl136806/mysql/repo/mysql-review/ based on
> revid:olav.sandstaa@stripped
>
> 3322 Roy Lyseng 2011-01-12
> Bug#58561: Server Crash with correlated subquery and MyISAM tables
> when semijoin=on
>
> The problem here is that the variable 'sargables' contains invalid data
> after call to update_ref_and_keys(), causing a segmentation fault.
> Further inspection showed that the number of conditions was calculated
> erroneously, and it was also noticed that it was because
> thd->lex->current_select was not equal to select_lex passed as
> argument.
> The culprit was found to be subselect_single_select_engine::exec()
> that did not restore the value of 'current_select' after an engine
> was changed.
>
> Problem is fixed by restoring current_select properly.
> We also clean up the code slightly by replacing
> thd->lex->current_select in update_ref_and_keys()
> with the passed select_lex argument.
>
> mysql-test/include/subquery.inc
> Added test case for bug#58561.
>
> mysql-test/r/subquery_all.result
> mysql-test/r/subquery_all_jcl6.result
> mysql-test/r/subquery_nomat_nosj.result
> mysql-test/r/subquery_nomat_nosj_jcl6.result
> mysql-test/r/subquery_none.result
> mysql-test/r/subquery_none_jcl6.result
> Added test results for bug#58561.
>
> sql/item_subselect.cc
> In subselect_single_select_engine::exec(), restored saved
> 'current_select' when an engine is changed.
> Implemented 'clean exit' policy as a code cleanup fix.
>
> sql/sql_select.cc
> In update_ref_and_keys(), replaced reference to
> thd->lex->current_select with the select_lex passed as argument.
> This is a code cleanup fix.
>
> modified:
> mysql-test/include/subquery.inc
> mysql-test/r/subquery_all.result
> mysql-test/r/subquery_all_jcl6.result
> mysql-test/r/subquery_nomat_nosj.result
> mysql-test/r/subquery_nomat_nosj_jcl6.result
> mysql-test/r/subquery_none.result
> mysql-test/r/subquery_none_jcl6.result
> sql/item_subselect.cc
> sql/sql_select.cc
> === modified file 'mysql-test/include/subquery.inc'
> --- a/mysql-test/include/subquery.inc 2010-11-23 15:18:44 +0000
> +++ b/mysql-test/include/subquery.inc 2011-01-12 16:08:16 +0000
> @@ -5067,6 +5067,65 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (S
> --echo
> DROP TABLE t1;
>
> +--echo #
> +--echo # BUG#58561: Server Crash with correlated subquery and MyISAM tables
> +--echo #
> +
> +CREATE TABLE cc (
> + pk INT,
> + col_int_key INT,
> + col_varchar_key VARCHAR(1),
> + PRIMARY KEY (pk),
> + KEY col_int_key (col_int_key),
> + KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO cc VALUES (10,7,'v');
> +INSERT INTO cc VALUES (11,1,'r');
> +
> +CREATE TABLE bb (
> + pk INT,
> + col_date_key DATE,
> + PRIMARY KEY (pk),
> + KEY col_date_key (col_date_key)
> +) ENGINE=MyISAM;
> +INSERT INTO bb VALUES (10,'2002-02-21');
> +
> +CREATE TABLE c (
> + pk INT,
> + col_int_key INT,
> + col_varchar_key VARCHAR(1),
> + PRIMARY KEY (pk),
> + KEY col_int_key (col_int_key),
> + KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO c VALUES (1,NULL,'w');
> +INSERT INTO c VALUES (19,NULL,'f');
> +
> +CREATE TABLE b (
> + pk INT,
> + col_int_key INT,
> + col_varchar_key VARCHAR(1),
> + PRIMARY KEY (pk),
> + KEY col_int_key (col_int_key),
> + KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO b VALUES (1,7,'f');
> +
> +SELECT col_int_key
> +FROM b granparent1
> +WHERE (col_int_key, col_int_key) IN (
> + SELECT parent1.pk, parent1.pk
> + FROM bb parent1 JOIN cc parent2
> + ON parent2.col_varchar_key = parent2.col_varchar_key
> + WHERE granparent1.col_varchar_key IN (
> + SELECT col_varchar_key
> + FROM c)
> + AND parent1.pk = granparent1.col_int_key
> + ORDER BY parent1.col_date_key
> +);
> +
> +DROP TABLE bb, b, cc, c;
> +
> --echo End of 5.6 tests
>
> --echo #
>
> === modified file 'mysql-test/r/subquery_all.result'
> --- a/mysql-test/r/subquery_all.result 2010-12-16 17:38:26 +0000
> +++ b/mysql-test/r/subquery_all.result 2011-01-12 16:08:16 +0000
> @@ -6214,6 +6214,59 @@ id select_type table type possible_keys
> 2 SUBQUERY t1 ref a a 5 const 1 Using index
>
> DROP TABLE t1;
> +#
> +# BUG#58561: Server Crash with correlated subquery and MyISAM tables
> +#
> +CREATE TABLE cc (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO cc VALUES (10,7,'v');
> +INSERT INTO cc VALUES (11,1,'r');
> +CREATE TABLE bb (
> +pk INT,
> +col_date_key DATE,
> +PRIMARY KEY (pk),
> +KEY col_date_key (col_date_key)
> +) ENGINE=MyISAM;
> +INSERT INTO bb VALUES (10,'2002-02-21');
> +CREATE TABLE c (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO c VALUES (1,NULL,'w');
> +INSERT INTO c VALUES (19,NULL,'f');
> +CREATE TABLE b (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO b VALUES (1,7,'f');
> +SELECT col_int_key
> +FROM b granparent1
> +WHERE (col_int_key, col_int_key) IN (
> +SELECT parent1.pk, parent1.pk
> +FROM bb parent1 JOIN cc parent2
> +ON parent2.col_varchar_key = parent2.col_varchar_key
> +WHERE granparent1.col_varchar_key IN (
> +SELECT col_varchar_key
> +FROM c)
> +AND parent1.pk = granparent1.col_int_key
> +ORDER BY parent1.col_date_key
> +);
> +col_int_key
> +DROP TABLE bb, b, cc, c;
> End of 5.6 tests
> #
> # BUG#46743 "Azalea processing correlated, aggregate SELECT
>
> === modified file 'mysql-test/r/subquery_all_jcl6.result'
> --- a/mysql-test/r/subquery_all_jcl6.result 2010-11-30 13:55:22 +0000
> +++ b/mysql-test/r/subquery_all_jcl6.result 2011-01-12 16:08:16 +0000
> @@ -6218,6 +6218,59 @@ id select_type table type possible_keys
> 2 SUBQUERY t1 ref a a 5 const 1 Using index
>
> DROP TABLE t1;
> +#
> +# BUG#58561: Server Crash with correlated subquery and MyISAM tables
> +#
> +CREATE TABLE cc (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO cc VALUES (10,7,'v');
> +INSERT INTO cc VALUES (11,1,'r');
> +CREATE TABLE bb (
> +pk INT,
> +col_date_key DATE,
> +PRIMARY KEY (pk),
> +KEY col_date_key (col_date_key)
> +) ENGINE=MyISAM;
> +INSERT INTO bb VALUES (10,'2002-02-21');
> +CREATE TABLE c (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO c VALUES (1,NULL,'w');
> +INSERT INTO c VALUES (19,NULL,'f');
> +CREATE TABLE b (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO b VALUES (1,7,'f');
> +SELECT col_int_key
> +FROM b granparent1
> +WHERE (col_int_key, col_int_key) IN (
> +SELECT parent1.pk, parent1.pk
> +FROM bb parent1 JOIN cc parent2
> +ON parent2.col_varchar_key = parent2.col_varchar_key
> +WHERE granparent1.col_varchar_key IN (
> +SELECT col_varchar_key
> +FROM c)
> +AND parent1.pk = granparent1.col_int_key
> +ORDER BY parent1.col_date_key
> +);
> +col_int_key
> +DROP TABLE bb, b, cc, c;
> End of 5.6 tests
> #
> # BUG#46743 "Azalea processing correlated, aggregate SELECT
>
> === modified file 'mysql-test/r/subquery_nomat_nosj.result'
> --- a/mysql-test/r/subquery_nomat_nosj.result 2010-11-30 13:55:22 +0000
> +++ b/mysql-test/r/subquery_nomat_nosj.result 2011-01-12 16:08:16 +0000
> @@ -6214,6 +6214,59 @@ id select_type table type possible_keys
> 2 SUBQUERY t1 ref a a 5 const 1 Using index
>
> DROP TABLE t1;
> +#
> +# BUG#58561: Server Crash with correlated subquery and MyISAM tables
> +#
> +CREATE TABLE cc (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO cc VALUES (10,7,'v');
> +INSERT INTO cc VALUES (11,1,'r');
> +CREATE TABLE bb (
> +pk INT,
> +col_date_key DATE,
> +PRIMARY KEY (pk),
> +KEY col_date_key (col_date_key)
> +) ENGINE=MyISAM;
> +INSERT INTO bb VALUES (10,'2002-02-21');
> +CREATE TABLE c (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO c VALUES (1,NULL,'w');
> +INSERT INTO c VALUES (19,NULL,'f');
> +CREATE TABLE b (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO b VALUES (1,7,'f');
> +SELECT col_int_key
> +FROM b granparent1
> +WHERE (col_int_key, col_int_key) IN (
> +SELECT parent1.pk, parent1.pk
> +FROM bb parent1 JOIN cc parent2
> +ON parent2.col_varchar_key = parent2.col_varchar_key
> +WHERE granparent1.col_varchar_key IN (
> +SELECT col_varchar_key
> +FROM c)
> +AND parent1.pk = granparent1.col_int_key
> +ORDER BY parent1.col_date_key
> +);
> +col_int_key
> +DROP TABLE bb, b, cc, c;
> End of 5.6 tests
> #
> # BUG#46743 "Azalea processing correlated, aggregate SELECT
>
> === modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
> --- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2010-11-30 13:55:22 +0000
> +++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-01-12 16:08:16 +0000
> @@ -6218,6 +6218,59 @@ id select_type table type possible_keys
> 2 SUBQUERY t1 ref a a 5 const 1 Using index
>
> DROP TABLE t1;
> +#
> +# BUG#58561: Server Crash with correlated subquery and MyISAM tables
> +#
> +CREATE TABLE cc (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO cc VALUES (10,7,'v');
> +INSERT INTO cc VALUES (11,1,'r');
> +CREATE TABLE bb (
> +pk INT,
> +col_date_key DATE,
> +PRIMARY KEY (pk),
> +KEY col_date_key (col_date_key)
> +) ENGINE=MyISAM;
> +INSERT INTO bb VALUES (10,'2002-02-21');
> +CREATE TABLE c (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO c VALUES (1,NULL,'w');
> +INSERT INTO c VALUES (19,NULL,'f');
> +CREATE TABLE b (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO b VALUES (1,7,'f');
> +SELECT col_int_key
> +FROM b granparent1
> +WHERE (col_int_key, col_int_key) IN (
> +SELECT parent1.pk, parent1.pk
> +FROM bb parent1 JOIN cc parent2
> +ON parent2.col_varchar_key = parent2.col_varchar_key
> +WHERE granparent1.col_varchar_key IN (
> +SELECT col_varchar_key
> +FROM c)
> +AND parent1.pk = granparent1.col_int_key
> +ORDER BY parent1.col_date_key
> +);
> +col_int_key
> +DROP TABLE bb, b, cc, c;
> End of 5.6 tests
> #
> # BUG#46743 "Azalea processing correlated, aggregate SELECT
>
> === modified file 'mysql-test/r/subquery_none.result'
> --- a/mysql-test/r/subquery_none.result 2010-11-29 13:30:18 +0000
> +++ b/mysql-test/r/subquery_none.result 2011-01-12 16:08:16 +0000
> @@ -6213,6 +6213,59 @@ id select_type table type possible_keys
> 2 SUBQUERY t1 ref a a 5 const 1 Using index
>
> DROP TABLE t1;
> +#
> +# BUG#58561: Server Crash with correlated subquery and MyISAM tables
> +#
> +CREATE TABLE cc (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO cc VALUES (10,7,'v');
> +INSERT INTO cc VALUES (11,1,'r');
> +CREATE TABLE bb (
> +pk INT,
> +col_date_key DATE,
> +PRIMARY KEY (pk),
> +KEY col_date_key (col_date_key)
> +) ENGINE=MyISAM;
> +INSERT INTO bb VALUES (10,'2002-02-21');
> +CREATE TABLE c (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO c VALUES (1,NULL,'w');
> +INSERT INTO c VALUES (19,NULL,'f');
> +CREATE TABLE b (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO b VALUES (1,7,'f');
> +SELECT col_int_key
> +FROM b granparent1
> +WHERE (col_int_key, col_int_key) IN (
> +SELECT parent1.pk, parent1.pk
> +FROM bb parent1 JOIN cc parent2
> +ON parent2.col_varchar_key = parent2.col_varchar_key
> +WHERE granparent1.col_varchar_key IN (
> +SELECT col_varchar_key
> +FROM c)
> +AND parent1.pk = granparent1.col_int_key
> +ORDER BY parent1.col_date_key
> +);
> +col_int_key
> +DROP TABLE bb, b, cc, c;
> End of 5.6 tests
> #
> # BUG#46743 "Azalea processing correlated, aggregate SELECT
>
> === modified file 'mysql-test/r/subquery_none_jcl6.result'
> --- a/mysql-test/r/subquery_none_jcl6.result 2010-11-29 13:04:34 +0000
> +++ b/mysql-test/r/subquery_none_jcl6.result 2011-01-12 16:08:16 +0000
> @@ -6217,6 +6217,59 @@ id select_type table type possible_keys
> 2 SUBQUERY t1 ref a a 5 const 1 Using index
>
> DROP TABLE t1;
> +#
> +# BUG#58561: Server Crash with correlated subquery and MyISAM tables
> +#
> +CREATE TABLE cc (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO cc VALUES (10,7,'v');
> +INSERT INTO cc VALUES (11,1,'r');
> +CREATE TABLE bb (
> +pk INT,
> +col_date_key DATE,
> +PRIMARY KEY (pk),
> +KEY col_date_key (col_date_key)
> +) ENGINE=MyISAM;
> +INSERT INTO bb VALUES (10,'2002-02-21');
> +CREATE TABLE c (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO c VALUES (1,NULL,'w');
> +INSERT INTO c VALUES (19,NULL,'f');
> +CREATE TABLE b (
> +pk INT,
> +col_int_key INT,
> +col_varchar_key VARCHAR(1),
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=MyISAM;
> +INSERT INTO b VALUES (1,7,'f');
> +SELECT col_int_key
> +FROM b granparent1
> +WHERE (col_int_key, col_int_key) IN (
> +SELECT parent1.pk, parent1.pk
> +FROM bb parent1 JOIN cc parent2
> +ON parent2.col_varchar_key = parent2.col_varchar_key
> +WHERE granparent1.col_varchar_key IN (
> +SELECT col_varchar_key
> +FROM c)
> +AND parent1.pk = granparent1.col_int_key
> +ORDER BY parent1.col_date_key
> +);
> +col_int_key
> +DROP TABLE bb, b, cc, c;
> End of 5.6 tests
> #
> # BUG#46743 "Azalea processing correlated, aggregate SELECT
>
> === modified file 'sql/item_subselect.cc'
> --- a/sql/item_subselect.cc 2010-12-29 00:38:59 +0000
> +++ b/sql/item_subselect.cc 2011-01-12 16:08:16 +0000
> @@ -2303,6 +2303,7 @@ int join_read_next_same_or_null(READ_REC
> int subselect_single_select_engine::exec()
> {
> DBUG_ENTER("subselect_single_select_engine::exec");
> + int rc= 0;
> char const *save_where= thd->where;
> SELECT_LEX *save_select= thd->lex->current_select;
> thd->lex->current_select= select_lex;
> @@ -2313,16 +2314,19 @@ int subselect_single_select_engine::exec
> unit->set_limit(unit->global_parameters);
> if (join->optimize())
> {
> - thd->where= save_where;
> - executed= 1;
> - thd->lex->current_select= save_select;
> - DBUG_RETURN(join->error ? join->error : 1);
> + executed= true;
> + rc= join->error ? join->error : 1;
> + goto exit;
> }
> if (save_join_if_explain())
> - DBUG_RETURN(1); /* purecov: inspected */
> + {
> + rc= 1;
> + goto exit;
> + }
> if (item->engine_changed)
> {
> - DBUG_RETURN(1);
> + rc= 1;
> + goto exit;
> }
IMHO it would be more readable to write:
if (save_join_if_explain() || item->engine_changed)
{
rc= 1;
goto exit;
}
But it's up to you.
> }
> if (select_lex->uncacheable&&
> @@ -2331,9 +2335,8 @@ int subselect_single_select_engine::exec
> {
> if (join->reinit())
> {
> - thd->where= save_where;
> - thd->lex->current_select= save_select;
> - DBUG_RETURN(1);
> + rc= 1;
> + goto exit;
> }
> item->reset();
> item->assigned((executed= 0));
> @@ -2389,14 +2392,15 @@ int subselect_single_select_engine::exec
> tab->read_first_record= tab->save_read_first_record;
> tab->read_record.read_record= tab->save_read_record;
> }
> - executed= 1;
> - thd->where= save_where;
> - thd->lex->current_select= save_select;
> - DBUG_RETURN(join->error||thd->is_fatal_error);
> + executed= true;
> +
> + rc= join->error||thd->is_fatal_error;
Please add spaces around ||.
> }
> +
> +exit:
> thd->where= save_where;
> thd->lex->current_select= save_select;
> - DBUG_RETURN(0);
> + DBUG_RETURN(rc);
> }
>
> int subselect_union_engine::exec()
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-12-29 00:38:59 +0000
> +++ b/sql/sql_select.cc 2011-01-12 16:08:16 +0000
> @@ -6166,8 +6166,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
> substitutions.
> */
> sz= max(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))*
> - (((thd->lex->current_select->cond_count+1)*2 +
> - thd->lex->current_select->between_count)*m+1);
> + (((select_lex->cond_count+1)*2 +
> + select_lex->between_count)*m+1);
Please add spaces around * and +.
> if (!(key_fields=(KEY_FIELD*) thd->alloc(sz)))
> return TRUE; /* purecov: inspected */
> and_level= 0;
>
>
>
>
