You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Ng Jiunn Jye (JIRA)" <ji...@apache.org> on 2015/10/26 15:26:28 UTC
[jira] [Created] (CALCITE-937) Unable to use user defined function
within view
Ng Jiunn Jye created CALCITE-937:
------------------------------------
Summary: Unable to use 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
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)