revision-id: fc34657511a9aa08dd92f7363dc53f58934f9673 (mariadb-10.2.29-62-gfc34657511a) parent(s): f0aa073f2bf3d8d85b3d028df89cdb4cdfc4002d author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-17 16:40:06 +0530 message: MDEV-21318: Wrong results with window functions and implicit grouping The issue here is for degenerate joins we should execute the window function but it is not getting executed in all the cases. To get the window function values window function needs to be executed always. This currently does not happen in few cases where the join would return 0 or 1 row like 1) IMPOSSIBLE WHERE 2) IMPOSSIBLE HAVING 3) MIN/MAX optimization 4) EMPTY CONST TABLE 5) ZERO LIMIT The fix is to make sure that window functions get executed and the temporary table is setup for the execution of window functions --- mysql-test/r/win.result | 51 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/win.test | 56 +++++++++++++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 33 ++++++++++++++++++++++++++++- sql/sql_select.h | 1 + 4 files changed, 140 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 805fd2ed3d7..de6e0e5afbb 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3643,5 +3643,56 @@ x foo drop table t1; # +# MDEV-21318: Wrong results with window functions and implicit grouping +# +CREATE TABLE t1 (a INT); +# +# With empty table +# The expected result here is 1, NULL +# +SELECT row_number() over(), sum(1) FROM t1 where a=1; +row_number() over() sum(1) +1 NULL +insert into t1 values (2); +# +# Const table has 1 row, but still impossible where +# The expected result here is 1, NULL +# +SELECT row_number() over(), sum(1) FROM t1 where a=1; +row_number() over() sum(1) +1 NULL +# +# Impossible HAVING +# Empty result is expected +# +SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0; +row_number() over() sum(1) +# +# const table has 1 row, no impossible where +# The expected result here is 1, 2 +# +SELECT row_number() over(), sum(a) FROM t1 where a=2; +row_number() over() sum(a) +1 2 +drop table t1; +# +# Impossible Where +# +create table t1(a int); +insert into t1 values (1); +# +# Expected result is NULL, 0, NULL +# +SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; +MAX(a) OVER () COUNT(a) abs(a) +NULL 0 NULL +# +# Expected result is 1, 0, NULL +# +SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; +MAX(1) OVER () COUNT(a) abs(a) +1 0 NULL +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 0f79834567b..9bc867cea7f 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2351,6 +2351,62 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x; drop table t1; +--echo # +--echo # MDEV-21318: Wrong results with window functions and implicit grouping +--echo # + +CREATE TABLE t1 (a INT); + +--echo # +--echo # With empty table +--echo # The expected result here is 1, NULL +--echo # + +SELECT row_number() over(), sum(1) FROM t1 where a=1; +insert into t1 values (2); + +--echo # +--echo # Const table has 1 row, but still impossible where +--echo # The expected result here is 1, NULL +--echo # + +SELECT row_number() over(), sum(1) FROM t1 where a=1; + +--echo # +--echo # Impossible HAVING +--echo # Empty result is expected +--echo # + +SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0; + +--echo # +--echo # const table has 1 row, no impossible where +--echo # The expected result here is 1, 2 +--echo # + +SELECT row_number() over(), sum(a) FROM t1 where a=2; +drop table t1; + +--echo # +--echo # Impossible Where +--echo # + +create table t1(a int); +insert into t1 values (1); + +--echo # +--echo # Expected result is NULL, 0, NULL +--echo # +SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; + +--echo # +--echo # Expected result is 1, 0, NULL +--echo # + +SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; + +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c9cb533aa33..f6943b18cee 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1447,6 +1447,7 @@ JOIN::optimize_inner() zero_result_cause= "Zero limit"; } table_count= top_join_tab_count= 0; + implicit_grouping_with_window_funcs(); error= 0; goto setup_subq_exit; } @@ -1502,6 +1503,7 @@ JOIN::optimize_inner() zero_result_cause= "No matching min/max row"; table_count= top_join_tab_count= 0; error=0; + implicit_grouping_with_window_funcs(); goto setup_subq_exit; } if (res > 1) @@ -1517,6 +1519,7 @@ JOIN::optimize_inner() tables_list= 0; // All tables resolved select_lex->min_max_opt_list.empty(); const_tables= top_join_tab_count= table_count; + implicit_grouping_with_window_funcs(); /* Extract all table-independent conditions and replace the WHERE clause with them. All other conditions were computed by opt_sum_query @@ -1615,6 +1618,7 @@ JOIN::optimize_inner() zero_result_cause= "no matching row in const table"; DBUG_PRINT("error",("Error: %s", zero_result_cause)); error= 0; + implicit_grouping_with_window_funcs(); goto setup_subq_exit; } if (!(thd->variables.option_bits & OPTION_BIG_SELECTS) && @@ -1639,6 +1643,7 @@ JOIN::optimize_inner() zero_result_cause= "Impossible WHERE noticed after reading const tables"; select_lex->mark_const_derived(zero_result_cause); + implicit_grouping_with_window_funcs(); goto setup_subq_exit; } @@ -1781,6 +1786,7 @@ JOIN::optimize_inner() zero_result_cause= "Impossible WHERE noticed after reading const tables"; select_lex->mark_const_derived(zero_result_cause); + implicit_grouping_with_window_funcs(); goto setup_subq_exit; } @@ -18225,7 +18231,8 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab) } } else if (join->sort_and_group && !tmp_tbl->precomputed_group_by && - !join->sort_and_group_aggr_tab && join->tables_list) + !join->sort_and_group_aggr_tab && join->tables_list && + join->top_join_tab_count) { DBUG_PRINT("info",("Using end_write_group")); aggr->set_write_func(end_write_group); @@ -26924,6 +26931,30 @@ Item *remove_pushed_top_conjuncts(THD *thd, Item *cond) return cond; } +/* + There are 5 cases in which we shortcut the join optimization process as we + conclude that the join would be a degenerate one + 1) IMPOSSIBLE WHERE + 2) IMPOSSIBLE HAVING + 3) MIN/MAX optimization (@see opt_sum_query) + 4) EMPTY CONST TABLE + 5) ZERO LIMIT + If a window function is present in any of the above cases then to get the + result of the window function, we need to execute it. So we need to + create a temporary table for its execution. Here we need to take in mind + that aggregate functions and non-aggregate function need not be executed. + +*/ + + +void JOIN::implicit_grouping_with_window_funcs() +{ + if (select_lex->have_window_funcs() && send_row_on_empty_set()) + { + const_tables= top_join_tab_count= table_count= 0; + } +} + /** @} (end of group Query_Optimizer) */ diff --git a/sql/sql_select.h b/sql/sql_select.h index fe44f448446..74e8ef4698b 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1057,6 +1057,7 @@ class JOIN :public Sql_alloc void restore_query_plan(Join_plan_state *restore_from); /* Choose a subquery plan for a table-less subquery. */ bool choose_tableless_subquery_plan(); + void implicit_grouping_with_window_funcs(); public: JOIN_TAB *join_tab, **best_ref;