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

User-defined function within view

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 1.4.0-incubating
    • 1.5.0
    • core
    • None

    Description

      Unable to use user-defined function within view.

      Steps to reproduce:
      Create a new schema with User Defined Function and View.
      Use the new user defined function in the View SQL
      Execute query with UDF
      => Success // UDF is registered successfully
      Execute query on VIEW
      => Error : No match found for function signature MY_INCREMENT(<NUMERIC>, <NUMERIC>

      Oct 26, 2015 10:21:43 PM org.apache.calcite.sql.validate.SqlValidatorException <init>
      SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature MY_INCREMENT(<NUMERIC>, <NUMERIC>)

      Test Code:
      *************************************************
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.sql.Statement;

      import org.apache.calcite.adapter.java.ReflectiveSchema;
      import org.apache.calcite.schema.Function;
      import org.apache.calcite.schema.ScalarFunction;
      import org.apache.calcite.schema.SchemaPlus;
      import org.apache.calcite.schema.impl.AbstractSchema;
      import org.apache.calcite.schema.impl.ScalarFunctionImpl;
      import org.apache.calcite.schema.impl.ViewTable;
      import org.apache.calcite.util.Util;

      import com.google.common.collect.ImmutableList;

      public class TestViewWithUDF {

      public static void main(String[] args) throws Exception

      { // TODO Auto-generated method stub new TestViewWithUDF().run(); }

      public void run() throws ClassNotFoundException, SQLException

      { Class.forName("org.apache.calcite.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:calcite:"); CalciteConnection calciteConnection = connection .unwrap(CalciteConnection.class); SchemaPlus rootSchema = calciteConnection.getRootSchema(); ReflectiveSchema refSchema = new ReflectiveSchema(new Hr()); rootSchema.add("hr", new ReflectiveSchema(new Hr())); SchemaPlus post = rootSchema.add("POST", new AbstractSchema()); ScalarFunction myIncrFunc = ScalarFunctionImpl.create( MyIncrement.class, "eval"); post.add("MY_INCREMENT", myIncrFunc); post.add( "V_EMP", ViewTable .viewMacro( post, "select \"empid\" as EMPLOYEE_ID, \"name\" || ' ' || \"name\" as EMPLOYEE_NAME, \n" + "\"salary\" as EMPLOYEE_SALARY, POST.MY_INCREMENT(\"salary\", 10) as INCREMENTED_SALARY \n" + "from \"hr\".\"emps\"", ImmutableList.<String> of(), null)); Statement statement = connection.createStatement(); ResultSet resultSet = statement .executeQuery("select \"empid\" as EMPLOYEE_ID, \"name\" || ' ' || \"name\" as EMPLOYEE_NAME, \n" + "\"salary\" as EMPLOYEE_SALARY, POST.MY_INCREMENT(\"salary\", 10) as INCREMENTED_SALARY \n" + "from \"hr\".\"emps\""); printRS(resultSet); ResultSet viewResultSet = statement.executeQuery("select * \n" + "from \"POST\".\"V_EMP\""); printRS(viewResultSet); resultSet.close(); statement.close(); connection.close(); }

      public void printRS (ResultSet resultSet) throws SQLException{
      final StringBuilder buf = new StringBuilder();
      while (resultSet.next()) {
      int n = resultSet.getMetaData().getColumnCount();
      for (int i = 1; i <= n; i++)

      { buf.append(i > 1 ? "; " : "") .append(resultSet.getMetaData().getColumnLabel(i)) .append("=") .append(resultSet.getObject(i)); }

      System.out.println(buf.toString());
      buf.setLength(0);
      }
      }

      /** Object that will be used via reflection to create the "hr" schema. */
      public static class Hr {
      public final Employee[] emps =

      { new Employee(100, "Bill",900), new Employee(200, "Eric",1000), new Employee(150, "Sebastian",1100), }

      ;
      }

      /** Object that will be used via reflection to create the "emps" table. */
      public static class Employee {
      public final int empid;
      public final String name;
      public final int salary;

      public Employee(int empid, String name, int salary)

      { this.empid = empid; this.name = name; this.salary = salary; }

      }

      public static class MyIncrement {
      public float eval(int x, int y)

      { return x + x*y/100; }

      }
      }

      *************************************

      Error Stack trace :
      Oct 26, 2015 10:21:43 PM org.apache.calcite.sql.validate.SqlValidatorException <init>
      SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature MY_INCREMENT(<NUMERIC>, <NUMERIC>)
      Oct 26, 2015 10:21:43 PM org.apache.calcite.runtime.CalciteException <init>
      SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 2, column 35 to line 2, column 60: No match found for function signature MY_INCREMENT(<NUMERIC>, <NUMERIC>)
      Exception in thread "main" java.sql.SQLException: error while executing SQL "select *
      from "POST"."V_EMP"": From line 2, column 35 to line 2, column 60: No match found for function signature MY_INCREMENT(<NUMERIC>, <NUMERIC>)
      at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
      at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:112)
      at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:130)
      at org.apache.calcite.jdbc.TestViewWithUDF.run(TestViewWithUDF.java:59)
      at org.apache.calcite.jdbc.TestViewWithUDF.main(TestViewWithUDF.java:24)
      Caused by: org.apache.calcite.runtime.CalciteContextException: From line 2, column 35 to line 2, column 60: No match found for function signature MY_INCREMENT(<NUMERIC>, <NUMERIC>)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:88)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:436)
      at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
      at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:688)
      at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:673)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3845)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1543)
      at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:287)
      at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:211)
      at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4184)
      at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:1)
      at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1461)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1444)
      at org.apache.calcite.sql.SqlAsOperator.deriveType(SqlAsOperator.java:122)
      at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4184)
      at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:1)
      at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1461)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1444)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:433)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3352)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2956)
      at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
      at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:843)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:829)
      at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:207)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:803)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:517)
      at org.apache.calcite.prepare.CalcitePrepareImpl.parse_(CalcitePrepareImpl.java:269)
      at org.apache.calcite.prepare.CalcitePrepareImpl.analyzeView(CalcitePrepareImpl.java:245)
      at org.apache.calcite.schema.Schemas.analyzeView(Schemas.java:330)
      at org.apache.calcite.schema.impl.ViewTable$ViewTableMacro.apply(ViewTable.java:163)
      at org.apache.calcite.jdbc.CalciteSchema.getTableBasedOnNullaryFunction(CalciteSchema.java:461)
      at org.apache.calcite.prepare.CalciteCatalogReader.getTableFrom(CalciteCatalogReader.java:118)
      at org.apache.calcite.prepare.CalciteCatalogReader.getTable(CalciteCatalogReader.java:99)
      at org.apache.calcite.prepare.CalciteCatalogReader.getTable(CalciteCatalogReader.java:1)
      at org.apache.calcite.sql.validate.EmptyScope.getTableNamespace(EmptyScope.java:75)
      at org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace(DelegatingScope.java:124)
      at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:104)
      at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:843)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:829)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2743)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2728)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2946)
      at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
      at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:843)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:829)
      at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:207)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:803)
      at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:517)
      at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:532)
      at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:222)
      at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:188)
      at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:671)
      at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:572)
      at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:541)
      at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:173)
      at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:561)
      at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:477)
      at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:110)
      ... 3 more
      Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature MY_INCREMENT(<NUMERIC>, <NUMERIC>)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:88)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:436)
      at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
      at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
      ... 64 more

      Attachments

        Activity

          People

            julianhyde Julian Hyde
            jiunnjye Ng Jiunn Jye
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: