Adding a User-Defined Function to Calcite

Question

I need to add a user-defined function to Calcite that takes an integer as a parameter and returns an integer.

    public class SquareFunction  {
        public int eval(int a) {
            return a*a;
        }
    }

and the relevant code that creates a schema and adds the function is

     SchemaPlus rootSchema = Frameworks.createRootSchema(false);
     rootSchema.add("SQUARE_FUNC", 
                    ScalarFunctionImpl.create(SquareFunction.class,"eval");

But a simple SQL like

select SQUARE_FUNC(1) from test;

fails during the validation with the following message:

No match found for function signature SQUARE_FUNC(<NUMERIC>)

The stack trace is:

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:804)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:789)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4386)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1670)
    at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:278)
    at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4965)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:1)
    at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1586)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1571)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:453)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3668)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3186)
    at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
    at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:937)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:918)
    at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:220)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:893)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:603)
    at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:188) ... 26 more

I followed the Calcite's UdfTest.testUserDefinedFunctionInView implementation but still couldn't make it work. What am I doing wrong?

I'm facing the same problem. Were you able to figure out the solution? — Kishore, Jun 08 '17 at 21:56
Are you adding a schema to rootschema, like a sub-schema? Something like rootSchema.add("my-schema", new Subschema()) — Kishore, Jun 11 '17 at 02:49
@MichaelAlexeev, if you are still working on this, would you mind trying double in place of int. May be it is not able to do a cast. — Arun A K, Mar 08 '18 at 19:43

score 2 · Answer 1 · answered Jul 07 '17 at 03:38

How does Calcite validates that a function exists?

As I known, SqlOperatorTable defines a directory interface for enumerating and looking up SQL operators and functions, and lookupOperatorOverloads retrieves a list of operators with a given name and syntax.

For the Frameworks, the default SqlOperatorTable of FrameworkConfig is SqlStdOperatorTable.instance(). But the function you added only searched at CalciteCatalogReader. So following code can work:

   public class UdfTest {
    private static final String SQL      = "select SQUARE_FUNC(1)";
    private static final String FUN_NAME = "SQUARE_FUNC";

    public static void main(String[] args) throws Exception {
        useFramworksExec();
    }

    private static void useFramworksExec() throws Exception {
        CalciteSchema rootSchema = CalciteSchema.createRootSchema(false, false);
        SchemaPlus schema = rootSchema.plus();
        schema.add(FUN_NAME, ScalarFunctionImpl.create(SquareFunction.class, "eval"));
        CalciteCatalogReader reader = new CalciteCatalogReader(rootSchema,
            SqlParser.Config.DEFAULT.caseSensitive(),
            rootSchema.path(null),
            new JavaTypeFactoryImpl());
        FrameworkConfig config = Frameworks.newConfigBuilder().operatorTable(reader).defaultSchema(schema).build();
        Planner planner = Frameworks.getPlanner(config);
        SqlNode ast = planner.parse(SQL);
        SqlNode validatedAst = planner.validate(ast);
        System.out.println(validatedAst.toString());
    }
}

CalciteCatalogReader constructor is public CalciteCatalogReader(CalciteSchema rootSchema, List defaultSchema, RelDataTypeFactory typeFactory, CalciteConnectionConfig config) . This solution does not compiles. — Nischal Kumar, May 01 '19 at 13:52

score 1 · Answer 2 · edited May 25 '17 at 01:47

1

Is SquareFunction an inner class? If so, try making it static.

If that doesn't work, try making eval a static method.

edited May 25 '17 at 01:47

Michael Mior

28,107
9
89
113

answered May 24 '17 at 18:07

Julian Hyde

1,239
7
10

2

The SquareFunction is a regular class. Making it pubic inner static (and making the “eval” method static as well) didn’t help. While debugging the application I can see that the ScalarFunctionImpl.create call returns a valid ScalarFunctionImpl object which is successfully added to the schema. But when I dumped the content of the SqlStdOperatorTable.operators multimap from the SqlStdOperatorTable.lookupOperatorOverloads method the SQUARE_FUNC name wasn’t there and the routput operatorList is empty. – Michael Alexeev May 25 '17 at 00:22
1

Another observation is that even if the function were there it would be filtered out by the SqlUtil.lookupSubjectRoutinesByName method. The filtering predicate only allows the SqlFunction type but ScalarFunctionImpl does not extend it as far as I can tell – Michael Alexeev May 25 '17 at 00:27
Maybe the statement is being prepared (parsed and validated) in a context that uses a different root schema, one that doesn't contain the function. – Julian Hyde May 26 '17 at 18:13
No, I don't think this is the case here. The defaultSchema object that associated with the PlannerImpl that does the parsing and the validation is the same schema that has UDF added – Michael Alexeev May 30 '17 at 22:38
I run the Calcite's UdfTest.testUserDefinedFunctionInView unit test (version 1.13.0-SNAPSHOT) and got exactly the same error: No match found for function signature MY_INCREMENT(, ). According to the CALCITE-937 this functionality is there since the 1.5.0 release. Should I try a different version? – Michael Alexeev Jun 03 '17 at 00:49
1

I could get it working with public double eval(double a) { return a*a; } With int, it was failing at argType != null && !SqlTypeUtil.canCastFrom(paramType, argType, false) – Arun A K Mar 05 '18 at 20:09

score 1 · Answer 3 · answered May 01 '19 at 14:20

As CalciteCatalogReader object requires context, There is another way to register custom Functions.

SqlFunction countRelation = new SqlFunction("COUNT_RELATION",
                SqlKind.OTHER_FUNCTION,
                ReturnTypes.INTEGER,
                null,
                OperandTypes.STRING,
                SqlFunctionCategory.NUMERIC);

        SqlStdOperatorTable sqlStdOperatorTable = SqlStdOperatorTable.instance();
        sqlStdOperatorTable.register(countRelation);

        this.frameworkConfig = Frameworks.newConfigBuilder()
                .parserConfig(parserConfig)
                .defaultSchema(schemaPlus)
                .programs(Programs.sequence(Programs.ofRules(Programs.RULE_SET), Programs.CALC_PROGRAM))
                .traitDefs(traitDefs)
                .operatorTable(sqlStdOperatorTable)
                .build();

Adding a User-Defined Function to Calcite

3 Answers3

Linked