Hey, how can I create an array from a list of values in Pinot? Calcite SQL grammar has this value constructor:

ARRAY '[' value [, value ]* ']'	Creates an array from a list of values.

Could you please write a sample query to help us understand the ask?

yes, exactly. And also w/ literals to quickly verify behavior before working w/ actual columns:

SELECT ARRAY[1, 2, 3];

SELECT ARRAYLENGTH(ARRAY[1, 2, 3]);

etc.

This is a transform. I can see some challenges implementing it: 1. We can model the result of

ARRAY

as a multi-value, but all elements of the MV must be the same data type 2. Scalar function can only take fixed number of arguments (instead of something like

array(String... values)

) 3. Scalar function cannot do type matching now, which means we might need to have

arrayInt()

,

arrayLong()

etc

yes, Pinot only works w/ heterogenous MV columns. There are MV columns transformations (

ARRAYLENGTH

,

MAP_VALUE

,

VALUEIN

) and aggregations (

COUNTMV

,

MINMV

, etc.) that works w/ the MV column generated using

ARRAY

which is very useful.

Yes, let's file a GH issue to track it. It's not trivial to add the support and might need further discussion