Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3547

SqlValidatorException because Planner cannot find UDFs added to schema

    XMLWordPrintableJSON

    Details

      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).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                danny0405 Danny Chen
                Reporter:
                seancxmao Chenxiao Mao
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 20m
                  1h 20m