You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2015/10/27 02:18:27 UTC
[jira] [Resolved] (CALCITE-937) User-defined function within view
[ https://issues.apache.org/jira/browse/CALCITE-937?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde resolved CALCITE-937.
---------------------------------
Resolution: Fixed
Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/74803d0a. Thanks for the test case!
> User-defined function within view
> ---------------------------------
>
> Key: CALCITE-937
> URL: https://issues.apache.org/jira/browse/CALCITE-937
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.4.0-incubating
> Reporter: Ng Jiunn Jye
> Assignee: Julian Hyde
> Priority: Minor
> Fix For: 1.5.0
>
>
> 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
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)