firebird
firebird copied to clipboard
FirebirdSQL
UNLIST table-value function
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:
- I suppose it's OK to imply
UNLISTas relation name if theASclause is omitted. But I'm not sure about the column name - should we also implyUNLIST, or maybe use keywordVALUEor maybe some other ideas?
select unlist from unlist(...)
select value from unlist(...)
select unlist.unlist from unlist(...)
select unlist.value from unlist(...)
-
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') -
If
<data type conversion>is omitted and the input argument is BLOB, should we return also BLOB or maybe VARCHAR(MAX)?
> 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.
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.
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.
As for
VALUEas 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>andVALUElook unsuitable.
UNLIST(...) [AS ALIAS[(COL1, ..., COLN)]] ?
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 ...])
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)
In other words
UNLIST(...) [AS] _correlation-name_ (_column-name_)
(i.e. not optional!)
Mark, while <correlation name> is mandatory in the SQL specification, <parenthesized derived column list> is optional.
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.
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.
Personally I think the
UNLISTshould require the correlation clause (including the column list), so there would be no need to specify defaults.
So far I tend to agree.
Though it is true, I wonder why (in both cases) we cannot generate them as usual "expression", "expression1", etc.
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
ASin a row will be confusing to read. I think sticking toRETURNINGis 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.
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.
They're somewhat separated -- UNLIST(A AS INT) AS T -- but maybe still not very friendly.
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
ASin a row will be confusing to read. I think sticking toRETURNINGis 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 ...
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>
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(...))
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 ;)?
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!
@livius2: looks like you are asking for CSV string to be parsed and returned in tabular format.
@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.
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.
@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.
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.
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)
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.
@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.
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.