On 08/11/10 05:42 PM, Evgeny Potemkin wrote:
>> - if (*tab->on_expr_ref&& !table->null_row)
>> + /* We will evaluate on-expressions here only if it is not considered
>> + expensive. This also prevents executing materialized subqueries
>> + in optimization phase. This is necessary since proper setup for
>> + such execution has not been done at this stage. (See comment in
>> + internal_remove_eq_conds() tagged
>> + */
>> + if (*tab->on_expr_ref&& !table->null_row&&
>> + !(*tab->on_expr_ref)->is_expensive())
> IMO, the proper check would be:
> if (*tab->on_expr_ref&& !table->null_row &&
> !((*tab->on_expr_ref)->is_expensive() &&
> The reason is that is_expensive() returns true also for UDF and SP
> Using it to detect a subselect is actually abuse. Look:
> SELECT ... FROM t1 LEFT JOIN t2 ON an_expensive_sp_func() = 2 ...
> And lets the function always return 0. In this case 'impossible where'
> will be found much later and some resource would be wasted.
> Same, btw, applies to WHERE clause.
The main intention here is not to detect subselects. It is used to
avoid evaluation of expensive queries in the optimization phase. As a
side effect, it also fixes the issue reported in this bug report. If
UDFs return TRUE for functions that are not expensive to evaluate, I
think that is a separate problem that needs to be fixed.
Yes, the same applies to WHERE, and here is Sergey Petrunia's
reasoning for why it is done this way (from the commit comment of
the patch for BUG#36133):
- Fixed by making the range optimizer not to evaluate expressions
that have item->is_expensive() == TRUE (these are materialization
subqueries and stored function calls). This should also resolve
the problem that EXPLAIN may be too long. This change cuts off
some opportunities for range optimizer, but this is the price
we're willing to pay for separation of query optimization and
I see no reason to handle ON-clauses differently,