From b9730226dce5bf34b87aa28963f1df68a695a93c Mon Sep 17 00:00:00 2001 From: Nayuta Yanagisawa Date: Sun, 22 Aug 2021 19:52:10 +0000 Subject: 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. --- sql/sql_select.cc | 11 +++-- .../mysql-test/spider/bugfix/r/mdev_26345.result | 42 ++++++++++++++++++ .../mysql-test/spider/bugfix/t/mdev_26345.cnf | 3 ++ .../mysql-test/spider/bugfix/t/mdev_26345.test | 51 ++++++++++++++++++++++ .../spider/mysql-test/spider/r/direct_join.result | 2 +- 5 files changed, 102 insertions(+), 7 deletions(-) create mode 100644 storage/spider/mysql-test/spider/bugfix/r/mdev_26345.result create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_26345.cnf create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_26345.test 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 -- cgit v1.2.1