Calcite function with default argument value

Question

I am using Apache Calcite to add some built-in functions. Now, I want to implement the GROUP_CONCAT function like MySQL to concat one column with one separator.

SELECT GROUP_CONCAT(n_name, '|') FROM nation GROUP BY n_lang;

The function class as follows:

public class SqlGroupConcatFunction extends SqlAggFunction {
  public SqlGroupConcatFunction() {
    super(
        "GROUP_CONCAT",
        null,
        SqlKind.GROUP_CONCAT,
        ReturnTypes.VARCHAR_2000,
        InferTypes.FIRST_KNOWN,
        OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.STRING),
        SqlFunctionCategory.STRING,
        false,
        false);
  }
}

Now, I want this function can accept one parameter(without separator) or two parameters. When accept only one parameter, set the second parameter with default value.

I do not find any methods to set the default argument value in Calcite. Does have method to implement this feature?

score 3 · Accepted Answer · answered Nov 26 '16 at 01:34

In CALCITE-941 we added support for parameters are optional and have names. You can add the @Parameter annotation to an argument of user-defined function to specify its name and whether it is optional.

However, your use case is an aggregate function, and the @Parameter annotation does not work for these.

So, you will have to specify a SqlOperandTypeChecker argument to your constructor that allows 1 or 2 parameters. Try replacing

OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.STRING),

with

OperandTypes.or(
  OperandTypes.family(SqlTypeFamily.ANY),
  OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.STRING)),

That will satisfy the validator. At some later stage, probably at sql-to-rel conversion time, you can intercept the 1-argument calls and make them into 2-argument calls.

Does this updated recently? Can we use group_concat directly in SQL? — Jary zhen, Jan 09 '19 at 13:35

Calcite function with default argument value

1 Answers1