[Feature] Support Lateral view syntax

[EmmyMiao87]

Search before asking

• I had searched in the issues 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?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[EmmyMiao87]

TODO:

1. table function from subquery

select * from (select k1, bitmap_union_string(v1) as new_v1 from table) lateral view explode_split(new_v1) t as tb_v1;

2. int result type of explode_split(string, ",")
3. reduce memory copy in output tuple of table function

[morningman]

Execution of Table Function Node

Table Function Node (TFN) contains one or more Table Functions, and its main logic is to expand the data received from the child nodes into multiple rows through the Table Function and return the data to the upper layer. The main execution process is as follows:

1. Get a row of data from the child node child row.
2. Pass the child row into each table function, and each table function will calculate and get a result set: S1, S2,...
3. Do the Cartesian product of child row and each result set and send it to the upper layer.

for example. Suppose the child row has 3 columns, k1, v1, v2:

k1	v1	v2
1	"a,b,c"	"4,5,6"

Two Table Functions: explod_split(v1,',') and explode_split(v2,',') respectively produce the following result sets:

explod_split(v1,',')
"a"
"b"
"c"

explode_split(v2,',')
"4"
"5"
"6"

The final Cartesian product result is:

k1	explod_split(v1,',')	explode_split(v2,',')
1	"a"	"4"
1	"a"	"5"
1	"a"	"6"
1	"b"	"4"
1	"b"	"5"
1	"b"	"6"
1	"c"	"4"
1	"c"	"5"
1	"c"	"6"

Table Function Interface Design

Because Doris does not currently support complex data types (such as Array), and Table Function is essentially an expression that returns an array type. So in this implementation, we will do special treatment for Table Function.

1. DummyTableFunctions

   This is a deception class. Its main purpose is to generate the scalar function signature of the table function on the BE side to facilitate query planning on the FE side, and to use the existing scalar function framework when the BE performs parameter expression calculations. In other words, in the planning and execution preparation stages of the entire query, Table Function is treated as a scalar function.

2. TableFunctionFactory

   The factory class of Table Function returns real Table Function instances based on the function name. Currently only supports matching Function by function name.

3. TableFunction

   The actual Table Function implementation class. Provide the following interfaces:

   1. prepare()/open()

      Some preparations, such as calculation of constant expressions, memory allocation for intermediate result sets, and so on.

   2. process(row)

      According to the incoming data (row), calculate the Table Function result set.

   3. reset()

      Because of the Cartesian product relationship between multiple Table Functions, all the result sets of a Function may be traversed multiple times. This method will set the cursor of the result set to the initial position in order to continue the traversal.

   4. get_value()

      Get the value of the position pointed by the current cursor.

   5. forward()

      Move the cursor forward, then you can call get_value() to get the next value

   6. close()

      The cleanup work after Function execution.

   The subclasses of TableFunction are concrete implementations of each Table Function. The following three functions are implemented in this issue:

   1. explode_split(str, delimiter)

      Split str into multiple strings according to delimiter.

   2. explode_json_array_xxx(json_str)

      Split a json array. According to the type of elements in the json array, xxx can be string, int or double

   3. explode_bitmap(bitmap)

      Expand a bitmap and return the value of each element in the bitmap.

Table Function Node Interface Design

Table Function Node inherits from Exec Node. There are the following interfaces:

1. init()

   Some initialization work, including obtaining Table Function objects, etc.

2. prepare()/open()

   Some preparations. For example, prepare()/open() of the call expression

3. get_next()

   Get a batch of results. Here, get_next() of the child node will be called to get the child node data first, then calculate the result of the Table Function, and return the data after the association.