revision-id: f33b07558e31f9aed5afb28e903feebd5cf0964f (mariadb-10.2.26-14-gf33b07558e3) parent(s): 3b234104ae227556f06c2c3d227e5fc51692c8fa author: Varun Gupta committer: Varun Gupta timestamp: 2019-08-12 01:09:56 +0530 message: MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null || pos->item != __null' failed. When aggregate functions are not added to the GROUP BY temp table, then make sure to use another temp table to create fields for aggregate functions as Window Functions temp table requires all the fields in JOIN::all_fields to be in the temp table. --- mysql-test/r/win.result | 29 +++++++++++++++++++++++++++++ mysql-test/t/win.test | 24 ++++++++++++++++++++++++ sql/sql_select.cc | 10 +++++++++- 3 files changed, 62 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 13d452f3ef2..d5d12a6473c 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3634,5 +3634,34 @@ rank() over (partition by 'abc' order by 'xyz') 1 drop table t1; # +# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null +# || pos->item != __null' failed. +# +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (1, 1),(3, 3); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(7),(4),(3); +explain SELECT a, RANK() OVER (ORDER BY SUM(i1)) +FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +SELECT a, RANK() OVER (ORDER BY SUM(i1)) +FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; +a RANK() OVER (ORDER BY SUM(i1)) +1 1 +3 2 +explain SELECT sum(distinct i1), a, RANK() OVER (ORDER BY SUM(i1)) +FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +SELECT sum(distinct i1), a, RANK() OVER (ORDER BY SUM(i1)) +FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; +sum(distinct i1) a RANK() OVER (ORDER BY SUM(i1)) +1 1 1 +3 3 2 +DROP TABLE t1, t2; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index a4d42ce2b91..a56fc6c60a1 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2341,6 +2341,30 @@ select rank() over (partition by 'abc' order by 'xyz') from t1; select rank() over (partition by 'abc' order by 'xyz') from t1; drop table t1; +--echo # +--echo # MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null +--echo # || pos->item != __null' failed. +--echo # + +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (1, 1),(3, 3); + +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(7),(4),(3); + +let $query= SELECT a, RANK() OVER (ORDER BY SUM(i1)) + FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; + +eval explain $query; +eval $query; + +let $query= SELECT sum(distinct i1), a, RANK() OVER (ORDER BY SUM(i1)) + FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; + +eval explain $query; +eval $query; +DROP TABLE t1, t2; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index bdab9cf76cf..818d73f68c8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2651,10 +2651,18 @@ bool JOIN::make_aggr_tables_info() This code is also used if we are using distinct something we haven't been able to store in the temporary table yet like SEC_TO_TIME(SUM(...)). + + Also for the case when there is a window function and an aggregate + function and the aggregate function were not added to the temporary + table of GROUP BY, then we need to create a temp table for such items + as Window Function needs the list of all the items inside the temp + table that belong to JOIN::all_fields. */ if ((group_list && (!test_if_subpart(group_list, order) || select_distinct)) || - (select_distinct && tmp_table_param.using_outer_summary_function)) + (select_distinct && tmp_table_param.using_outer_summary_function) || + (select_lex->have_window_funcs() && select_lex->agg_func_used() && + !tmp_table_param.quick_group)) { /* Must copy to another table */ DBUG_PRINT("info",("Creating group table"));