Sorry by accident, the below comment was deleted:
> + DBUG_PRINT("info", ("Checking if subq can be converted to
semi-join"));
> + /*
> + Check if we're in subquery that is a candidate for flattening into a
> + semi-join (which is done in flatten_subqueries()). The
requirements are:
> + 1. Subquery predicate is an IN/=ANY subquery predicate
> + 2. Subquery is a single SELECT (not a UNION)
> + 3. Subquery does not have GROUP BY or ORDER BY
> + 4. Subquery does not use aggregate functions or HAVING
> + 5. Subquery predicate is at the AND-top-level of ON/WHERE clause
> + 6. We are not in a subquery of a single table UPDATE/DELETE that
> + doesn't have a JOIN (TODO: We should handle this at some
> + point by switching to multi-table UPDATE/DELETE)
> + 7. We're not in a confluent table-less subquery, like "SELECT 1".
> + 8. No execution method was already chosen (by a prepared
statement)
> + 9. Parent select is not a confluent table-less select
> + 10. Neither parent nor child select have STRAIGHT_JOIN option.
> + */
> + if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_SEMIJOIN) &&
> + subq_predicate->substype() == Item_subselect::IN_SUBS &&
// 1
> + !select_lex->is_part_of_union() &&
// 2
> + !select_lex->group_list.elements && !join->order &&
// 3
> + !join->having && !select_lex->with_sum_func &&
// 4
> + thd->thd_marker.emb_on_expr_nest &&
// 5
> + select_lex->outer_select()->join &&
// 6
> + select_lex->master_unit()->first_select()->leaf_tables &&
// 7
> + in_exists_predicate->exec_method ==
> + Item_exists_subselect::EXEC_UNSPECIFIED
&& // 8
> + select_lex->outer_select()->leaf_tables &&
// 9
> + !((join->select_options |
> + select_lex->outer_select()->join->select_options)
> + & SELECT_STRAIGHT_JOIN))
// 10
I find it a bit confusing that two pointers, subq_predicate and
in_exists_predicate are used for the same object in the same
statement. I think it would be better to use in_exists_predicate in
both places.
--
Øystein
