子查询

子查询（Subquery）是嵌套在另一个查询（通常是 SELECT 语句）中的 SQL 查询。它可以用在 SELECT、FROM、WHERE 或 HAVING 子句中，为外部查询提供数据或条件。子查询的使用使得 SQL 查询变得更加灵活和强大，因为它们允许我们在单个查询中解决更复杂的问题。

子查询的一些重要特征如下：

1. 子查询的位置：子查询可以放在多个 SQL 子句中，如 SELECT、WHERE、HAVING 和 FROM 子句。它们可以与 SELECT、UPDATE、INSERT、DELETE 语句以及表达式运算符（如比较运算符 =、>、<、<=，以及 IN、EXISTS 等）一起使用。
2. 主查询与子查询的关系：子查询是嵌套在另一个查询内部的查询。外部查询被称为主查询，而内部查询则被称为子查询。
3. 执行顺序：当子查询是独立的（即不依赖于外部查询的结果）时，它通常首先执行。当存在相关性时，解析器会根据需要实时决定先执行哪个查询，并相应地使用子查询的输出。
4. 括号的使用：子查询必须用括号括起来，以区分它们是嵌套在另一个查询中。

下面我们分别用 t1 和 t2 表以及相关 SQL，介绍子查询的基本特性和用法。建表语句如下：

1create table t1
2(
3    c1 bigint, 
4    c2 bigint
5)
6DISTRIBUTED BY HASH(c1) BUCKETS 3
7PROPERTIES ("replication_num" = "1");
8
9create table t2
10(
11    c1 bigint, 
12    c2 bigint
13)
14DISTRIBUTED BY HASH(c1) BUCKETS 3
15PROPERTIES ("replication_num" = "1");

子查询的分类

按照子查询返回数据的特性分类

按照子查询返回数据的特性，可分为标量和非标量子查询：

1. 标量子查询

子查询一定返回一个单一的值（本质上等价于一个一行一列的 Relation）。如果子查询没有任何数据返回，则返回 NULL 值。标量子查询理论上可以出现在任何允许单值表达式出现的地方。

2. 非标量子查询

子查询返回一个 Relation（不同于标量子查询的返回值，该 Relation 可包含多行多列）。如果子查询没有任何数据返回，则返回空集（0 行）。非标量子查询理论上可以出现在任何允许关系（集合）出现的地方。

以下分别对标量和非标量子查询举例说明（对于两个括号内的子查询，当 t2 是空表时，两个子查询返回结果不同）。

1-- 标量子查询，当 t2 是空表时，子查询返回标量值 null  
2select * from t1 where t1.c1 > (select sum(t2.c1) from t2);  
3  
4-- 非标量子查询，当 t2 是空表时，子查询返回 empty set (0 rows)  
5select * from t1 where t1.c1 in (select t2.c1 from t2);

按照子查询是否引用了外部查询的列分类

按照子查询是否引用了外部查询的列，可分为关联子查询和非关联子查询

1. 非关联子查询

子查询没有引用外部查询的任何列。非关联子查询常常可以独立运算，并一次性返回相应结果供外部查询使用。

2. 关联子查询

子查询引用了主查询（又称为外部查询）的一个或多个列（引用的外部列常常在子查询的 WHERE 条件中）。关联子查询常常可以看做是对外部关联的表的一个过滤操作，因为对于外部表的每一行数据，都会对子查询进行运算，并返回相应结果。

以下分别对关联和非关联子查询举例说明：

1-- 关联子查询，子查询内部使用了外部表的列 t1.c2  
2select * from t1 where t1.c1 in (select t2.c1 from t2 where t2.c2 = t1.c2);  
3  
4-- 非关联子查询，子查询内部没有使用任何外部表 t1 的列  
5select * from t1 where t1.c1 in (select t2.c1 from t2);

PALO 支持的子查询

PALO 支持所有的非关联子查询，对关联子查询（有部分限制）的支持如下：

• 支持在 WHERE和 HAVING子句中的关联标量子查询。
• 支持在 WHERE和 HAVING子句中的关联的 IN、NOT IN、EXISTS、NOT EXISTS 非标量子查询。
• 支持在SELECT列表中的关联标量子查询。
• 对于嵌套子查询，PALO 只支持子查询关联到自己的直接父查询，不支持跨层级关联到父查询的更外层查询。

关联子查询的限制

关联的标量子查询的限制

• 关联条件必须是等值条件。

• 子查询的输出必须是单个聚合函数的结果，且没有 group by 子句。

  SQL 复制

  1-- 单个聚合函数，且无 group by，支持  
  2select * from t1 where t1.c1 < (select max(t2.c1) from t2 where t1.c2 = t2.c2);  
  3  
  4-- 等价改写的 SQL 如下：  
  5select t1.* from t1 inner join (select t2.c2 as c2, max(t2.c1) as c1 from t2 group by t2.c2) tx on t1.c1 < tx.c1 and t1.c2 = tx.c2;  
  6  
  7-- 非等值条件，不支持  
  8select * from t1 where t1.c1 = (select max(t2.c1) from t2 where t1.c2 > t2.c2);  
  9  
  10-- 没有聚合函数，不支持  
  11select * from t1 where t1.c1 = (select t2.c1 from t2 where t1.c2 = t2.c2);  
  12  
  13-- 有聚合函数，但包含 group by，不支持  
  14select * from t1 where t1.c1 = (select max(t2.c1) from t2 where t1.c2 = t2.c2 group by t2.c2);

关联的 (not) exists 子查询的限制

