Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-537

Make multi-column non-top level subqueries to be executed via index (index/unique subquery) instead of single_select_engine

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • 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.

        Attachments

          Activity

            People

            • Assignee:
              timour Timour Katchaounov (Inactive)
              Reporter:
              timour Timour Katchaounov (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: