MDEV-14592: Custom Aggregates Usage Status Variable10.3-MDEV-14592

Introduced new status variable for custom aggregate functions

8 files changed, 179 insertions, 0 deletions

diff --git a/mysql-test/r/custom_aggregates_i_s.result b/mysql-test/r/custom_aggregates_i_s.result
new file mode 100644
index 00000000000..a0d6032a139
--- /dev/null
+++ b/mysql-test/r/custom_aggregates_i_s.result
@@ -0,0 +1,94 @@
+show global status like "%custom_aggregate%";
+Variable_name	Value
+Feature_custom_aggregate_functions	0
+create table t2 (sal int(10));
+create table t3 (sal int(10),id int);
+insert into t3 values (0,1),(1,2),(2,3),(3,4);
+create aggregate function f1(x INT) returns int
+begin
+declare tot_sum int default 0;
+declare continue handler for not found return tot_sum;
+loop
+fetch group next row;
+set tot_sum= tot_sum + x;
+end loop;
+end|
+create aggregate function f2 (x int) returns int
+begin
+declare counter int default 0;
+declare continue handler for not found return 0;
+loop
+fetch group next row;
+set counter =counter + (select f1(sal) from t1); 
+end loop;
+end|
+create table t1 (sal int(10),id int(10));
+INSERT INTO t1 (sal,id) VALUES (5000,1);
+INSERT INTO t1 (sal,id) VALUES (2000,2);
+INSERT INTO t1 (sal,id) VALUES (1000,3);
+Normal select with custom aggregate function
+select f1(sal) from t1 where id>= 1;
+f1(sal)
+8000
+show global status like "%custom_aggregate%";
+Variable_name	Value
+Feature_custom_aggregate_functions	1
+show global status like "*custom_aggregate*";
+Variable_name	Value
+subqueries with custom aggregates
+explain
+select * from t1, (select f1(sal) as a  from t1 where id>= 1) q where q.a=t1.sal;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.sal	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+show global status like "%custom_aggregate%";
+Variable_name	Value
+Feature_custom_aggregate_functions	2
+explain
+select * from t1, (select sal as a from t1 where (select f1(t3.sal) from t3) >=-1 ) q where q.a=t1.sal;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	
+show global status like "*custom_aggregate*";
+Variable_name	Value
+explain
+select (select f1(sal) as a from t3 where t3.id= t1.id )  from t1 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	Using where
+show global status like "%custom_aggregate%";
+Variable_name	Value
+Feature_custom_aggregate_functions	4
+explain
+select (select f1(sal) as a from t3 where t3.id= t1.id )  from t1 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	Using where
+show global status like "%custom_aggregate%";
+Variable_name	Value
+Feature_custom_aggregate_functions	5
+custom aggregates inside other customm aggregates
+explain
+select f2(sal) from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+show global status like "%custom_aggregate%";
+Variable_name	Value
+Feature_custom_aggregate_functions	6
+cte with custom aggregates
+with agg_sum as (
+select f1(sal) from t1 where t1.id >=1 group by t1.id
+)
+select * from agg_sum;
+f1(sal)
+5000
+2000
+1000
+show global status like "%custom_aggregate%";
+Variable_name	Value
+Feature_custom_aggregate_functions	7
+drop table t2,t1,t3;
+drop function f1;
+drop function f2;
diff --git a/mysql-test/t/custom_aggregates_i_s.test b/mysql-test/t/custom_aggregates_i_s.test
new file mode 100644
index 00000000000..154b0101a22
--- /dev/null
+++ b/mysql-test/t/custom_aggregates_i_s.test
@@ -0,0 +1,73 @@
+show global status like "%custom_aggregate%";
+create table t2 (sal int(10));
+create table t3 (sal int(10),id int);
+insert into t3 values (0,1),(1,2),(2,3),(3,4);
+delimiter |;
+
+create aggregate function f1(x INT) returns int
+begin
+  declare tot_sum int default 0;
+  declare continue handler for not found return tot_sum;
+  loop
+    fetch group next row;
+    set tot_sum= tot_sum + x;
+  end loop;
+end|
+
+create aggregate function f2 (x int) returns int
+begin
+  declare counter int default 0;
+  declare continue handler for not found return 0;
+  loop
+    fetch group next row;
+    set counter =counter + (select f1(sal) from t1); 
+  end loop;
+end|
+
+delimiter ;|
+
+create table t1 (sal int(10),id int(10));
+INSERT INTO t1 (sal,id) VALUES (5000,1);
+INSERT INTO t1 (sal,id) VALUES (2000,2);
+INSERT INTO t1 (sal,id) VALUES (1000,3);
+
+--echo Normal select with custom aggregate function
+select f1(sal) from t1 where id>= 1;
+show global status like "%custom_aggregate%";
+show global status like "*custom_aggregate*";
+
+
+--echo subqueries with custom aggregates
+explain
+select * from t1, (select f1(sal) as a  from t1 where id>= 1) q where q.a=t1.sal;
+show global status like "%custom_aggregate%";
+
+explain
+select * from t1, (select sal as a from t1 where (select f1(t3.sal) from t3) >=-1 ) q where q.a=t1.sal;
+show global status like "*custom_aggregate*";
+
+explain
+select (select f1(sal) as a from t3 where t3.id= t1.id )  from t1 ;
+show global status like "%custom_aggregate%";
+
+explain
+select (select f1(sal) as a from t3 where t3.id= t1.id )  from t1 ;
+show global status like "%custom_aggregate%";
+
+--echo custom aggregates inside other customm aggregates
+
+explain
+select f2(sal) from t1;
+show global status like "%custom_aggregate%";
+
+--echo cte with custom aggregates
+
+with agg_sum as (
+  select f1(sal) from t1 where t1.id >=1 group by t1.id
+)
+select * from agg_sum;
+show global status like "%custom_aggregate%";
+
+drop table t2,t1,t3;
+drop function f1;
+drop function f2;
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 25a0f68f575..50bba033e1c 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -313,6 +313,8 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
     }
   }
   aggr_sel->set_agg_func_used(true);
