Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
Multi-column non-top level subqueries can be executed via the unique_subquery/index_subquery methods instead of the general single_select engine.
If the same queries are transformed into single-column INs, then unique_subquery/index_subquery is chosen. However in some cases the IN-EXISTS transformation for multi-column subqueries adds unnecessary null-rejecting conditions that prevent the use of the index-based subquery access methods. The problem is that the method {Item_in_subselect::create_row_in_to_exists_cond()}} adds Item_is_not_null_test and Item_func_trig_cond without looking at the left IN operand. At the same time, the analogous method for single columns does that, and doesn't add the above conditions if the left argument cannot be NULL.
The proposed patch is:
@@ -2290,7 +2303,7 @@ Item_in_subselect::create_row_in_to_exis
|
ref_pointer_array+i,
|
(char *)"<no matter>",
|
(char *)"<list ref>"));
|
- if (!abort_on_null)
|
+ if (!abort_on_null && select_lex->ref_pointer_array[i]->maybe_null)
|
{
|
Item *having_col_item=
|
new Item_is_not_null_test(this,
|
@@ -2309,10 +2322,6 @@ Item_in_subselect::create_row_in_to_exis
|
(char *)"<no matter>",
|
(char *)"<list ref>"));
|
item= new Item_cond_or(item, item_isnull);
|
- /*
|
- TODO: why we create the above for cases where the right part
|
- cant be NULL?
|
- */
|
if (left_expr->element_index(i)->maybe_null)
|
{
|
if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
|
@@ -2323,6 +2332,11 @@ Item_in_subselect::create_row_in_to_exis
|
}
|
*having_item= and_items(*having_item, having_col_item);
|
}
|
+ if (!abort_on_null && left_expr->element_index(i)->maybe_null)
|
+ {
|
+ if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
|
+ DBUG_RETURN(true);
|
+ }
|
*where_item= and_items(*where_item, item);
|
}
|
}
|
The change is proposed for 10.0 because it will change all affected query plans to use new access methods.