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 <[email protected]>
> 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
>
>
>
> [email protected]
