MDEV-15073: Generic UDAF parser code in server for windows functionsbb-10.4-MDEV-15073

Added support for usual agreggate UDF (UDAF)
Added remove() call support for more efficient window function processing
Added example of aggregate UDF with efficient windows function support

9 files changed, 354 insertions, 5 deletions

diff --git a/mysql-test/main/udf.result b/mysql-test/main/udf.result
index 6af6b167511..839f93fc66c 100644
--- a/mysql-test/main/udf.result
+++ b/mysql-test/main/udf.result
@@ -465,3 +465,93 @@ a	b
 Hello	HL
 DROP FUNCTION METAPHON;
 DROP TABLE t1;
+
+MDEV-15073: Generic UDAF parser code in server for windows functions
+
+CREATE AGGREGATE FUNCTION avgcost
+RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
+CREATE AGGREGATE FUNCTION avg2
+RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
+CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
+create table t1(pk int primary key,
+a int,
+sum int,
+price float(24));
+insert into t1 values
+(1, 1, 100, 50.00),
+(2, 1, 100, 100.00),
+(3, 1, 100, 50.00),
+(4, 1, 100, 50.00),
+(5, 1, 100, 50.00),
+(6, 1, 100, NULL),
+(7, 1, NULL, NULL),
+(8, 2, 2,   2),
+(9, 2, 4,   4);
+select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+from t1;
+pk	a	sum	price	avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+1	1	100	50	75.0000
+2	1	100	100	66.6667
+3	1	100	50	66.6667
+4	1	100	50	50.0000
+5	1	100	50	50.0000
+6	1	100	NULL	50.0000
+7	1	NULL	NULL	0.0000
+8	2	2	2	3.3333
+9	2	4	4	3.3333
+select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+pk	a	sum	price	avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+1	1	100	50	50.0000
+2	1	100	100	75.0000
+3	1	100	50	75.0000
+4	1	100	50	50.0000
+5	1	100	50	50.0000
+6	1	100	NULL	50.0000
+7	1	NULL	NULL	0.0000
+8	2	2	2	2.0000
+9	2	4	4	3.3333
+select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+from t1;
+pk	a	sum	price	avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+1	1	100	50	0.7500
+2	1	100	100	0.6667
+3	1	100	50	0.6667
+4	1	100	50	0.5000
+5	1	100	50	0.5000
+6	1	100	NULL	0.5000
+7	1	NULL	NULL	0.0000
+8	2	2	2	1.0000
+9	2	4	4	1.0000
+select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+pk	a	sum	price	avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+1	1	100	50	0.5000
+2	1	100	100	0.7500
+3	1	100	50	0.7500
+4	1	100	50	0.5000
+5	1	100	50	0.5000
+6	1	100	NULL	0.5000
+7	1	NULL	NULL	0.0000
+8	2	2	2	1.0000
+9	2	4	4	1.0000
+select pk, a, sum, price, tttttttt(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from ' at line 1
+select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from ' at line 1
+select pk, a, sum, price, round(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from ' at line 1
+select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from ' at line 1
+drop table t1;
+DROP FUNCTION avgcost;
+DROP FUNCTION avg2;
+DROP FUNCTION myfunc_double;
diff --git a/mysql-test/main/udf.test b/mysql-test/main/udf.test
index c3a25c6bcce..e72f8b219af 100644
--- a/mysql-test/main/udf.test
+++ b/mysql-test/main/udf.test
@@ -528,3 +528,62 @@ DROP FUNCTION METAPHON;
 #INSERT INTO t1 (a) VALUES ('Hello');
 #SELECT * FROM t1;
 DROP TABLE t1;
+
+--echo
+--echo MDEV-15073: Generic UDAF parser code in server for windows functions
+--echo
+
+--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
+eval CREATE AGGREGATE FUNCTION avgcost
+        RETURNS REAL SONAME "$UDF_EXAMPLE_SO";
+--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
+eval CREATE AGGREGATE FUNCTION avg2
+        RETURNS REAL SONAME "$UDF_EXAMPLE_SO";
+--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
+eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_SO";
+
+create table t1(pk int primary key,
+                a int,
+                sum int,
+                price float(24));
+insert into t1 values
+  (1, 1, 100, 50.00),
+  (2, 1, 100, 100.00),
+  (3, 1, 100, 50.00),
+  (4, 1, 100, 50.00),
+  (5, 1, 100, 50.00),
+  (6, 1, 100, NULL),
+  (7, 1, NULL, NULL),
+  (8, 2, 2,   2),
+  (9, 2, 4,   4);
+
+--sorted_result
+select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+from t1;
+--sorted_result
+select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+
+--sorted_result
+select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
+from t1;
+--sorted_result
+select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+--error ER_PARSE_ERROR
+select pk, a, sum, price, tttttttt(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+--error ER_PARSE_ERROR
+select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+--error ER_PARSE_ERROR
+select pk, a, sum, price, round(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+--error ER_PARSE_ERROR
+select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
+from t1;
+
+drop table t1;
+DROP FUNCTION avgcost;
+DROP FUNCTION avg2;
+DROP FUNCTION myfunc_double;
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 0e52b2988a3..d19645b5020 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -3235,6 +3235,25 @@ bool Item_udf_sum::add()
   DBUG_RETURN(0);
 }
 
+
+bool Item_udf_sum::supports_removal()
+{
+  DBUG_ENTER("Item_udf_sum::supports_remove");
+  DBUG_PRINT("info", ("support: %d", udf.supports_removal()));
+  DBUG_RETURN(udf.supports_removal());
+}
+
+
+void Item_udf_sum::remove()
+{
+  my_bool tmp_null_value;
+  DBUG_ENTER("Item_udf_sum::remove");
+  udf.remove(&tmp_null_value);
+  null_value= tmp_null_value;
+  DBUG_VOID_RETURN;
+}
+
+
 void Item_udf_sum::cleanup()
 {
   /*
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 1a21c257221..663dea1ed7b 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -574,7 +574,7 @@ public:
   virtual bool add()= 0;
   virtual bool setup(THD *thd) { return false; }
 
-  virtual bool supports_removal() const { return false; }
+  virtual bool supports_removal() { return false; }
   virtual void remove() { DBUG_ASSERT(0); }
 
   virtual void cleanup();
@@ -817,7 +817,7 @@ public:
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_sum_sum>(thd, this); }
 
-  bool supports_removal() const
+  bool supports_removal()
   {
     return true;
   }
@@ -888,7 +888,7 @@ public:
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_sum_count>(thd, this); }
 
-  bool supports_removal() const
+  bool supports_removal()
   {
     return true;
   }
@@ -944,7 +944,7 @@ public:
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_sum_avg>(thd, this); }
 
-  bool supports_removal() const
+  bool supports_removal()
   {
     return true;
   }
@@ -1169,7 +1169,7 @@ public:
     DBUG_ASSERT(0);
   }
 
-  bool supports_removal() const
+  bool supports_removal()
   {
     return true;
   }
@@ -1561,6 +1561,8 @@ public:
 
   void clear();
   bool add();
+  bool supports_removal();
+  void remove();
   void reset_field() {};
   void update_field() {};
   void cleanup();
diff --git a/sql/sql_udf.cc b/sql/sql_udf.cc
index 9a036156de6..aee4869bd40 100644
--- a/sql/sql_udf.cc
+++ b/sql/sql_udf.cc
@@ -76,6 +76,8 @@ static const char *init_syms(udf_func *tmp, char *nm)
     (void)strmov(end, "_add");
     if (!((tmp->func_add= (Udf_func_add) dlsym(tmp->dlhandle, nm))))
       return nm;
+    (void)strmov(end, "_remove");
+    tmp->func_remove= (Udf_func_add) dlsym(tmp->dlhandle, nm);
   }
 
   (void) strmov(end,"_deinit");
@@ -565,6 +567,7 @@ int mysql_create_function(THD *thd,udf_func *udf)
   u_d->func_deinit= udf->func_deinit;
   u_d->func_clear= udf->func_clear;
   u_d->func_add= udf->func_add;
+  u_d->func_remove= udf->func_remove;
 
   /* create entry in mysql.func table */
 
diff --git a/sql/sql_udf.h b/sql/sql_udf.h
index 6e6fed2a81a..d1f2ae7bfb0 100644
--- a/sql/sql_udf.h
+++ b/sql/sql_udf.h
@@ -47,6 +47,7 @@ typedef struct st_udf_func
   Udf_func_deinit func_deinit;
   Udf_func_clear func_clear;
   Udf_func_add func_add;
+  Udf_func_add func_remove;
   ulong usage_count;
 } udf_func;
 
@@ -131,6 +132,20 @@ class udf_handler :public Sql_alloc
     func(&initid, &f_args, &is_null, &error);
     *null_value= (my_bool) (is_null || error);
   }
+  bool supports_removal()
+  { return MY_TEST(u_d->func_remove); }
+  void remove(my_bool *null_value)
+  {
+    DBUG_ASSERT(u_d->func_remove);
+    if (get_arguments())
+    {
+      *null_value=1;
+      return;
+    }
+    Udf_func_add func= u_d->func_remove;
+    func(&initid, &f_args, &is_null, &error);
+    *null_value= (my_bool) (is_null || error);
+  }
   String *val_str(String *str,String *save_str);
 };
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 3a2c9bb67d6..6c4528bba4c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -11159,6 +11159,21 @@ window_func:
           {
             ((Item_sum *) $1)->mark_as_window_func_sum_expr();
           }
+        |
+          function_call_generic
+          {
+            Item* item = (Item*)$1;
+            /* Only UDF aggregate here possible */
+            if ((item == NULL) ||
+                (item->type() != Item::SUM_FUNC_ITEM)
+                || (((Item_sum *)item)->sum_func() != Item_sum::UDF_SUM_FUNC))
+            {
+              thd->parse_error();
+              MYSQL_YYABORT;
+            }
+
+            ((Item_sum *) $1)->mark_as_window_func_sum_expr();
+          }
         ;
 
 simple_window_func:
diff --git a/sql/udf_example.c b/sql/udf_example.c
index 6db2b5e737a..bdc995b51fc 100644
--- a/sql/udf_example.c
+++ b/sql/udf_example.c
@@ -173,6 +173,13 @@ void avgcost_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error
 void avgcost_clear( UDF_INIT* initid, char* is_null, char *error );
 void avgcost_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
 double avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
+my_bool avg2_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
+void avg2_deinit( UDF_INIT* initid );
+void avg2_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
+void avg2_clear( UDF_INIT* initid, char* is_null, char *error );
+void avg2_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
+void avg2_remove( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
+double avg2( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
 my_bool is_const_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
 char *is_const(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long
                *length, char *is_null, char *error);
@@ -1049,6 +1056,138 @@ avgcost( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)),
   return data->totalprice/(double)data->totalquantity;
 }
 
+
+/*
+** Average 2 (number, sum)*/
+struct avg2_data
+{
+  ulonglong	count;
+  double	sum;
+};
+
+
+my_bool
+avg2_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
+{
+  struct avg2_data*	data;
+
+  if (args->arg_count != 2)
+  {
+    strcpy(
+	   message,
+	   "wrong number of arguments: AVG2() requires two arguments"
+	   );
+    return 1;
+  }
+
+  if ((args->arg_type[0] != INT_RESULT) || (args->arg_type[1] != REAL_RESULT) )
+  {
+    strcpy(
+	   message,
+	   "wrong argument type: AVG2() requires an INT and a REAL"
+	   );
+    return 1;
+  }
+
+  /*
+  **	force arguments to double.
+  */
+  /*args->arg_type[0]	= REAL_RESULT;
+    args->arg_type[1]	= REAL_RESULT;*/
+
+  initid->maybe_null	= 0;		/* The result may be null */
+  initid->decimals	= 4;		/* We want 4 decimals in the result */
+  initid->max_length	= 20;		/* 6 digits + . + 10 decimals */
+
+  if (!(data = (struct avg2_data*) malloc(sizeof(struct avg2_data))))
+  {
+    strmov(message,"Couldn't allocate memory");
+    return 1;
+  }
+  data->count	= 0;
+  data->sum	= 0.0;
+
+  initid->ptr = (char*)data;
+
+  return 0;
+}
+
+void
+avg2_deinit( UDF_INIT* initid )
+{
+  free(initid->ptr);
+}
+
+
+/* This is only for MySQL 4.0 compability */
+void
+avg2_reset(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message)
+{
+  avgcost_clear(initid, is_null, message);
+  avgcost_add(initid, args, is_null, message);
+}
+
+/* This is needed to get things to work in MySQL 4.1.1 and above */
+
+void
+avg2_clear(UDF_INIT* initid, char* is_null __attribute__((unused)),
+              char* message __attribute__((unused)))
+{
+  struct avg2_data* data = (struct avg2_data*)initid->ptr;
+  data->sum=	0.0;
+  data->count=	0;
+}
+
+
+void
+avg2_add(UDF_INIT* initid, UDF_ARGS* args,
+            char* is_null __attribute__((unused)),
+            char* message __attribute__((unused)))
+{
+  if (args->args[0] && args->args[1])
+  {
+    struct avg2_data* data	= (struct avg2_data*)initid->ptr;
+    longlong quantity		= *((longlong*)args->args[0]);
+    double sum	                = *((double*)args->args[1]);
+
+    data->count	+= quantity;
+    data->sum	+= sum;
+  }
+}
+
+
+void
+avg2_remove(UDF_INIT* initid, UDF_ARGS* args,
+               char* is_null __attribute__((unused)),
+               char* message __attribute__((unused)))
+{
+  if (args->args[0] && args->args[1])
+  {
+    struct avg2_data* data	= (struct avg2_data*)initid->ptr;
+    longlong quantity		= *((longlong*)args->args[0]);
+    double sum	                = *((double*)args->args[1]);
+
+    data->count	-= quantity;
+    data->sum	-= sum;
+  }
+}
+
+
+double
+avg2( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)),
+         char* is_null, char* error __attribute__((unused)))
+{
+  struct avg2_data* data = (struct avg2_data*)initid->ptr;
+  if (!data->count)
+  {
+    *is_null = 1;
+    return 0.0;
+  }
+
+  *is_null = 0;
+  return data->sum/(double)data->count;
+}
+
 my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args,
 				  char *message);
 char *myfunc_argument_name(UDF_INIT *initid, UDF_ARGS *args, char *result,
diff --git a/sql/udf_example.def b/sql/udf_example.def
index 74230b638bf..903c2b74893 100644
--- a/sql/udf_example.def
+++ b/sql/udf_example.def
@@ -23,6 +23,13 @@ EXPORTS
   avgcost_add
   avgcost_clear
   avgcost
+  avg2_init
+  avg2_deinit
+  avg2_reset
+  avg2_add
+  avg2_remove
+  avg2_clear
+  avg2
   is_const
   is_const_init
   check_const_len