1. 背景

你是否也遇到示例中一个多行多列表格数据的输出场景，却苦于只能使用多 UNION ALL 的语法来实现。工程实践中，我们也注意到有用户程序生成的 SQL 存在巨大 UNION ALL，这种类型的 SQL 对 OceanBase 内核是不友好的，通常会导致内存和 CPU 占用上涨。

OceanBase> SELECT 1, 2, 3 FROM DUAL UNION ALL SELECT 4, 5, 6 FROM DUAL UNION ALL SELECT 7, 8, 9 FROM DUAL; 
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        4 |        5 |        6 |
|        7 |        8 |        9 |
+----------+----------+----------+

OceanBase 在 V4.2.1 版本实现了 Values Statement 语法，它返回一行或多行作为表，可用作独立的 SQL 语句。换句话说，Values Statement提供了一种表数据的构造方式。例如：

OceanBase> VALUES ROW(1, 2, 3), ROW(4, 5, 6), ROW(7, 8, 9);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        4 |        5 |        6 |
|        7 |        8 |        9 |
+----------+----------+----------+

2. 语法

其标准语法如下：

VALUES row_constructor_list [ORDER BY column_designator] [LIMIT number]

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

value_list:
    value[, value][, ...]

column_designator:
    column_index

Values Statement 语句由 VALUES 关键字组成，后跟一个或多个行构造函数的列表，以逗号分隔。行构造函数由 ROW() 行构造函数子句组成，该子句带有一个包含在括号中的一个或多个标量值的值列表。值可以是任何 MySQL 数据类型或标量值的表达式。最终组成多列多行的一张"表"，“表”的列默认指定了隐式命名：column_0、column_1、column_2 等，始终以 0 开头。如下面的一个简单例子：

OceanBase> VALUES ROW(1, -2, 3), ROW(5, 7, 9), ROW(4, 6, 8);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |       -2 |        3 |
|        5 |        7 |        9 |
|        4 |        6 |        8 |
+----------+----------+----------+

Values Statement 一列中允许使用不同类型的数值，如下所示：

OceanBase> VALUES ROW(1, -2, 'oceanbase'), ROW('a', 5, 'mysql'), ROW('222asda', 8, date'2020-12-01');
+----------+----------+--------------+
| column_0 | column_1 | column_2     |
+----------+----------+--------------+
| 1        |       -2 | oceanbase    |
| a        |        5 | mysql        |
| 222asda  |        8 | 2020-12-01   |
+----------+----------+--------------+

3. 使用场景

1. 创建临时表格数据，可以使用一个表格数据替换多 UNION ALL。

例如：

SELECT 1, 2, 3 UNION ALL SELECT 4, 5, 6 UNION ALL SELECT 7, 8, 9; 
# 使用 Values Statement 表示临时表格数据为：
VALUES ROW(1, 2, 3), ROW(4, 5, 6), ROW(7, 8, 9);

例如：

CREATE TABLE t1 (type_a int, type_b int, type_c int);
SELECT CASE WHEN V.c1 = 1 AND t1.type_a = 1 THEN 'type_a'
            WHEN V.c1 = 2 AND t1.type_b = 1 THEN 'type_b'
            WHEN V.c1 = 3 AND t1.type_c = 1 THEN 'type_c'
            ELSE NULL
       END AS col_type
       FROM t1, (SELECT 1 AS c1 UNION ALL SELECT 2 UNION ALL SELECT 3) V;
# 使用 Values Statement 表示临时表格数据为：
SELECT CASE WHEN V.column_0 = 1 AND t1.type_a = 1 THEN 'type_a'
            WHEN V.column_0 = 2 AND t1.type_b = 1 THEN 'type_b'
            WHEN V.column_0 = 3 AND t1.type_c = 1 THEN 'type_c'
            ELSE NULL
       END AS col_type
       FROM t1, (VALUES ROW(1), ROW(2), ROW(3)) V;

1. 快速创建表或视图。

例如：

CREATE TABLE t1 AS VALUES ROW(1, 2, 3), ROW(4, 5, 6), ROW(7, 8, 9);
CREATE VIEW v1 AS VALUES ROW(1, 2, 3), ROW(4, 5, 6), ROW(7, 8, 9);

4. Values Statement 能力和展望

除此之外，OceanBase V4.2.x 会将 Values Statement 用于优化器内部改写，优化大 IN/UNION 场景的执行性能，降低 SQL 资源消耗。例如：

1. 将谓词中IN做改写，将过滤谓词变成了semi join，进一步改写为inner join，有了更多优化改写 的可能性。

SELECT * FROM t1 WHERE t1.c1 IN (1, 2, 3, 4);
# 内核改写为：
SELECT * FROM t1 SEMI JOIN (VALUES ROW(1), ROW(2), ROW(3), ROW(4)) V WHERE t1.c1 = V.column_0;

1. 对于大量的常量union all/union 也能够改写

SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
# 内核改写为：
VALUES ROW(1, 2, 3)