[#MDEV-537] Make multi-column non-top level subqueries to be executed via index (index/unique subquery) instead of single_select

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.

@@ -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.

[MDEV-537] Make multi-column non-top level subqueries to be executed via index (index/unique subquery) instead of single_select_engine Created: 2012-09-17 Updated: 2013-02-07 Resolved: 2013-02-07
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Fix Version/s:	10.0.2

[MDEV-537] Make multi-column non-top level subqueries to be executed via index (index/unique subquery) instead of single_select_engine Created: 2012-09-17 Updated: 2013-02-07 Resolved: 2013-02-07