Shall we implement higher order function map (Presto Transform)?

[lmatz]

This refers to the transform function in PrestoDB:https://prestodb.io/docs/current/functions/array.html#transform

The way Risingwave currently write is:

dev=> CREATE TABLE my_table (my_array integer[]);
CREATE_TABLE
dev=> insert into my_table values (ARRAY[1,2,3,4,5]);
INSERT 0 1
dev=> select unnest(my_array) * 2 as transformed_array from my_table;
 transformed_array 
-------------------
                 2
                 4
                 6
                 8
                10
(5 rows)

If I do:

select array_agg(unnest(my_array) * 2)  as transformed_array from my_table;
ERROR:  QueryError: Feature is not yet implemented: table function inside aggregation calls

And in PG:

martin=# select array_agg(unnest(my_array) * 2) as transformed_array from my_table;
ERROR:  aggregate function calls cannot contain set-returning function calls
LINE 1: select array_agg(unnest(my_array) * 2) as transformed_array ...
                         ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

Current workaround is to use subquery / CTE / view:

select array_agg(transformed_array) from (
  select unnest(my_array) * 2 as transformed_array from my_table
) as unnested;

But writing Transform(my_array, x -> x * 2) is much more concise.

Also note the unnest + array_agg trick above only works for 1d array, because unnest in PostgreSQL works recursively. We would need a non-recursive unnest_list to pair with array_agg precisely if such usage become more common.

This case is inspired by users' query of Presto, but the user's usage can be worked around as it essentially is a xxx in (A, B, C)

Link risingwavelabs/rfcs#69

[lmatz]

We would need a non-recursive unnest_list to pair with array_agg precisely if such usage become more common.

What if we don't know the dimension in advance? I suppose we cannot determine how many times unnest_list and array_agg we should apply. In this case transform seems to be the only way out

[xiangjinwu]

What if we don't know the dimension in advance?

It is part of the static type. int[] requires one pair while int[][][] requires three pairs. We do not allow {2,3} and {{3,4},{5,6}} to appear in the same column.

[st1page]

I missed this issue and write a RFC today for the higher order function risingwavelabs/rfcs#69

[lmatz]

I missed this issue and write a RFC today for the higher order function risingwavelabs/rfcs#69

It's fine, I was thinking the same:

For the lambda expression, it is like a anonymous SQL UDF. We can implement the SQL UDF first and design lambda expression later.

I remember Xiangjin told me that the lambda expression here is more restrictive, i.e. it must be a const function

[xiangjinwu]

Let me clarify this, supporting lambda expression and higher order function are two different things, although related.

It is possible to use higher order function (map, filter, reduce) without lambda expression. We just need a builtin or UDF:

• transform(array[1, 4, 9, 16], sqrt) -> {1, 2, 3, 4}

Lambda expression just makes the use of HOF more convenient, so that we do not need to create function beforehand and come up with names.

The core part of HOP support is how to pass functions (incl lambda) as arguments. Right now Expr::eval only consumes chunks with typed arrays, but we may not want to put functions in arrays/chunks - that is, there are no array/column of function type.

By saying the argument (possible a function) must be const, I mean it cannot be a column (InputRef) as follows:

select transform(arr, fn) from
  (values (array['alice', 'bob']), (array['san francisco']), (array['utf8', 'ascii'])) as t1 (arr)
cross join
  (values (lower), (upper), (initcap)) as t2 (fn);

(To be honest, the SomeAllExpression today is similar to HOF. v < ALL(arr) and v ~ ALL(arr) is handled by the same logic parameterized by the binary predicate operator < or ~, with hack of course.)

[xxchan]

We've already implemented lambda expression and higher order function (array_transform) in #11937

Remaining tracked in #11837