Details
-
Type:
Task
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Fix Version/s: 10.0.2
-
Component/s: None
-
Labels: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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
merged & tested with latest 10.0, pushed to 10.0.02
The implementation has been tested by Elena, and is waiting for 10.0.1 to be released in order to be pushed to 10.0.2.