MDEV-26345 SELECT MIN on Spider table returns more rows than expected

The Spider storage engine ignored the implicit grouping when
aggregation was converted to constant by the query optimizer.
As a result, the Spider SE returned rows more than expected.

To fix the problem, we notify the Spider SE of the existence of
the implicit grouping via Query::distinct.

5 files changed, 102 insertions, 7 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9311eb4fa18..fa25cdc98ad 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3278,13 +3278,12 @@ bool JOIN::make_aggr_tables_info()
       /*
         Check if the storage engine can intercept the query.
 
-        JOIN::optimize_stage2() might convert DISTINCT into GROUP BY and then
-        optimize away GROUP BY (group_list). In such a case, we need to notify
-        a storage engine supporting a group by handler of the existence of the
-        original DISTINCT. Thus, we set select_distinct || group_optimized_away
-        to Query::distinct.
+        The query optimizer might optimize away aggregation functins or DISTINCT.
+        In such a cage, we need to notify a storage engine supporting a group by
+        handler of the existence of the original explicit or implicit grouping.
+        Thus, we set select_distinct || implicit_grouping to Query::distinct.
       */
-      Query query= {&all_fields, select_distinct || group_optimized_away, tables_list,
+      Query query= {&all_fields, select_distinct || implicit_grouping, tables_list,
                     conds, group_list, order ? order : group_list, having};
       group_by_handler *gbh= ht->create_group_by(thd, &query);
 
diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_26345.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_26345.result
new file mode 100644
index 00000000000..d99782d7c30
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_26345.result
@@ -0,0 +1,42 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+MDEV-26345 SELECT MIN on Spider table returns more rows than expected
+
+connection child2_1;
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+CREATE TABLE tbl_a (
+`a`int,
+`b`int,
+PRIMARY KEY (`a`, `b`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+insert into `tbl_a` VALUES (1,1), (1,2), (2,11);
+connection master_1;
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+CREATE TABLE tbl_a (
+`a`int,
+`b`int,
+PRIMARY KEY (`a`, `b`)
+) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_a"' PARTITION BY LIST COLUMNS(`b`) (
+PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+connection master_1;
+SELECT MIN(b), a FROM tbl_a WHERE a=1;
+MIN(b)	a
+1	1
+connection master_1;
+DROP DATABASE IF EXISTS auto_test_remote;
+connection child2_1;
+DROP DATABASE IF EXISTS auto_test_remote;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_26345.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_26345.cnf
new file mode 100644
index 00000000000..05dfd8a0bce
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26345.cnf
@@ -0,0 +1,3 @@
+!include include/default_mysqld.cnf
+!include ../my_1_1.cnf
+!include ../my_2_1.cnf
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_26345.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_26345.test
new file mode 100644
index 00000000000..5ac1e0b0891
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26345.test
@@ -0,0 +1,51 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../../t/test_init.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+
+--echo
+--echo MDEV-26345 SELECT MIN on Spider table returns more rows than expected
+--echo
+
+--connection child2_1
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+eval CREATE TABLE tbl_a (
+    `a`int,
+    `b`int,
+    PRIMARY KEY (`a`, `b`)
+) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+insert into `tbl_a` VALUES (1,1), (1,2), (2,11);
+
+--connection master_1
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+eval CREATE TABLE tbl_a (
+    `a`int,
+    `b`int,
+    PRIMARY KEY (`a`, `b`)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_a"' PARTITION BY LIST COLUMNS(`b`) (
+    PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"'
+);
+
+--connection master_1
+SELECT MIN(b), a FROM tbl_a WHERE a=1;
+
+--connection master_1
+DROP DATABASE IF EXISTS auto_test_remote;
+--connection child2_1
+DROP DATABASE IF EXISTS auto_test_remote;
+
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
diff --git a/storage/spider/mysql-test/spider/r/direct_join.result b/storage/spider/mysql-test/spider/r/direct_join.result
index a1018c35fbf..e4fbe6407af 100644
--- a/storage/spider/mysql-test/spider/r/direct_join.result
+++ b/storage/spider/mysql-test/spider/r/direct_join.result
@@ -167,7 +167,7 @@ connection child2_1;
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
 argument
 select `id`,`hr_status`,`region_code`,`region` from `auto_test_remote`.`tbl_person` where `id` = '24FC3F0A5119432BAE13DD65AABAA39C' and `region` = 510411
-select count(0) `count(0)` from `auto_test_remote`.`tbl_ncd_cm_person` t0 where ((t0.`person_id` = '24FC3F0A5119432BAE13DD65AABAA39C') and (t0.`diseaseKind_id` = '52A0328740914BCE86ED10A4D2521816'))
+select distinct count(0) `count(0)` from `auto_test_remote`.`tbl_ncd_cm_person` t0 where ((t0.`person_id` = '24FC3F0A5119432BAE13DD65AABAA39C') and (t0.`diseaseKind_id` = '52A0328740914BCE86ED10A4D2521816'))
 SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
 SELECT * FROM tbl_person;
 id	hr_status	region_code	region