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 <winifred.wenhui.t...@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.t...@gmail.com