[CALCITE-113] User-defined scalar functions

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Labels: github-import
• Assignee: Unassigned
• Reporter: GitHub Import
• Created: 18/Jun/14 08:11
• Updated: 18/Jun/14 08:11
• Resolved: 18/Jun/14 08:11

Description

Add user-defined scalar functions to Optiq. (By "scalar" I mean not table functions.)

Define them in the model like this:

```json
schemas: [
{
name: 'foodmart',
functions: [

{ name: 'MY_PLUS', className: 'com.example.functions.MyPlusFunction' }

]
}
]
```

Two options for how functions are defined. I propose that we do one or both.

1. MyPlusFunction implements org.eigenbase.sql.SqlOperator. (This approach would allow user-defined functions to have multiple overloads, and to derive a return type and return null-ness based on the arguments. We will need to add a mechanism for such a function to implement itself.)

2. Use the Drill annotations:

```java
@FunctionTemplate(name = "add", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
public static class Add1 implements DrillSimpleFunc {
@Param IntHolder left;
@Param IntHolder right;
@Output IntHolder out;

public void setup(RecordBatch b) {}

public void eval()

{ out.value = left.value + right.value; }

}
```

See https://github.com/apache/incubator-drill/blob/master/exec/java-exec/src/main/java/org/apache/drill/exec/expr/annotations/FunctionTemplate.java.

Drill functions can be inlined by the JIT compiler. The above is as efficient as writing "x + y".

AFAICT, Drill functions are not overloaded. The arguments and result of "add" are INTEGERs. Of course arguments would be implicitly converted, per the usual the SQL conversion rules.

Automatic discovery of functions (e.g. by scanning jars) is out of scope of this feature.

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/113
Created by: julianhyde
Labels: enhancement,
Created at: Wed Jan 15 19:53:09 CET 2014
State: closed