Thanks for the reply Julian. I'm happy to modify Solr rules, but I'm
not clear on what type of RelOptRule to use for a function? The Solr
adapter code I'm working with is here:
https://github.com/apache/lucene-solr/tree/master/solr/core/src/java/org/apache/solr/handler/sql
I tried to distill this down as simple as possible, the Solr adapter
provides a custom schema impl that looks like:
{code}
class SolrSchema extends AbstractSchema {
final Properties properties;
SolrSchema(Properties properties) {
super();
this.properties = properties;
}
@Override
protected Multimap<String, Function> getFunctionMultimap() {
Method timMethod = Types.lookupMethod(TimFunction.class, "eval",
String.class);
ScalarFunction timFunc = ScalarFunctionImpl.create(timMethod);
ImmutableMultimap.Builder<String,Function> mmb =
ImmutableMultimap.builder();
mmb.put("tim", timFunc);
return mmb.build();
}
...
{code}
Notice that I'm overriding the getFunctionMultimap method to install
the "tim" function ... is this equivalent to using the JSON model
approach you suggested?
My TimFunction is simply:
{code}
public class TimFunction {
public static String eval(String str) {
System.out.println(">> TimFunction#eval("+str+")");
return str;
}
}
{code}
Of course, what I need eventually is to push-down the function to Solr
but I'm just getting the feel for how functions work for now.
I added some println's in the SolrRules code to see where the "tim"
function gets invoked for this query:
select movie_id, user_id, tim('foo') as tim from ratings order by
movie_id desc LIMIT 100
What I'm seeing is the return type is VARCHAR(1) which doesn't seem
correct as it returns String and at some point the function (RexCall)
gets transformed into CAST?
>> timMethod: public static java.lang.String
>> org.apache.solr.handler.sql.TimFunction.eval(java.lang.String)
>> SolrProjectRule: project=rel#14:LogicalProject.NONE.[[0
>> DESC]](input=rel#13:Subset#3.NONE.[0 DESC],movie_id=$0,user_id=$1,tim=$2),
>> relTypeName=LogicalProject, input: rel#13:Subset#3.NONE.[0 DESC]
>> SolrProjectRule: converted=rel#31:Subset#3.Solr.[0 DESC],
>> relTypeName=RelSubset, class: class org.apache.calcite.plan.volcano.RelSubset
>> there are 3 projects in rel#14:LogicalProject.NONE.[[0
>> DESC]](input=rel#13:Subset#3.NONE.[0 DESC],movie_id=$0,user_id=$1,tim=$2)
>> 0: $0, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 1: $1, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 2: $2, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=VARCHAR(1)
>> namedProjects=[<$0, movie_id>, <$1, user_id>, <$2, tim>]
>> created new SolrProject(1)
>> SolrProjectRule:
>> project=rel#10:LogicalProject.NONE.[](input=rel#9:Subset#1.NONE.[],movie_id=$1,user_id=$0,tim=tim('foo')),
>> relTypeName=LogicalProject, input: rel#9:Subset#1.NONE.[]
>> SolrProjectRule: converted=rel#39:Subset#1.Solr.[], relTypeName=RelSubset,
>> class: class org.apache.calcite.plan.volcano.RelSubset
>> there are 3 projects in
>> rel#10:LogicalProject.NONE.[](input=rel#9:Subset#1.NONE.[],movie_id=$1,user_id=$0,tim=tim('foo'))
>> 0: $1, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 1: $0, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 2: tim('foo'), class=class org.apache.calcite.rex.RexCall,
>> kind=OTHER_FUNCTION, type=VARCHAR(1)
>> namedProjects=[<$1, movie_id>, <$0, user_id>, <tim('foo'), tim>]
>> created new SolrProject(2)
>> TimFunction#eval(foo)
>> SolrProjectRule: project=rel#44:LogicalProject.NONE.[0
>> DESC](input=rel#43:Subset#5.NONE.[1
>> DESC],movie_id=$1,user_id=$0,tim=tim('foo')), relTypeName=LogicalProject,
>> input: rel#43:Subset#5.NONE.[1 DESC]
>> SolrProjectRule: converted=rel#48:Subset#5.Solr.[1 DESC],
>> relTypeName=RelSubset, class: class org.apache.calcite.plan.volcano.RelSubset
>> there are 3 projects in rel#44:LogicalProject.NONE.[0
>> DESC](input=rel#43:Subset#5.NONE.[1
>> DESC],movie_id=$1,user_id=$0,tim=tim('foo'))
>> 0: $1, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 1: $0, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 2: tim('foo'), class=class org.apache.calcite.rex.RexCall,
>> kind=OTHER_FUNCTION, type=VARCHAR(1)
>> namedProjects=[<$1, movie_id>, <$0, user_id>, <tim('foo'), tim>]
>> created new SolrProject(3)
>> SolrProjectRule:
>> project=rel#8:LogicalProject.NONE.[](input=rel#7:Subset#0.Solr.[],user_id=$1,movie_id=$4),
>> relTypeName=LogicalProject, input: rel#7:Subset#0.Solr.[]
>> SolrProjectRule: converted=rel#7:Subset#0.Solr.[], relTypeName=RelSubset,
>> class: class org.apache.calcite.plan.volcano.RelSubset
>> there are 2 projects in
>> rel#8:LogicalProject.NONE.[](input=rel#7:Subset#0.Solr.[],user_id=$1,movie_id=$4)
>> 0: $1, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 1: $4, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> namedProjects=[<$1, user_id>, <$4, movie_id>]
>> created new SolrProject(4)
>> SolrProjectRule: project=rel#54:LogicalProject.NONE.[0
>> DESC](input=rel#43:Subset#5.NONE.[1
>> DESC],movie_id=$1,user_id=$0,tim=CAST('f'):VARCHAR(1) CHARACTER SET
>> "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"),
>> relTypeName=LogicalProject, input: rel#43:Subset#5.NONE.[1 DESC]
>> SolrProjectRule: converted=rel#48:Subset#5.Solr.[1 DESC],
>> relTypeName=RelSubset, class: class org.apache.calcite.plan.volcano.RelSubset
>> there are 3 projects in rel#54:LogicalProject.NONE.[0
>> DESC](input=rel#43:Subset#5.NONE.[1
>> DESC],movie_id=$1,user_id=$0,tim=CAST('f'):VARCHAR(1) CHARACTER SET
>> "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary")
>> 0: $1, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 1: $0, class=class org.apache.calcite.rex.RexInputRef, kind=INPUT_REF,
>> type=JavaType(class java.lang.String)
>> 2: CAST('f'):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
>> "ISO-8859-1$en_US$primary", class=class org.apache.calcite.rex.RexCall,
>> kind=CAST, type=VARCHAR(1)
>> namedProjects=[<$1, movie_id>, <$0, user_id>, <CAST('f'):VARCHAR(1)
>> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", tim>]
>> created new SolrProject(5)
>> TimFunction#eval(foo)
>> SolrProjectRule: project=rel#61:LogicalProject.NONE.[[0
>> DESC]](input=rel#43:Subset#5.NONE.[1
On Mon, Mar 27, 2017 at 2:28 PM, Julian Hyde <jh...@apache.org> wrote:
> I like the idea of an adapter-supplied function, but it’s not in Calcite
> right now.
>
> The easiest thing is probably to define the function in the JSON model [1].
> That should get you through validation (which is where you’re tripping up
> right now).
>
> Then you’ll need to push the function down to the storage engine. There isn’t
> a nice way of declaring what UDFs a particular engine can handle. I fear that
> you’ll need to modify the Solr rules.
>
> Julian
>
> [1] https://calcite.apache.org/docs/model.html#function
> <https://calcite.apache.org/docs/model.html#function>
>
>
>> On Mar 27, 2017, at 11:23 AM, Timothy Potter <thelabd...@gmail.com> wrote:
>>
>> I'm trying to add the ability to push-down the evaluation of a
>> function to my storage engine (Solr in this case). For instance,
>>
>> select movie_id, user_id, custom_rating('foo') as rating from movie_ratings
>>
>> where custom_rating('foo') is some function in my storage engine.
>>
>> This seems like a UDF in Calcite terms but I'm not having any luck
>> getting the function node passed to my adapter code.
>>
>> I've tried adding a ScalarFunction to my schema but it gets evaluated
>> early (long before my adapter code gets hit).
>>
>> I've also tried a TableFunction, but always end up with this error (using
>> 1.11)
>>
>> Caused by: org.apache.calcite.runtime.CalciteContextException: From
>> line 1, column 27 to line 1, column 36: No match found for function
>> signature custom_rating(<CHARACTER>)
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>> at
>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>> at
>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
>> at
>> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
>> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:796)
>> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:784)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4080)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1614)
>> at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:278)
>> at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4445)
>> at
>> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4432)
>> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
>>
>>
>> which when debugging looks like the function isn't registered
>> correctly in the schema but I've overridden protected Multimap<String,
>> Function> getFunctionMultimap() in my schema impl. to include my
>> function.
>>
>> I scanned the docs and the UDF code (e.g. Smalls) but don't see an
>> example of a adapter supplied function. Any guidance you can provide
>> is appreciated.
>>
>> Thanks.
>> Tim
>
