MDEV-25714 Join using derived with aggregation returns incorrect results

If a join query uses a derived table (view / CTE) with GROUP BY clause then
the execution plan for such join may employ split optimization. When this
optimization is employed the derived table is not materialized. Rather only
some partitions of the derived table are subject to grouping. Split
optimization can be applied only if:
- there are some indexes over the tables used in the join specifying the
  derived table whose prefixes partially cover the field items used in the
  GROUP BY list (such indexes are called splitting indexes)
- the WHERE condition of the join query contains conjunctive equalities
  between columns of the derived table that comprise major parts of
  splitting indexes and columns of the other join tables.

When the optimizer evaluates extending of a partial join by the rows of the
derived table it always considers a possibility of using split optimization.
Different splitting indexes can be used depending on the extended partial
join. At some rare conditions, for example, when there is a non-splitting
covering index for a table joined in the join specifying the derived table
usage of a splitting index to produce rows needed for grouping may be still
less beneficial than usage of such covering index without any splitting
technique. The function JOIN_TAB::choose_best_splitting() must take this
into account.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

1 files changed, 22 insertions, 5 deletions

diff --git a/sql/opt_split.cc b/sql/opt_split.cc
index c3a2d03a93b..edf9ae3deff 100644
--- a/sql/opt_split.cc
+++ b/sql/opt_split.cc
@@ -960,11 +960,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
       in the cache
     */
     spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts);
-    if (!spl_plan &&
-	(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) &&
-	(spl_plan->best_positions=
-	   (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) &&
-	!spl_opt_info->plan_cache.push_back(spl_plan))
+    if (!spl_plan)
     {
       /*
         The plan for the chosen key has not been found in the cache.
@@ -974,6 +970,27 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
       reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table,
                                       best_key, remaining_tables, true);
       choose_plan(join, all_table_map & ~join->const_table_map);
+
+      /*
+        Check that the chosen plan is really a splitting plan.
+        If not or if there is not enough memory to save the plan in the cache
+        then just return with no splitting plan.
+      */
+      POSITION *first_non_const_pos= join->best_positions + join->const_tables;
+      TABLE *table= first_non_const_pos->table->table;
+      key_map spl_keys= table->keys_usable_for_splitting;
+      if (!(first_non_const_pos->key &&
+            spl_keys.is_set(first_non_const_pos->key->key)) ||
+          !(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) ||
+	  !(spl_plan->best_positions=
+	     (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) ||
+	  spl_opt_info->plan_cache.push_back(spl_plan))
+      {
+        reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table,
+                                        best_key, remaining_tables, false);
+        return 0;
+      }
+
       spl_plan->keyuse_ext_start= best_key_keyuse_ext_start;
       spl_plan->table= best_table;
       spl_plan->key= best_key;