[
https://issues.apache.org/jira/browse/CALCITE-937?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-937:
--------------------------------
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
was:
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
> 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)
