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, 37 insertions, 0 deletions

diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test
index 19a6ecf216f..6f33c71eede 100644
--- a/mysql-test/main/derived_split_innodb.test
+++ b/mysql-test/main/derived_split_innodb.test
@@ -150,3 +150,40 @@ eval set statement optimizer_switch='split_materialized=on' for  $query;
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # Bug mdev-25714: usage non-splitting covering index is cheaper than
+--echo #                 usage of the best splitting index for one group
+--echo #
+
+create table t1 (
+  id int not null, itemid int not null, index idx (itemid)
+) engine=innodb;
+insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3);
+create table t2 (id int not null) engine=innodb;
+insert into t2 values (2);
+create table t3 (
+  id int not null, itemid int not null, userid int not null, primary key (id),
+  index idx1 (userid, itemid), index idx2 (itemid)
+) engine innodb;
+insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1);
+analyze table t1,t2,t3;
+
+let $q=
+select t1.id, t1.itemid, dt.id, t2.id
+  from t1,
+  (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
+  t2
+    where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
+
+set optimizer_switch='split_materialized=on';
+eval explain $q;
+eval $q;
+
+set optimizer_switch='split_materialized=off';
+eval explain $q;
+eval $q;
+
+drop table t1,t2,t3;
+set optimizer_switch='split_materialized=default';
+
+--echo # End of 10.3 tests