Declaration UDAF with multi-input params throughs PARSE_ERROR

Help Procedural SQL
1

Hi,
I what to create aggregate function with multiple input parameter, but getting ERROR 1064 ER_PARSE_ERROR

This is simple example

DELIMITER //
CREATE FUNCTION avg_init() RETURNS RECORD(s BIGINT, c BIGINT) AS
  BEGIN
    RETURN ROW(0, 0);
  END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION avg_iter(state RECORD(s BIGINT, c BIGINT), v BIGINT, b INT) RETURNS RECORD(s BIGINT, c BIGINT) AS
  BEGIN
    RETURN ROW(state.s + v + b, state.c + 1);
  END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION avg_merge(state1 RECORD(s BIGINT, c BIGINT), state2 RECORD(s BIGINT, c BIGINT)) RETURNS RECORD(s BIGINT, c BIGINT) AS
  BEGIN
    RETURN row(state1.s + state2.s, state1.c + state2.c);
  END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION avg_terminate(state RECORD(s BIGINT, c BIGINT)) RETURNS BIGINT AS
  BEGIN
    RETURN state.s / state.c;
  END //
DELIMITER ;

CREATE AGGREGATE avg_udaf(a BIGINT, b INT DEFAULT 5) RETURNS BIGINT
  WITH STATE RECORD(s BIGINT, c BIGINT)
  INITIALIZE WITH avg_init
  ITERATE WITH avg_iter
  MERGE WITH avg_merge
  TERMINATE WITH avg_terminate;

add as a result I got parse error

ERROR 1064 ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a BIGINT,b INT) RETURNS BIGINT
  WITH STATE RECORD(s BIGINT, c BIGINT)
  INITIAL' at line 1

I can create aggregate function by specifying data type only

CREATE AGGREGATE avg_udaf(BIGINT, INT) RETURNS BIGINT
  WITH STATE RECORD(s BIGINT, c BIGINT)
  INITIALIZE WITH avg_init
  ITERATE WITH avg_iter
  MERGE WITH avg_merge
  TERMINATE WITH avg_terminate;

and this works fine, but this way I can’t specify modifiers like

CREATE AGGREGATE avg_udaf(BIGINT, INT DEFAULT 5) RETURNS BIGINT

Appreciate any help.
Thanks in advance,

Alex

MemSQL Version: 7.0.16

2

DEFAULT is not supported for UDAF arguments. I added a feature request to track it.

1 Like