Re: [DISCUSS] Support Higher-order functions
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:
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
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 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.
 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 <winifred.wenhui.tang@xxxxxxxxx> 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.
> For example, SQL statement like "SELECT TRANSFORM(values, element -> element + 1) FROM iteblog;" 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.
> Wen-hui Tang
>  https://issues.apache.org/jira/browse/SPARK-23899
>  https://issues.apache.org/jira/browse/SPARK-23908