[CALCITE-3547] SqlValidatorException because Planner cannot find UDFs added to schema

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.21.0
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Chenxiao Mao
• Created: 29/Nov/19 10:48
• Updated: 06/Mar/20 04:36
• Resolved: 06/Dec/19 05:50

Description

This could be reproduce by the below test case:

  @Test public void testValidateUserDefinedFunctionInSchema() throws Exception {
    SchemaPlus rootSchema = Frameworks.createRootSchema(true);
    rootSchema.add("my_plus",
      ScalarFunctionImpl.create(Smalls.MyPlusFunction.class, "eval"));
    final FrameworkConfig config = Frameworks.newConfigBuilder()
      .defaultSchema(
        CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.HR))
      .build();
    final Planner planner = Frameworks.getPlanner(config);
    final String sql = "select \"my_plus\"(\"deptno\", 100) as \"p\"\n"
      + "from \"hr\".\"emps\"";

    SqlNode parse = planner.parse(sql);
    SqlNode validate = planner.validate(parse);
    assertThat(Util.toLinux(validate.toString()),
      equalTo("SELECT `my_plus`(`emps`.`deptno`, 100) AS `p`\n"
        + "FROM `hr`.`emps` AS `emps`"));
  }

The exception is as below:

org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature my_plus(<NUMERIC>, <NUMERIC>)

By digging into the related code, I found that the validator inside PlannerImpl only looks for UDFs in SqlStdOperatorTable.

If we let validator inside PlannerImpl looks for UDFs not only in SqlStdOperatorTable but also CalciteCatalogReader like what CalcitePrepareImpl does, we can avoid this validation exception.

  private SqlValidator createSqlValidator(Context context,
      CalciteCatalogReader catalogReader) {
    final SqlOperatorTable opTab0 =
        context.config().fun(SqlOperatorTable.class,
            SqlStdOperatorTable.instance());
    final SqlOperatorTable opTab =
        ChainedSqlOperatorTable.of(opTab0, catalogReader);
    final JavaTypeFactory typeFactory = context.getTypeFactory();
    final SqlConformance conformance = context.config().conformance();
    return new CalciteSqlValidator(opTab, catalogReader, typeFactory,
        conformance);
  }

This seems to be the same problem discussed before at StackOverflow (https://stackoverflow.com/questions/44147819/adding-a-user-defined-function-to-calcite).

Issue Links

• links to: GitHub Pull Request #1615