Hi Roy,
Thanks for fixing this. The patch is approved. Some minor suggestions
in-line.
On 02/14/2011 04:01 PM, Roy Lyseng wrote:
> #At file:///home/rl136806/mysql/repo/mysql-work5/ based on
revid:roy.lyseng@stripped
>
> 3343 Roy Lyseng 2011-02-14
> Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
>
> Used to be bug#59919.
>
> A crash occurs when a certain subquery is transformed with a
semi join
> and a semi join materialization operation is attempted.
> The subquery contains a nested join structure (an outer join where
> the inner part is itself an inner join).
> Function setup_sj_materialization() tries to lookup the embedding
> semi join nest for an inner table of the subquery. However, it
uses
> the pointer tab->table->pos_in_table_list->embedding, pointing to
> an outer join nest, instead of the correct pointer
tab->emb_sj_nest.
> When dereferencing this pointer, a crash occurs.
>
> mysql-test/include/subquery_sj.inc
> Added test case for bug#11766739
>
> mysql-test/r/subquery_sj_all.result
> mysql-test/r/subquery_sj_all_jcl6.result
> mysql-test/r/subquery_sj_all_jcl7.result
> mysql-test/r/subquery_sj_dupsweed.result
> mysql-test/r/subquery_sj_dupsweed_jcl6.result
> mysql-test/r/subquery_sj_dupsweed_jcl7.result
> mysql-test/r/subquery_sj_firstmatch.result
> mysql-test/r/subquery_sj_firstmatch_jcl6.result
> mysql-test/r/subquery_sj_firstmatch_jcl7.result
> mysql-test/r/subquery_sj_loosescan.result
> mysql-test/r/subquery_sj_loosescan_jcl6.result
> mysql-test/r/subquery_sj_loosescan_jcl7.result
> mysql-test/r/subquery_sj_mat.result
> mysql-test/r/subquery_sj_mat_jcl6.result
> mysql-test/r/subquery_sj_mat_jcl7.result
> mysql-test/r/subquery_sj_mat_nosj.result
> mysql-test/r/subquery_sj_none.result
> mysql-test/r/subquery_sj_none_jcl6.result
> mysql-test/r/subquery_sj_none_jcl7.result
> Added test results for bug#11766739
>
> sql/sql_select.cc
> In setup_sj_materialization(), replaced use of pointer
> tab->table->pos_in_table_list->embedding with
> tab->emb_sj_nest.
>
> modified:
> mysql-test/include/subquery_sj.inc
> mysql-test/r/subquery_sj_all.result
> mysql-test/r/subquery_sj_all_jcl6.result
> mysql-test/r/subquery_sj_all_jcl7.result
> mysql-test/r/subquery_sj_dupsweed.result
> mysql-test/r/subquery_sj_dupsweed_jcl6.result
> mysql-test/r/subquery_sj_dupsweed_jcl7.result
> mysql-test/r/subquery_sj_firstmatch.result
> mysql-test/r/subquery_sj_firstmatch_jcl6.result
> mysql-test/r/subquery_sj_firstmatch_jcl7.result
> mysql-test/r/subquery_sj_loosescan.result
> mysql-test/r/subquery_sj_loosescan_jcl6.result
> mysql-test/r/subquery_sj_loosescan_jcl7.result
> mysql-test/r/subquery_sj_mat.result
> mysql-test/r/subquery_sj_mat_jcl6.result
> mysql-test/r/subquery_sj_mat_jcl7.result
> mysql-test/r/subquery_sj_mat_nosj.result
> mysql-test/r/subquery_sj_none.result
> mysql-test/r/subquery_sj_none_jcl6.result
> mysql-test/r/subquery_sj_none_jcl7.result
> sql/sql_select.cc
> === modified file 'mysql-test/include/subquery_sj.inc'
> --- a/mysql-test/include/subquery_sj.inc 2011-01-27 11:38:22 +0000
> +++ b/mysql-test/include/subquery_sj.inc 2011-02-14 15:01:33 +0000
> @@ -3504,3 +3504,25 @@ deallocate prepare s;
> DROP TABLE ot1, ot2, ot3, it1;
>
> --echo # End of the test for bug#57623.
> +
> +--echo #
> +--echo # Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
> +--echo #
> +
> +CREATE TABLE a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
> +CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
Suggestions, which you may feel free to ignore:
- Use t1, t2 for table names.
- "NOT NULL" is not significant and can be dropped to get a more
minimal test case.
- Since MyISAM is default in this test, it should not be necessary
to specify engine. (I acknowledge that this error does not occur
with Innodb since constant optimizations will not be done.)
> +
> +INSERT INTO a VALUES (1);
> +INSERT INTO b VALUES (1,1), (2,1);
> +
> +let $query=
> +SELECT * FROM b
> +WHERE f2 IN (SELECT a.f1
> + FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON
1=1);
How aboue "ON TRUE" instead of "ON 1=1"?
> +
> +eval EXPLAIN $query;
> +eval $query;
> +
> +DROP TABLE a, b;
> +
> +--echo # End of the test for bug#11766739.
>
...
--
Øystein
