EmmyMiao87 opened a new issue #6746: URL: https://github.com/apache/incubator-doris/issues/6746
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Description The functions in SQL can be divided into the following categories according to the number of input and output rows: 1. Scalar function 2. Aggregate function 3. Table Function: One line of input corresponds to multiple lines of output. Doris currently supports the first two functions, but does not support the third. Table Function is usually used in operations such as column to row. For example, the following table data: | id | names | |---|---| | 1 | a, b, c | | 2 | d, e, f | If you want to output multiple comma-separated data in the names column in rows as follows: | id | name | |---|---| | 1 | a | | 1 | b | | 1 | c | | 2 | d | | 2 | e | | 2 | f | You need to use the Table Function, which converts one row of data into multiple rows. Common Table Function, such as explode in hive, or unnest in presto, can convert array or list types into multi-line output. like: `select explode(array_col1) from tbl;` # Design ## Syntax ``` lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias) fromClause: FROM baseTable (lateralView) ``` ### Multiple Lateral Views A FROM clause can have multiple LATERAL VIEW clauses. Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW. For example, the following could be a valid query: ``` SELECT * FROM exampleTable LATERAL VIEW explode_split(col1, ",") myTable1 AS myCol1 LATERAL VIEW explode(col2, ",") myTable2 AS myCol2; ``` ## TableFunctionNode The result of Table Function is multiple rows, which can also be regarded as a relational table. So we need to implement a new TableFunctionNode. It contains the TableFunction that needs to be processed. For each row of TableFunctionNode input, execute the corresponding TableFunction, and then perform the correlation operation and output. ``` MySQL [test]> explain select k1, e1, e2 from test_explode lateral view explode_split (col1, ",") tmp as e1 lateral view explode_split (col2, ",") tmp as e2; +---------------------------------------------------------------------------+ | Explain String | +---------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS:`k1` | `e1` | 'e2' | | | | RESULT SINK | | | | 1:TABLE FUNCTION NODE | | | table function: explode_split(`col1`, ','), explode_split('col2', ',') | | | | | 0:OlapScanNode | | TABLE: test_explode | +---------------------------------------------------------------------------+ ``` ### Use case _No response_ ### Related issues _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
