summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/win.result30
-rw-r--r--mysql-test/t/win.test21
-rw-r--r--sql/opt_sum.cc2
-rw-r--r--sql/sql_select.cc59
-rw-r--r--sql/sql_select.h2
-rw-r--r--sql/sql_window.cc7
6 files changed, 90 insertions, 31 deletions
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 26194bc5990..ec83998aa98 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -3055,3 +3055,33 @@ name row_cnt pct_of_total
Fred 2 66.67
Joe 1 33.33
drop table t1;
+#
+# MDEV-11990: window function over min/max aggregation
+#
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2), (4), (2);
+select sum(max(id)) over (order by max(id)) from t1;
+sum(max(id)) over (order by max(id))
+4
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary
+create index idx on t1(id);
+select sum(max(id)) over (order by max(id)) from t1;
+sum(max(id)) over (order by max(id))
+4
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+select sum(max(id)) over (order by max(id)) from t1 where id < 3;
+sum(max(id)) over (order by max(id))
+2
+select count(max(id)) over (order by max(id)) from t1 where id < 3;
+count(max(id)) over (order by max(id))
+1
+select max(id), rank() over (order by max(id)) from t1 where id < 3;
+max(id) rank() over (order by max(id))
+2 1
+drop table t1;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 82c1dadf5ad..95d32c5bd14 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1856,3 +1856,24 @@ from
) q;
drop table t1;
+
+--echo #
+--echo # MDEV-11990: window function over min/max aggregation
+--echo #
+
+create table t1 (id int);
+insert into t1 values (1), (2), (3), (2), (4), (2);
+
+select sum(max(id)) over (order by max(id)) from t1;
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+
+create index idx on t1(id);
+select sum(max(id)) over (order by max(id)) from t1;
+explain
+select sum(max(id)) over (order by max(id)) from t1;
+select sum(max(id)) over (order by max(id)) from t1 where id < 3;
+select count(max(id)) over (order by max(id)) from t1 where id < 3;
+select max(id), rank() over (order by max(id)) from t1 where id < 3;
+
+drop table t1;
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index e7bf4658d5c..9bc21ab3ac3 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -461,7 +461,7 @@ int opt_sum_query(THD *thd,
{
if (recalc_const_item)
item->update_used_tables();
- if (!item->const_item())
+ if (!item->const_item() && item->type() != Item::WINDOW_FUNC_ITEM)
const_result= 0;
}
}
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index fbddab20908..a685dd73956 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1092,7 +1092,7 @@ int JOIN::optimize()
!skip_sort_order && !no_order && (order || group_list),
select_distinct);
uint select_nr= select_lex->select_number;
- JOIN_TAB *curr_tab= join_tab + top_join_tab_count;
+ JOIN_TAB *curr_tab= join_tab + exec_join_tab_cnt();
for (uint i= 0; i < aggr_tables; i++, curr_tab++)
{
if (select_nr == INT_MAX)
@@ -1446,7 +1446,8 @@ JOIN::optimize_inner()
}
DBUG_PRINT("info",("Select tables optimized away"));
- zero_result_cause= "Select tables optimized away";
+ if (!select_lex->have_window_funcs())
+ zero_result_cause= "Select tables optimized away";
tables_list= 0; // All tables resolved
const_tables= top_join_tab_count= table_count;
/*
@@ -2232,13 +2233,14 @@ bool JOIN::make_aggr_tables_info()
bool implicit_grouping_with_window_funcs= implicit_grouping &&
select_lex->have_window_funcs();
-
+ bool implicit_grouping_without_tables= implicit_grouping &&
+ !tables_list;
/*
Setup last table to provide fields and all_fields lists to the next
node in the plan.
*/
- if (join_tab && top_join_tab_count)
+ if (join_tab && top_join_tab_count && tables_list)
{
join_tab[top_join_tab_count - 1].fields= &fields_list;
join_tab[top_join_tab_count - 1].all_fields= &all_fields;
@@ -2284,7 +2286,7 @@ bool JOIN::make_aggr_tables_info()
order= query.order_by;
aggr_tables++;
- curr_tab= join_tab + top_join_tab_count;
+ curr_tab= join_tab + exec_join_tab_cnt();
bzero(curr_tab, sizeof(JOIN_TAB));
curr_tab->ref.key= -1;
curr_tab->join= this;
@@ -2362,7 +2364,7 @@ bool JOIN::make_aggr_tables_info()
single table queries, thus it is sufficient to test only the first
join_tab element of the plan for its access method.
*/
- if (join_tab && top_join_tab_count &&
+ if (join_tab && top_join_tab_count && tables_list &&
join_tab->is_using_loose_index_scan())
tmp_table_param.precomputed_group_by=
!join_tab->is_using_agg_loose_index_scan();
@@ -2372,7 +2374,7 @@ bool JOIN::make_aggr_tables_info()
if (need_tmp)
{
aggr_tables++;
- curr_tab= join_tab + top_join_tab_count;
+ curr_tab= join_tab + exec_join_tab_cnt();
bzero(curr_tab, sizeof(JOIN_TAB));
curr_tab->ref.key= -1;
if (only_const_tables())
@@ -2431,8 +2433,9 @@ bool JOIN::make_aggr_tables_info()
/* Change sum_fields reference to calculated fields in tmp_table */
items1= ref_ptr_array_slice(2);
- if (sort_and_group || curr_tab->table->group ||
- tmp_table_param.precomputed_group_by)
+ if ((sort_and_group || curr_tab->table->group ||
+ tmp_table_param.precomputed_group_by) &&
+ !implicit_grouping_without_tables)
{
if (change_to_use_tmp_fields(thd, items1,
tmp_fields_list1, tmp_all_fields1,
@@ -2772,7 +2775,7 @@ bool JOIN::make_aggr_tables_info()
- duplicate value removal
Both of these operations are done after window function computation step.
*/
- curr_tab= join_tab + top_join_tab_count + aggr_tables - 1;
+ curr_tab= join_tab + exec_join_tab_cnt() + aggr_tables - 1;
if (select_lex->window_funcs.elements)
{
curr_tab->window_funcs_step= new Window_funcs_computation;
@@ -2787,7 +2790,7 @@ bool JOIN::make_aggr_tables_info()
// Reset before execution
set_items_ref_array(items0);
if (join_tab)
- join_tab[top_join_tab_count + aggr_tables - 1].next_select=
+ join_tab[exec_join_tab_cnt() + aggr_tables - 1].next_select=
setup_end_select_func(this, NULL);
group= has_group_by;
@@ -2828,7 +2831,7 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields,
tmp_table_param.using_outer_summary_function=
tab->tmp_table_param->using_outer_summary_function;
tab->join= this;
- DBUG_ASSERT(tab > join_tab || select_lex->have_window_funcs());
+ DBUG_ASSERT(tab > tab->join->join_tab || !top_join_tab_count || !tables_list);
if (tab > join_tab)
(tab - 1)->next_select= sub_select_postjoin_aggr;
tab->aggr= new (thd->mem_root) AGGR_OP(tab);
@@ -2855,7 +2858,8 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields,
if (make_sum_func_list(all_fields, fields_list, true))
goto err;
if (prepare_sum_aggregators(sum_funcs,
- !join_tab->is_using_agg_loose_index_scan()))
+ !(tables_list &&
+ join_tab->is_using_agg_loose_index_scan())))
goto err;
if (setup_sum_funcs(thd, sum_funcs))
goto err;
@@ -3116,7 +3120,7 @@ JOIN::reinit()
if (aggr_tables)
{
- JOIN_TAB *curr_tab= join_tab + top_join_tab_count;
+ JOIN_TAB *curr_tab= join_tab + exec_join_tab_cnt();
JOIN_TAB *end_tab= curr_tab + aggr_tables;
for ( ; curr_tab < end_tab; curr_tab++)
{
@@ -3243,7 +3247,7 @@ void JOIN::save_explain_data(Explain_query *output, bool can_overwrite,
Explain_union *eu= output->get_union(nr);
explain= &eu->fake_select_lex_explain;
join_tab[0].tracker= eu->get_fake_select_lex_tracker();
- for (uint i=0 ; i < top_join_tab_count + aggr_tables; i++)
+ for (uint i=0 ; i < exec_join_tab_cnt() + aggr_tables; i++)
{
if (join_tab[i].filesort)
{
@@ -3312,7 +3316,8 @@ void JOIN::exec_inner()
if (result->prepare2())
DBUG_VOID_RETURN;
- if (!tables_list && (table_count || !select_lex->with_sum_func))
+ if (!tables_list && (table_count || !select_lex->with_sum_func) &&
+ !select_lex->have_window_funcs())
{ // Only test of functions
if (select_options & SELECT_DESCRIBE)
select_describe(this, FALSE, FALSE, FALSE,
@@ -12004,7 +12009,7 @@ void JOIN::cleanup(bool full)
w/o tables: they don't have some members initialized and
WALK_OPTIMIZATION_TABS may not work correctly for them.
*/
- if (table_count)
+ if (top_join_tab_count && tables_list)
{
for (tab= first_breadth_first_tab(); tab;
tab= next_breadth_first_tab(first_breadth_first_tab(),
@@ -12018,7 +12023,7 @@ void JOIN::cleanup(bool full)
cleaned= true;
//psergey2: added (Q: why not in the above loop?)
{
- JOIN_TAB *curr_tab= join_tab + top_join_tab_count;
+ JOIN_TAB *curr_tab= join_tab + exec_join_tab_cnt();
for (uint i= 0; i < aggr_tables; i++, curr_tab++)
{
if (curr_tab->aggr)
@@ -17832,7 +17837,7 @@ 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->sort_and_group_aggr_tab && join->tables_list)
{
DBUG_PRINT("info",("Using end_write_group"));
aggr->set_write_func(end_write_group);
@@ -17924,7 +17929,8 @@ do_select(JOIN *join, Procedure *procedure)
if (join->pushdown_query->store_data_in_temp_table)
{
- JOIN_TAB *last_tab= join->join_tab + join->table_count;
+ JOIN_TAB *last_tab= join->join_tab + join->table_count -
+ join->exec_join_tab_cnt();
last_tab->next_select= end_send;
enum_nested_loop_state state= last_tab->aggr->end_send();
@@ -17995,7 +18001,8 @@ do_select(JOIN *join, Procedure *procedure)
dbug_serve_apcs(join->thd, 1);
);
- JOIN_TAB *join_tab= join->join_tab + join->const_tables;
+ JOIN_TAB *join_tab= join->join_tab +
+ (join->tables_list ? join->const_tables : 0);
if (join->outer_ref_cond && !join->outer_ref_cond->val_int())
error= NESTED_LOOP_NO_MORE_ROWS;
else
@@ -23234,8 +23241,11 @@ change_refs_to_tmp_fields(THD *thd, Ref_ptr_array ref_pointer_array,
uint i, border= all_fields.elements - elements;
for (i= 0; (item= it++); i++)
{
- res_all_fields.push_back(new_item= item->get_tmp_table_item(thd),
- thd->mem_root);
+ if (item->type() == Item::SUM_FUNC_ITEM && item->const_item())
+ new_item= item;
+ else
+ new_item= item->get_tmp_table_item(thd);
+ res_all_fields.push_back(new_item, thd->mem_root);
ref_pointer_array[((i < border)? all_fields.elements-i-1 : i-border)]=
new_item;
}
@@ -24489,7 +24499,7 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta,
void save_agg_explain_data(JOIN *join, Explain_select *xpl_sel)
{
- JOIN_TAB *join_tab=join->join_tab + join->top_join_tab_count;
+ JOIN_TAB *join_tab=join->join_tab + join->exec_join_tab_cnt();
Explain_aggr_node *prev_node;
Explain_aggr_node *node= xpl_sel->aggr_tree;
bool is_analyze= join->thd->lex->analyze_stmt;
@@ -24580,6 +24590,7 @@ int JOIN::save_explain_data_intern(Explain_query *output,
if (select_lex->master_unit()->derived)
explain->connection_type= Explain_node::EXPLAIN_NODE_DERIVED;
+ save_agg_explain_data(this, explain);
output->add_node(explain);
}
else if (pushdown_query)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 4327646cdee..ab5dc08e044 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1492,6 +1492,8 @@ public:
/* True if the plan guarantees that it will be returned zero or one row */
bool only_const_tables() { return const_tables == table_count; }
+ /* Number of tables actually joined at the top level */
+ uint exec_join_tab_cnt() { return tables_list ? top_join_tab_count : 0; }
int prepare(TABLE_LIST *tables, uint wind_num,
COND *conds, uint og_num, ORDER *order, bool skip_order_by,
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index 557c7503306..0e407308d4e 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -2761,7 +2761,7 @@ bool Window_func_runner::exec(THD *thd, TABLE *tbl, SORT_INFO *filesort_result)
bool Window_funcs_sort::exec(JOIN *join)
{
THD *thd= join->thd;
- JOIN_TAB *join_tab= &join->join_tab[join->top_join_tab_count];
+ JOIN_TAB *join_tab= join->join_tab + join->exec_join_tab_cnt();
/* Sort the table based on the most specific sorting criteria of
the window functions. */
@@ -2841,11 +2841,6 @@ bool Window_funcs_sort::setup(THD *thd, SQL_SELECT *sel,
sort_order= order;
}
filesort= new (thd->mem_root) Filesort(sort_order, HA_POS_ERROR, true, NULL);
- if (!join_tab->join->top_join_tab_count)
- {
- filesort->tracker=
- new (thd->mem_root) Filesort_tracker(thd->lex->analyze_stmt);
- }
/* Apply the same condition that the subsequent sort has. */
filesort->select= sel;
View Lorry Controller Status