[ 
https://issues.apache.org/jira/browse/CALCITE-3394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16949439#comment-16949439
 ] 

Vaishnavee Kulkarni commented on CALCITE-3394:
----------------------------------------------

[~danny0405] Thank you for suggesting this approach! I will explore these 
classes. 
For now, I got this working by using `SqlUserDefinedFunction` and 
`ScalarFunction` - 


{code:java}
public static class MyUdf1 {
   public Integer eval(String a) {
      return a.length();
   }
}

final String functionName = "STR_LEN";
final ScalarFunction udfLengthFunction = 
ScalarFunctionImpl.create(Types.lookupMethod(MyUdf1.class, "eval", 
String.class));
connection.getRootSchema().getSubSchema("SYSTEM").add(functionName, 
udfLengthFunction);

FrameworkConfig frameworkConfig = getConfig();

SqlIdentifier udfLengthIdentifier = new 
SqlIdentifier(Collections.singletonList(functionName), null, SqlParserPos.ZERO, 
null);
final SqlOperator strLenOperator = new 
SqlUserDefinedFunction(udfLengthIdentifier, ReturnTypes.INTEGER, null, 
OperandTypes.STRING, null, udfLengthFunction);

final RelBuilder builder = RelBuilder.create(frameworkConfig);
RelNode udfRelNode = builder
      .scan("EMP")
      .project(builder.call(strLenOperator, builder.literal("SampleString")))
      .build();

ResultSet set = RelRunners.run(udfRelNode).executeQuery();
{code}
 

> Can not register implementation of a UDF with RexImpTable and use the same 
> with RelBuilder
> ------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-3394
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3394
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.21.0
>            Reporter: Vaishnavee Kulkarni
>            Priority: Major
>
> I am trying to register a simple UDF that returns the length of input string. 
> I can do this with prepareStatement approach -
> {code:java}
> public static class MyUdf1 {
>    public Integer eval(String a) {
>       return a.length();
>    }
> }
> public void testUDF() {
> final String strLenSql = "select STRLEN('SampleString') from emp";
> ScalarFunction strLenFunction = ScalarFunctionImpl.create(MyUdf1.class, 
> "eval");
> calciteConnection.getRootSchema().add("STRLEN", strLenFunction);
> ResultSet resultSet = 
> calciteConnection.prepareStatement(strLenSql).executeQuery();
> resultSet.next();
> System.out.println(resultSet.getString(1));
> }
> {code}
>  
> When I try the similar steps with _RelBuilder_, I can successfully register 
> the _SqlOperator_; but am unable to refer to the implementation of this 
> operator. The builder refers to _RexImpTable_'s maps for the function table 
> implementation and there is no public/protected api exposed for these maps. 
> {code:java}
> SqlFunction length = new SqlFunction("STRLEN",
>       SqlKind.OTHER_FUNCTION,
>       ReturnTypes.INTEGER,
>       null,
>       OperandTypes.STRING,
>       SqlFunctionCategory.USER_DEFINED_FUNCTION);
> SqlStdOperatorTable sqlStdOperatorTable = SqlStdOperatorTable.instance();
> sqlStdOperatorTable.register(length);
> FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
>       .parserConfig(SqlParser.Config.DEFAULT)
>       .defaultSchema(connection.getRootSchema().getSubSchema("SYSTEM"))
>       .programs(Programs.sequence(Programs.ofRules(Programs.RULE_SET), 
> Programs.CALC_PROGRAM))
>       .operatorTable(sqlStdOperatorTable)
>       .build();
> final RelBuilder builder = RelBuilder.create(frameworkConfig);
> RelNode udfRelNode = builder
>       .scan("EMP")
>       .project(builder.call(length,builder.literal("SampleString")))
>       .build();
> ResultSet resultSet = RelRunners.run(udfRelNode).executeQuery();
> {code}
>  
> This code throws exception - 
> {code:java}
> Caused by: java.lang.RuntimeException: cannot translate call 
> STRLEN($t3)Caused by: java.lang.RuntimeException: cannot translate call 
> STRLEN($t3) at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:756)
>  at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:730)
>  at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:199)
>  at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:684)
> {code}
> There are no junits that show this working with _RelBuilder_. Is it possible 
> currently to register and use the udfs with RelBuilder?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Reply via email to