[DISCUSS] Support Higher-order functions

[Wen-hui Tang <wi...@gmail.com>]

Hello all,

Spark 2.4.0 was released last month. I noticed that Spark sql in Spark 2.4 adds a lot of higher-order functions, to deal with complex data types easier[1].
For example, SQL statement like "SELECT TRANSFORM(values, element -> element + 1) FROM iteblog;"[2] returns an array that is the result of applying function to each element of array.
I wonder if it is possible for Calcite to enhance it's parser to support higher-order functions. Thus,  projects powered by Calcite such as Flink can benefit from it.

Look forward to your feedback.

Best,
Wen-hui Tang

[1] https://issues.apache.org/jira/browse/SPARK-23899
[2] https://issues.apache.org/jira/browse/SPARK-23908



winifred.wenhui.tang@gmail.com

Re: [DISCUSS] Support Higher-order functions

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

When someone mentions higher-order functions in SQL, I get excited and start thinking about the stuff in a good functional language like ML that I’m missing from SQL. For example, we might do something like this, using SQL’s “WITH" as the equivalent of ML’s “let” construct[1]:

 WITH
   VAL two AS 2
   FUN twice(x) AS two * x
   FUN fibonacci(n) AS CASE WHEN n = 0 THEN 1 ELSE n * fibonacci(n - 1) END
 SELECT fibonacci(twice(empno))
 FROM emp

But I guess you’re not talking about *user-defined* high-order functions, or functions-as-values, just some operators that apply lambdas to a collection.

I can’t help pointing out that SQL has always been a functional language; for example, WHERE is the filter operator and SELECT is the map operator (what Presto calls TRANSFORM). Your example

  SELECT TRANSFORM(ARRAY[1, 2, 3], v -> v + 1)

is syntactic sugar for

  SELECT ARRAY(
    SELECT v + 1
    FROM UNNEST(ARRAY[1, 2, 3]) AS t(v))

In summary. Yes, I’d very much like to support higher-order functions. Not just lambda functions, but named functions and assign-once variables in a WITH clause around a query or expression. And also functions-as-values, which means we would need to devise a way to store function values in tables. And it would be hard to do higher-order functions without making the type system polymorphic.

Julian

[1] https://en.wikipedia.org/wiki/Let_expression <https://en.wikipedia.org/wiki/Let_expression>

> On Dec 3, 2018, at 5:10 AM, Wen-hui Tang <wi...@gmail.com> wrote:
> 
> Hello all,
> 
> Spark 2.4.0 was released last month. I noticed that Spark sql in Spark 2.4 adds a lot of higher-order functions, to deal with complex data types easier[1].
> For example, SQL statement like "SELECT TRANSFORM(values, element -> element + 1) FROM iteblog;"[2] returns an array that is the result of applying function to each element of array.
> I wonder if it is possible for Calcite to enhance it's parser to support higher-order functions. Thus,  projects powered by Calcite such as Flink can benefit from it.
> 
> Look forward to your feedback.
> 
> Best,
> Wen-hui Tang
> 
> [1] https://issues.apache.org/jira/browse/SPARK-23899
> [2] https://issues.apache.org/jira/browse/SPARK-23908
> 
> 
> 
> winifred.wenhui.tang@gmail.com