+  if (sum_func() == SP_AGGREGATE_FUNC)
+    aggr_sel->set_custom_agg_func_used(true);
   update_used_tables();
   thd->lex->in_sum_func= in_sum_func;
   return FALSE;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 60ad41eed39..1c48d628e35 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -8528,6 +8528,7 @@ SHOW_VAR status_vars[]= {
   {"Executed_events",          (char*) &executed_events, SHOW_LONG_NOFLUSH },
   {"Executed_triggers",        (char*) offsetof(STATUS_VAR, executed_triggers), SHOW_LONG_STATUS},
   {"Feature_check_constraint", (char*) &feature_check_constraint, SHOW_LONG },
+  {"Feature_custom_aggregate_functions", (char*) offsetof(STATUS_VAR, feature_custom_aggregate_functions), SHOW_LONG_STATUS},
   {"Feature_delay_key_write",  (char*) &feature_files_opened_with_delayed_keys, SHOW_LONG },
   {"Feature_dynamic_columns",  (char*) offsetof(STATUS_VAR, feature_dynamic_columns), SHOW_LONG_STATUS},
   {"Feature_fulltext",         (char*) offsetof(STATUS_VAR, feature_fulltext), SHOW_LONG_STATUS},
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 79370510e67..aec2f8c9458 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -814,6 +814,8 @@ typedef struct system_status_var
   ulong filesort_pq_sorts_;
 
   /* Features used */
+  ulong feature_custom_aggregate_functions; /* +1 when custom aggregate
+                                            functions are used */
   ulong feature_dynamic_columns;    /* +1 when creating a dynamic column */
   ulong feature_fulltext;	    /* +1 when MATCH is used */
   ulong feature_gis;                /* +1 opening a table with GIS features */
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index dcf0b7ad41b..57e92eacbf1 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2266,6 +2266,7 @@ void st_select_lex::init_query()
   select_list_tables= 0;
   m_non_agg_field_used= false;
   m_agg_func_used= false;
+  m_custom_agg_func_used= false;
   window_specs.empty();
   window_funcs.empty();
   tvc= 0;
@@ -2305,6 +2306,7 @@ void st_select_lex::init_select()
   merged_into= 0;
   m_non_agg_field_used= false;
   m_agg_func_used= false;
+  m_custom_agg_func_used= false;
   name_visibility_map= 0;
   with_dep= 0;
   join= 0;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 9856ca12324..0b1a93e8d02 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1221,9 +1221,11 @@ public:
   */
   bool non_agg_field_used() const { return m_non_agg_field_used; }
   bool agg_func_used()      const { return m_agg_func_used; }
+  bool custom_agg_func_used() const { return m_custom_agg_func_used; }
 
   void set_non_agg_field_used(bool val) { m_non_agg_field_used= val; }
   void set_agg_func_used(bool val)      { m_agg_func_used= val; }
+  void set_custom_agg_func_used(bool val) { m_custom_agg_func_used= val; }
   inline void set_with_clause(With_clause *with_clause);
   With_clause *get_with_clause()
   {
@@ -1267,6 +1269,7 @@ public:
 private:
   bool m_non_agg_field_used;
   bool m_agg_func_used;
+  bool m_custom_agg_func_used;
 
   /* current index hint kind. used in filling up index_hints */
   enum index_hint_type current_index_hint_type;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0b706f78452..baa4f5caf12 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3255,6 +3255,8 @@ bool JOIN::make_aggr_tables_info()
     /* Count that we're using window functions. */
     status_var_increment(thd->status_var.feature_window_functions);
   }
+  if (select_lex->custom_agg_func_used())
+    status_var_increment(thd->status_var.feature_custom_aggregate_functions);
 
   fields= curr_fields_list;
   // Reset before execution