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?

Shatzer

I'm facing the same problem. Were you able to figure out the solution? – Triennium

@kishore Unfortunately not yet. – Shatzer

Are you adding a schema to rootschema, like a sub-schema? Something like rootSchema.add("my-schema", new Subschema()) – Triennium

@MichaelAlexeev: any luck with the solution? – Spectrograph

@ArunAK Arun, no, I wasn't able to make at work. – Shatzer

@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. – Spectrograph

Answer 1

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());
    }
} 

Bodrogi

CalciteCatalogReader constructor is public CalciteCatalogReader(CalciteSchema rootSchema, List<String> defaultSchema, RelDataTypeFactory typeFactory, CalciteConnectionConfig config) . This solution does not compiles. – Gyrate

The version may be different. – Bodrogi

Outdated version – Cheboksary

Answer 2

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

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

Rhombohedral

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. – Shatzer

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 – Shatzer

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. – Rhombohedral

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 – Shatzer

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(<NUMERIC>, <NUMERIC>). According to the CALCITE-937 this functionality is there since the 1.5.0 release. Should I try a different version? – Shatzer

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) – Spectrograph

Answer 3

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();

Gyrate