The SELECT INTO operator assigns the query result to a list of variables. Only one row of data is returned for the query.
In the following query example, the SELECT statement outputs the COUNT(*) and MAX(c1) columns. The query results are assigned to variables @a and @b, respectively.
obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected (0.12 sec)
obclient>EXPLAIN SELECT COUNT(*), MAX(c1) INTO @a, @b FROM t1\G;
*************************** 1. row ***************************
Query Plan:
=========================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------------
|0 |SELECT INTO | |0 |37 |
|1 | SCALAR GROUP BY| |1 |37 |
|2 | TABLE SCAN |t1 |2 |37 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(*)], [T_FUN_MAX(t1.c1)]), filter(nil)
1 - output([T_FUN_COUNT(*)], [T_FUN_MAX(t1.c1)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)], [T_FUN_MAX(t1.c1)])
2 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
In the preceding example, the Outputs & filters section in the execution plan display shows in detail the output information of the SELECT INTO operator.
| Field | Description |
|---|---|
| output | The expressions by which the operator assigns values to the list of variables. |
| filter | The filter conditions of the operator. In this example, the condition is set to nil because no filter is configured for the SELECT INTO operator. |
