UNLIST table-value 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.

[aafemt]

Though it is true, I wonder why (in both cases) we cannot generate them as usual "expression", "expression1", etc.

[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?

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.

Agreed - 2 AS one after another is not good idea.

[mrotteveel]

Though it is true, I wonder why (in both cases) we cannot generate them as usual "expression", "expression1", etc.

Because derived tables must have explicitly specified column names. You shouldn't rely on generated names. The fact it is allowed (or maybe condoned) for top-level queries and not needed for (single-column) query expressions as columns or for IN/EXISTS/SINGULAR doesn't mean it is allowed for derived tables and CTEs.

[dyemanov]

They're somewhat separated -- UNLIST(A AS INT) AS T -- but maybe still not very friendly.

[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 ...])

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.

Agreed - 2 AS one after another is not good idea.

While I'm not insisting, must note that we already have this with CAST: SELECT CAST(expr AS datatype) AS alias ...

[mrotteveel]

Agreed - 2 AS one after another is not good idea.

While I'm not insisting, must note that we already have this with CAST: SELECT CAST(expr AS datatype) AS alias ...

That is a good point, but I don't think we should perpetuate it, especially not if there is a standard-endorsed alternative. To quote from 6.26 \<JSON value function>:

<JSON value function> ::=
    JSON_VALUE <left paren>
      <JSON API common syntax>
      [ <JSON returning clause> ]
      [ <JSON value empty behavior> ON EMPTY ]
      [ <JSON value error behavior> ON ERROR ]
    <right paren>

<JSON returning clause> ::=
    RETURNING <data type>

[mrotteveel]

It may be a possible solution for https://github.com/FirebirdSQL/firebird/issues/3821, as it would be possible to do WHERE ID IN (SELECT * FROM UNLIST(...))

I'm wondering, if we would allow unlist(...) as a derived table, wouldn't it be possible to also allow it as a query-expression (not 100% sure if that is the appropriate term, I always confuse the names SQL uses for this...):

where ID in(unlist(...))

[mrotteveel]

Or otherwise, only explicitly allow its use without select for IN, SINGULAR, SOME/ANY/ALL and maybe EXISTS (though then a length check would probably be simpler ;)?

[livius2]

Hi i know that you like to start from something simple but, think about e.g.: Excel. One separator is not enought here and one column too. Please consider multiple separators (and maybe multiple separators in multiple separators).

What i have in mind. Currently we have multiple stored procedure like this:

CREATE PROCEDURE GET_ROWS_FROM_LISTB_4COLS (
    ITEM_LIST BLOB SUB_TYPE 1,
    SEPARATOR_LIST VARCHAR(20) DEFAULT ';',
    SEPARATOR_SUB_LIST VARCHAR(20) DEFAULT ',' )
RETURNS (
    ITEM1 VARCHAR(120),
    ITEM2 VARCHAR(120),
    ITEM3 VARCHAR(120),
    ITEM4 VARCHAR(120) )

and i can call it with something like this.

SELECT * FROM GET_ROWS_FROM_LISTB_4COLS ('1111,2222,3333,4444;5555,6666,7777,8888;9999,0000,1234,4567', ';', ',')

and i got as result:

ITEM1   ITEM2   ITEM3   ITEM4
1111    2222    3333    4444
5555    6666    7777    8888
9999    0000    1234    4567

i also can ommit somthing in the list and got null or empty string in this case

SELECT * FROM GET_ROWS_FROM_LISTB_4COLS ('1111,,3333,;5555,6666;9999,0000,1234,4567', ';', ',')

ITEM1   ITEM2   ITEM3   ITEM4
1111        3333    
5555    6666        
9999    0000    1234    4567

If your implementation will support above it will be wonderfull. And what about "multiple separators in multiple separators" - e.g. [';', '#', '&']:

SELECT * FROM GET_ROWS_FROM_LISTB_4COLS ('1111,,3333,;5555,6666;9999,0000,1234,4567', [';', '#', '&'], [','])

if your implementation will alow this will be excelent!

[hvlad]

@livius2: looks like you are asking for CSV string to be parsed and returned in tabular format.

[livius2]

@hvlad

as rows and columns.

With one returned column it can be used in WHERE IN () but the usage purposes can be much wider. I show relation to CSV/excel, but we use it in multiple scenarions, we only sometimes use it in explicite CSV file scenarios.

