revision-id: b1d92f72934afaf82a1c28580f2a2b26cba4fca7 (mariadb-10.3.31-70-gb1d92f72934) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-13 16:41:37 +0300 message: MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values Disable LATERAL DERIVED optimization for subqueries that have WITH ROLLUP. This bug could affect queries with grouping derived tables / views / CTEs with ROLLUP. The bug could manifest itself if the corresponding materialized derived tables are subject to split optimization. The current implementation of the split optimization produces rows from the derived table in an arbitrary order. So these rows must be accumulated in another temporary table and sorted according to the used GROUP BY clause in order to be able to generate the additional ROLLUP rows. This patch prohibits to use split optimization for grouping derived tables / views / CTEs with ROLLUP. --- mysql-test/main/derived_split_innodb.result | 42 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 36 +++++++++++++++++++++++++ sql/opt_split.cc | 5 +++- 3 files changed, 82 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 7ea3b689f23..8162bfcdae7 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -234,4 +234,46 @@ id itemid id id 4 2 4 2 drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +# +# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results +# (The testcase is taken from testcase for MDEV-13389 due to it being +# much smaller) +# +create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; +insert into t3 values +(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), +(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), +(5,14,'dd'), (9,12,'ee'); +create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; +insert into t4 values +(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), +(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'), +(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'), +(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); +insert into t4 select a+10, b+10, concat(c,'f') from t4; +analyze table t3,t4; +Table Op Msg_type Msg_text +test.t3 analyze status OK +test.t4 analyze status OK +# This should use a plan with LATERAL DERIVED: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range idx_b idx_b 5 NULL 3 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 +2 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 +# ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range idx_b idx_b 5 NULL 3 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 +2 DERIVED t4 ALL NULL NULL NULL NULL 40 Using filesort +drop table t3, t4; # End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 6f33c71eede..2a3565be36f 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -186,4 +186,40 @@ eval $q; drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +--echo # +--echo # MDEV-26337: subquery with groupby and ROLLUP returns incorrect results +--echo # (The testcase is taken from testcase for MDEV-13389 due to it being +--echo # much smaller) +--echo # + +create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; +insert into t3 values +(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), +(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), +(5,14,'dd'), (9,12,'ee'); +create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; +insert into t4 values +(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), +(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'), +(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'), +(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); +insert into t4 select a+10, b+10, concat(c,'f') from t4; +analyze table t3,t4; + +--echo # This should use a plan with LATERAL DERIVED: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; + +--echo # ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; + +drop table t3, t4; + --echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index edf9ae3deff..8a985095f3c 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -310,6 +310,8 @@ struct SplM_field_ext_info: public SplM_field_info occurred also in the select list of this join 9. There are defined some keys usable for ref access of fields from C with available statistics. + 10. The select doesn't use WITH ROLLUP (This limitation can probably be + lifted) @retval true if the answer is positive @@ -326,7 +328,8 @@ bool JOIN::check_for_splittable_materialized() (unit->first_select()->next_select()) || // !(3) (derived->prohibit_cond_pushdown) || // !(4) (derived->is_recursive_with_table()) || // !(5) - (table_count == 0 || const_tables == top_join_tab_count)) // !(6) + (table_count == 0 || const_tables == top_join_tab_count) || // !(6) + rollup.state != ROLLUP::STATE_NONE) // (10) return false; if (group_list) // (7.1) {