User defined functions with non scalar return type

[Julian Feinauer <j....@pragmaticminds.de>]

Hey Guys,

 

first of all, great work!

I’m following the Calcite project since I worked a lot with Drill about 8 months ago and I’m very pleased about how everything has developed.

We are currently trying to replace our self-made SQL Parser and Engine with Calcite.

But I have one question as we need to create UDFs which do not only return a single scalar value.

 

In fact, we do some kind of mapping from one table (that is processed in a streaming fashion like the AggregateFunctions do) to another Table (or even more complex nested structure).

Currently I do not see a way to do this as the AggregateFunctions do only allow to return scalar primitive types, or?

All my trials in returning different types resulted in Compile Errors from the Code generation.

 

On the other hand, the TableFunctions do not seem appropriate to me as they can only use parameters as input and not Columns that are passed row by row, or?

 

I would really appreciate any suggestions or help for this problem.

I’m also open for a discussion about how to implement such a feature in Calcite.

 

Thank you already!

Julian

Re: User defined functions with non scalar return type

[Julian Hyde <jh...@apache.org>]

Calcite doesn’t support that currently. Here’s what it would take: First, complete the support for user-defined types. We already allow user-defined types internally (see how MockCatalogReader creates a structured type “Address” and how SqlValidatorTest.testStructuredTypes uses it) but they’re not fully supported. Second, define a mapping between Java types and SQL types so that you can declare a user-defined function or user-defined aggregate function, the validator will see that function’s Java return type and deduce the SQL type. Maybe that mapping could be done using Java annotations.

If that’s interesting to you please log a JIRA case.

Julian

> On Dec 9, 2016, at 1:22 AM, Julian Feinauer <j....@pragmaticminds.de> wrote:
> 
> Hey Guys,
> 
> 
> 
> first of all, great work!
> 
> I’m following the Calcite project since I worked a lot with Drill about 8 months ago and I’m very pleased about how everything has developed.
> 
> We are currently trying to replace our self-made SQL Parser and Engine with Calcite.
> 
> But I have one question as we need to create UDFs which do not only return a single scalar value.
> 
> 
> 
> In fact, we do some kind of mapping from one table (that is processed in a streaming fashion like the AggregateFunctions do) to another Table (or even more complex nested structure).
> 
> Currently I do not see a way to do this as the AggregateFunctions do only allow to return scalar primitive types, or?
> 
> All my trials in returning different types resulted in Compile Errors from the Code generation.
> 
> 
> 
> On the other hand, the TableFunctions do not seem appropriate to me as they can only use parameters as input and not Columns that are passed row by row, or?
> 
> 
> 
> I would really appreciate any suggestions or help for this problem.
> 
> I’m also open for a discussion about how to implement such a feature in Calcite.
> 
> 
> 
> Thank you already!
> 
> Julian
>