[sim1984]

Csv and other ideas other than unlist are not discussed in this ticket. If you want other opportunities to hover tricky blobs, create separate tickets.

[asfernandes]

@hvlad

as rows and columns.

With one returned column it can be used in WHERE IN () but the usage purposes can be much wider. I show relation to CSV/excel, but we use it in multiple scenarions, we only sometimes use it in explicite CSV file scenarios.

Off-topic disclaimer

This is very simple implemented with single UDR routine for multiple possible data sources, based in the metadata of the stored procedure defined.

Look at this example for JDBC data sources with FB/Java: https://github.com/FirebirdSQL/fbjava/blob/master/examples/fbjava-example/util/code.sql#L35

External routines (aka UDR) are very democratic and there to be used with different languages. It's time for community to even do these useful routines and share them.

[livius2]

Why do you think about udr here? It does not support anytype parameter/returns declaration, built in function can. You know Substring have not limited lenght declaration, you can put there 10 chars and 10000 chars without the need for declaring as 10000 at the beginning. With udr you must. MaxValue can have Integer, numeric, double precision as parameters and return also different types, and so on.

And why it is not releated to this ticket? It have strong releation to it. Giving even one list of items and divide it into rows, is a CSV term. But if you go in limited implementation, then probably we will never remove the needs for procedures like above one.

[livius2]

And to be more releated to this ticket ;-) and asked questions by @dyemanov:

below you have explicite Field name `FIELD_1' and as type:

SELECT * FROM UNLITS(LIST, FIELD_1 AS INTEGER, FIELD_2 AS CURRENCY, FIIELD_3 AS VARCHAR(30))

Or why not derived table syntax? Name of the field after. Default can be unlist.

select * from unlist('1,2,3') AS U (MY_VALUE)

select * from unlist('1,2,3' AS INTEGER, ',') AS U (MY_VALUE)

It will allow in the future extensions as proposed by me.

And i opt to not trim values as default. If trim required, add another optional parameters or if spaces are always e.g. one, someone should put it in separator e.g. `, '.

select * from unlist('1, 2, 3' AS INTEGER, ', ') AS U (MY_VALUE)

[sim1984]

A Udr with a single entry point can be declared with different names and a different set of input and output parameters. Metadata of such sets of procedures can be obtained inside the implementation and used when parsing csv. This is exactly what was hinted at. Unlist is the reverse procedure of the list aggregate function. It has no other meanings. If you want a more complex analysis of blob fields, for example by regular expressions, ask for a separate ticket.

[livius2]

@sim1984

A Udr with a single entry point can be declared with different names and a different set of input and output parameters

Having built in function work in any database even if you have not DDL privilege. With udr, you must have tones of declaration for same thing with different types. And as in my last comment, supporting such things with udr is missconception. But this is my humble opinion, you can ignore it. I can only bring light for real needs from other side, user side.

[dyemanov]

Personally, I can accept multiple separators in UNLIST. This does not absolutely match the LIST function but could be useful if you don't know what particular separator was used in LIST. And it somewhat reminds the multi-character TRIM function being discussed in a parallel topic. However, the single-value separator list (e.g.',;|') is not going to work as we allow multi-character separators in LIST. But we may allow multiple comma-delimited separators -- <separator> [, <separator> ...]. Any other syntax suggestions? And opinions whether it should be supported at all?

More complex parsing surely does not fit this proposal.

[dyemanov]

I'm wondering, if we would allow unlist(...) as a derived table, wouldn't it be possible to also allow it as a query-expression (not 100% sure if that is the appropriate term, I always confuse the names SQL uses for this...):

where ID in(unlist(...))

