UNLIST table-valued function

[dyemanov]

Implement a built-in function working the opposite way to the existing aggregate function LIST - i.e. parse the delimited string and return a record set containing the parsed values.

It may be a possible solution for #3821, as it would be possible to do WHERE ID IN (SELECT * FROM UNLIST(...))

The name UNLIST is suggested due to (1) being an antipode to the existing LIST function (even if we add a standard synonym LISTAGG one day) and (2) SQL standard having the UNNEST table-value function which does the similar job just for arrays/multi-sets.

Syntax:

<list derived table> ::= UNLIST(<value> [, <separator>] [<data type conversion>])
<separator> ::= <value>
<data type conversion> ::= RETURNING <data type>

If <separator> is omitted, comma is implied (as for LIST). If <data type conversion> is omitted, resulting field has the data type of the first input parameter.

The RETURNING syntax look weird, but this is exactly how SQL specification declares the output data type in JSON functions, so we used the same approach here.

Remaining questions:

1. I suppose it's OK to imply UNLIST as relation name if the AS clause is omitted. But I'm not sure about the column name - should we also imply UNLIST, or maybe use keyword VALUE or maybe some other ideas?

select unlist from unlist(...)
select value from unlist(...)
select unlist.unlist from unlist(...)
select unlist.value from unlist(...)

2. Should we trim the parsed strings under the hood, or make it optional, or leave it up to developers?
   select * from unlist('1, 2, 3')

3. If <data type conversion> is omitted and the input argument is BLOB, should we return also BLOB or maybe VARCHAR(MAX)?

[AlexPeshkoff]

> The RETURNING syntax look weird, but ....
Really weird, moreover grammatically UNLIST looks much like CAST and RETURNING is used in PL/SQL for other purposes. May be use AS instead RETURNING?
unlist (something as bigint)

> If data type conversion is omitted, resulting field has the data type of the first input parameter.
Not always good. Imagine parameter is CHAR(30000). Having very wide (and 99% useless) data set appears overkill. I suggest to limit it to something reasonable (64 or 128) and let one who needs wider values use explicit data type. And yes, use VARCHAR(this_size) for BLOB argument. Default should be OK in most cases.

> But I'm not sure about the column name - should we also imply UNLIST, or maybe use keyword VALUE or maybe some other ideas?
VALUE looks better for me, but I do insist.

[dyemanov]

The RETURNING syntax look weird, but .... Really weird, moreover grammatically UNLIST looks much like CAST and RETURNING is used in PL/SQL for other purposes. May be use AS instead RETURNING? unlist (something as bigint)

As I said, this is the standard way used in some other functions. And standard support for JSON functions is implemented by RedSoft and will be offered as PR. So it seems we'll have the "weird" RETURNING syntax anyway ;-) Thus I do not see as a big problem using it for UNLIST too. That said, AS was also considered and this is why I prefer to see more opinions regarding this syntax.

[dyemanov]

As for VALUE as derived column name, it looks OK for single-column table-value function, but we may have more complex ones in the future. In this case both <function_name> and VALUE look unsuitable.

[hvlad]

I've edited description to be better readable.

[hvlad]

As for VALUE as derived column name, it looks OK for single-column table-value function, but we may have more complex ones in the future. In this case both <function_name> and VALUE look unsuitable.

UNLIST(...) [AS ALIAS[(COL1, ..., COLN)]] ?

[hvlad]

The RETURNING syntax look weird, but ....
Really weird, moreover grammatically UNLIST looks much like CAST and RETURNING is used in PL/SQL for other purposes. May be use AS instead RETURNING?

Does it makes sence to have both ?

UNLIST(... [AS | RETURNING ...])

[mrotteveel]

I suppose it's OK to imply UNLIST as relation name if the AS clause is omitted. But I'm not sure about the column name - should we also imply UNLIST, or maybe use keyword VALUE or maybe some other ideas?

The fact that we allow derived tables without a correlation name is actually a bug if you look at the standard (e.g. see my second comment on #7574). Personally I think the UNLIST should require the correlation clause (including the column list), so there would be no need to specify defaults.

And otherwise, using unlist and value as a default is probably a bad idea as those will be reserved words (at least VALUE is already)

[mrotteveel]

In other words

UNLIST(...) [AS] _correlation-name_ (_column-name_)

(i.e. not optional!)

[dyemanov]

Mark, while <correlation name> is mandatory in the SQL specification, <parenthesized derived column list> is optional.

[mrotteveel]

The RETURNING syntax look weird, but ....
Really weird, moreover grammatically UNLIST looks much like CAST and RETURNING is used in PL/SQL for other purposes. May be use AS instead RETURNING?

Does it makes sence to have both ?

UNLIST(... [AS | RETURNING ...])

Given the correlation clause should be required, I think having two AS in a row will be confusing to read. I think sticking to RETURNING is better, and not add unnecessary syntax alternatives, especially if the standard already uses it for similar functionality.

[mrotteveel]

Mark, while <correlation name> is mandatory in the SQL specification, <parenthesized derived column list> is optional.

Yes it is, but that is because for normal derived tables you're able to specify column names in the query expression itself, and that doesn't apply here, so naming the column should be required here together with the correlation name.

[dyemanov]

Personally I think the UNLIST should require the correlation clause (including the column list), so there would be no need to specify defaults.

So far I tend to agree.