• 子查询不能同时有offset和 limit。

  SQL 复制

  1-- 带 limit 但无 offset，支持  
  2select * from t1 where exists (select t2.c1 from t2 where t1.c2 = t2.c2 limit 2);  
  3  
  4-- 等价改写 SQL 如下：  
  5select * from t1 left semi join t2 on t1.c2 = t2.c2;  
  6  
  7-- 带 offset 和 limit，不支持  
  8select * from t1 where exists (select t2.c1 from t2 where t1.c2 = t2.c2 limit 2, 3);

关联的 (not) in 子查询的限制

• 子查询的输出必须是单个列。
• 子查询不能带有limit。

• 子查询不能带有聚合函数或group by子句。

  SQL 复制

  1-- 支持的子查询  
  2select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2);  
  3  
  4-- 改写的等价 SQL 如下：  
  5select * from t1 left semi join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2;  
  6  
  7-- 子查询输出为多列，不支持  
  8select * from t1 where (t1.a, t1.c) in (select t2.c1, t2.c from t2 where t1.c2 = t2.c2);  
  9  
  10-- 子查询带 limit，不支持  
  11select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2 limit 3);  
  12  
  13-- 带有 group by 子句，不支持  
  14select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2 group by t2.c1);  
  15  
  16-- 带有聚合函数，不支持  
  17select * from t1 where t1.c1 in (select sum(t2.c1) from t2 where t1.c2 = t2.c2);

嵌套子查询的限制

目前只支持子查询关联到自己直接的父查询，不支持关联到父查询的更外层查询。

假设还有一个t3表，其建表语句如下：

1create table t3  
2(  
3    c1 bigint,   
4    c2 bigint  
5)  
6DISTRIBUTED BY HASH(c1) BUCKETS 3  
7PROPERTIES ("replication_num" = "1");

• 可以支持当子查询只使用了自己直接父查询的列

  SQL 复制

  1select   
  2    t1.c1   
  3from   
  4    t1   
  5where not exists (  
  6    select   
  7        t2.c1   
  8    from   
  9        t2   
  10    where not exists (  
  11        select   
  12            t3.c1   
  13        from   
  14            t3   
  15        where   
  16            t3.c2 = t2.c2  
  17    ) and t2.c2 = t1.c2  
  18);

• 不支持当最内层的子查询使用了直接父查询的列t2.c2，并使用了最外层查询的列t1.c1。

  SQL 复制

  1select   
  2    t1.c1   
  3from   
  4    t1   
  5where not exists (  
  6    select   
  7        t2.c1   
  8    from   
  9        t2   
  10    where not exists (  
  11        select   
  12            t3.c1   
  13        from   
  14            t3   
  15        where   
  16            t3.c2 = t2.c2 and t3.c1 = t1.c1  
  17    )  
  18);

Mark Join

在 where 条件中，一些由 (not) in 或 (not) exists 的子查询和其他过滤条件组成的 or 关系子句，需要特殊处理才能生成正常结果。举例如下：

1select 
2    t1.c1, 
3    t1.c2 
4from t1 
5where exists (
6    select 
7        t2.c1 
8    from t2 
9    where 
10        t1.c2 = t2.c2
11    ) or t1.c1 > 0;

这个 SQL 中的 exists 子句部分如果直接使用 left semi join，根据 left semi join 的语义，将会只输出 t1 中满足 t1.c2 = t2.c2 的行。然而，实际满足 t1.c1 > 0 这个条件的行也应该输出。为了达到这个目的，引入了 Mark Join 的机制。

备注： right semi join 类似，只是左右表不同。在这里，我们用 left semi join 作为示例。

示例 SQL 如下：

1-- 此 SQL 不能实际执行，只作为演示使用  
2select   
3    tx.c1,   
4    tx.c2   
5from   
6    (  
7        select   
8            t1.c1,   
9            t1.c2,   
10            mark_join_flag   
11        from   
12            t1 left (mark) semi join t2 on t1.c2 = t2.c2  
13    ) tx  
14where   
15    tx.mark_join_flag or tx.c1 > 0;

Mark Join 相较于普通的 left semi join，区别在于普通的 left semi join 会直接输出左表满足条件的行，而 Mark Join 则输出原始的左表加上一个值为 true、false或 null 的标志位列（示例中的 mark_join_flag 标志）。标志位的值通过 join 条件表达式 t1.c2 = t2.c2 决定，每一行都对应一个标志位值。标志位值的计算参见下表：

有了这个标志位之后，where 过滤条件就可以改写为 where mark_join_flag or t1.c1 > 0，从而得到正确结果。

常见问题

由于标量子查询的输出必须是一个单值，如果子查询返回的数据量超过一条记录，将会报告运行时错误。

对于关联的标量子查询

在使用关联标量子查询时，如果满足关联条件的子查询返回的数据量超过一条记录，将会报告运行时错误。

请参考以下 SQL 示例：

1-- 关联的标量子查询，如果 t2 表中满足 t1.c2 = t2.c2 的数据多于 1 条，则会报运行时错误
2select t1.*, (select t2.c1 from t2 where t1.c2 = t2.c2) from t1;
3
4-- 报错信息样例如下 
5ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT][E33] correlate scalar subquery must return only 1 row

对于非关联的标量子查询

PALO 会在运行时添加一个assert num rows算子，如果子查询返回的数据量超过一条记录，将会报告运行时错误。

请参考以下 SQL 示例：

1-- 非关联的标量子查询，如果 t2 表有多于 1 条的数据，则可能报运行时错误  
2select t1.*, (select t2.c1 from t2) from t1;  
3
4-- 报错信息样例如下  
5ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[CANCELLED]Expected EQ 1 to be returned by expression