This would be great and actually our users expect something like that (as it's simpler to use). However, the standard plays against us, or maybe I just fail to figure it out. This is possible by standard (for UNNEST):

select * from T1 JOIN UNNEST AS T2 ON ...

but IN / EXISTS predicates require a subquery inside parenthesis which must contain either SELECT or TABLE <name> or <table value constructor>, that's all. Do you want us to consider a non-standard extension? If so, should there be other exceptions from the standard rules?

[sim1984]

Personally, I can accept multiple separators in UNLIST. This does not absolutely match the LIST function but could be useful if you don't know what particular separator was used in LIST. And it somewhat reminds the multi-character TRIM function being discussed in a parallel topic. However, the single-value separator list (e.g.',;|') is not going to work as we allow multi-character separators in LIST. But we may allow multiple comma-delimited separators -- <separator> [, <separator> ...]. Any other syntax suggestions? And opinions whether it should be supported at all?

More complex parsing surely does not fit this proposal.

I'm not against many separators, I'm against unlist, which returns many columns according to complex rules.

Lots of comma delimiters look good.

UNLIST(<value> [, <separator> [, <separator> ...]] [<data type conversion>])

[sim1984]

It seems to me that there is no need to expand the syntax here. It's better to do it in the standard paradigm.

As for the returned column, RDB$VALUE can be used instead of VALUE. It's not very pretty, but you can always rename it (give it an alias).

[mrotteveel]

It seems to me that there is no need to expand the syntax here. It's better to do it in the standard paradigm.

As for the returned column, RDB$VALUE can be used instead of VALUE. It's not very pretty, but you can always rename it (give it an alias).

That would IMHO overload the meaning of RDB$ which generally means it is a system object. The column of UNLIST is not a system column.

[dyemanov]

We could (theoretically) allow missing column names, but only if they're not referenced explicitly. E.g.:

select * from unlist() as a; -- OK (with auto-generated column name returned via API)
select * from unlist() as a where unlist = 0; -- ERROR: unknown column "UNLIST"
select * from unlist() as a (b) where a.b = 0; -- OK
select * from (select * from unlist() as a); -- ERROR: no column name specified for column number 1 in derived table

[mrotteveel]

I'm wondering, if we would allow unlist(...) as a derived table, wouldn't it be possible to also allow it as a query-expression (not 100% sure if that is the appropriate term, I always confuse the names SQL uses for this...):

where ID in(unlist(...))

This would be great and actually our users expect something like that (as it's simpler to use). However, the standard plays against us, or maybe I just fail to figure it out. This is possible by standard (for UNNEST):

select * from T1 JOIN UNNEST AS T2 ON ...

but IN / EXISTS predicates require a subquery inside parenthesis which must contain either SELECT or TABLE <name> or <table value constructor>, that's all. Do you want us to consider a non-standard extension? If so, should there be other exceptions from the standard rules?

Hmm, on the one hand, I'm not a fan of deviating too much from the standard, on the other hand I hate unnecessary verbosity :)

As an example, BigQuery supports something like

value IN UNNEST(....)

(so even without the parentheses!)

For IN, they added a separate rule for UNNEST.

Maybe we should just follow the standard for now and just require a normal subquery for this case, and see if there is a demand to add a simplification. To be honest, a lot of the time, we all have the tendency to add too much gold-plating and other embellishments to new features instead of keeping it simple initially, as exemplified by some of the comments above asking for a wholly different feature than this (parsing CSV), and wanting to add options like supporting multiple separators (which IMHO would indicate bad data input, which should be addressed, instead of adding bandaids to work with that bad data).

[dyemanov]

I suppose multiple separators can be handled outside, e.g.:

select val from unlist(replace(str, ';', ',')) as tab (val)

but this is not so easy for trimming, if RETURNING clause specifies a non-string data type:

select trim(val) from unlist('1, 2, 3' returning int) as tab (val)

conversion error will be raised before TRIM is called.

[aafemt]

but IN / EXISTS predicates require a subquery inside parenthesis which must contain either SELECT or TABLE <name> or <table value constructor>, that's all.

In the case of ANSI standard TABLE <name> is not name of table but an introduce for any collection/rowset source. If UNLIST is a function - standard is fine with IN(TABLE(UNLIST(...))) because <multiset primary> includes as well <collection value expression> or even directly <routine invocation>.

[dyemanov]

Nope. What you describe belongs to <table reference> which follows after FROM. But <subquery> contains <query expression> which allows only TABLE <table or query name>.

i.e. you may write both FROM UNNEST (<collection value expression>) or FROM TABLE (<collection value expression>) but they cannot replace SELECT.

[mrotteveel]

but IN / EXISTS predicates require a subquery inside parenthesis which must contain either SELECT or TABLE <name> or <table value constructor>, that's all.

In the case of ANSI standard TABLE <name> is not name of table but an introduce for any collection/rowset source. If UNLIST is a function - standard is fine with IN(TABLE(UNLIST(...))) because <multiset primary> includes as well <collection value expression> or even directly <routine invocation>.

You're thinking of

<table function derived table> ::=
    TABLE <left paren> <collection value expression> <right paren>

Which is not the same as

<table subquery> ::=
    <subquery>

<subquery> ::=
    <left paren> <query expression> <right paren>

<query expression> ::=
    [ <with clause> ] <query expression body>
    [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

<query expression body> ::=
      <query term>
    | <query expression body> UNION [ ALL | DISTINCT ]
        [ <corresponding spec> ] <query term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ]
        [ <corresponding spec> ] <query term>

<query term> ::=
      <query primary>
    | <query term> INTERSECT [ ALL | DISTINCT ]
        [ <corresponding spec> ] <query primary>

<query primary> ::=
      <simple table>
    | <left paren> <query expression body>
        [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
      <right paren>

<simple table> ::=
      <query specification>
    | <table value constructor>
    | <explicit table>

<explicit table> ::=
    TABLE <table or query name>

<table or query name> ::=
      <table name>
    | <transition table name>
    | <query name>

All those elements of \<table or query name> are identifiers or identifier chains, not expressions.

[mrotteveel]

A \<table function derived table> can be used as a \<table primary> (i.e. in the from clause, like a derived table).

[aafemt]

I'm thinking of other branch: <in value list> - <row value expression> - <row value special case> - <nonparenthesized value expression primary> - <routine invocation> in the case of <SQL-invoked function> - <returns clause> - <returns table type>. I.e. if UNLIST returns "table type" it is allowed in IN condition directly though I don't know if the standard insists on this table to be a singleton result set.

[mrotteveel]

That derivation would not work for this case, as that is about the individual row values of the IN list, not for the entire IN list. Also the UNNEST wouldn't be classified as a routine invocation.

[aafemt]

But IN with single value is perfectly ok even if this value is a collection or row constructor, no?

[mrotteveel]

In other words, following that production would result in an IN-list with one element, and that doesn't match with what's needed because the single value you test against would never be equal to the element in the list. Also problematic on another level is the fact that Firebird doesn't support row-values (or at least, not with an arity greater than 1), and if it did, the requirement would be that the arity of the row value is deterministic, which it wouldn't be for this case.

[mrotteveel]

With the derivation you're proposing, use of ID IN (UNLIST('a,b,c' RETURNING CHAR(1)) would be equivalent to ID IN (('a', 'b', 'c')), which is never true even if ID is 'a', 'b', or 'c', it would only be true if ID is also a row value with value ('a', 'b', 'c'). It would not be equivalent to ID IN ('a', 'b', 'c') that you're thinking of.

[mrotteveel]

In short, we probably should park this (shortening use of UNLIST, not UNLIST itself) for now, and consider this later, before we shoot ourselves in the foot by introducing ambiguous or confusing syntax, or do something that will make introducing real row values later so much harder. And if we do want to shorten this, then following the example of BigQuery (i.e. ID IN UNLIST('a,b,c' RETURNING CHAR(1)); not parenthesized) is probably the only unambiguous option).

[AlexPeshkoff]

On 2/14/24 12:14, Dmitry Yemanov wrote:

I suppose multiple separators can be handled outside, e.g.:

select valfrom unlist(replace(str,';',','))as tab (val)

Yes. Use of replace here is absolutely logical.

but this is not so easy for trimming, if |RETURNING| clause specifies a non-string data type:

select trim(val)from unlist('1, 2, 3' returningint)as tab (val)

conversion error will be raised before |TRIM| is called.

Here I see something strange.

1. Spaces before first digit do not cause conversion error:

SQL> select cast(' 2' as int) from rdb$database;

       CAST

          2

2. unlist(... returning int) returns integer value, what means trim(int) ?

If list contains some more garbage except spaces one can always use replace to remove them. If something more complex is needed - processing can always be performed out of unlist() :

select userfunc(val)from unlist('1, 2, 3')as tab (val)

[dyemanov]

To summarize:

• [AS] <correlation_name> becomes mandatory. Derived column list may be mandatory or left optional if they're not referenced by name.
• Trimming and multiple separators are delayed.
• RETURNING is used for <data type conversion>.
• If <data type conversion> is omitted, VARCHAR(N) is returned, where N is implementation defined (some small value < 1000).
• Shortcut syntax may be considered, but limited to the IN predicate only.