Subquery
Subqueryは、別のクエリ（通常はSELECT文）内にネストされたSQLクエリです。外部クエリにデータや条件を提供するために、SELECT、FROM、WHERE、またはHAVING句で使用できます。subqueryの使用により、単一のクエリ内でより複雑な問題を解決できるため、SQLクエリはより柔軟で強力になります。
subqueryの重要な特徴は以下の通りです：
-
Subqueryの位置：SubqueryはWHERE句、HAVING句、FROM句など、複数のSQL句に配置できます。SELECT、UPDATE、INSERT、DELETE文や、比較演算子（=、>、<、<=など）およびIN、EXISTSなどの式演算子と一緒に使用できます。
-
メインクエリとSubqueryの関係：Subqueryは別のクエリ内にネストされたクエリです。外部クエリはメインクエリと呼ばれ、内部クエリはsubqueryと呼ばれます。
-
実行順序：subqueryとメインクエリの間に相関がない場合、通常subqueryが最初に実行されます。相関がある場合、パーサーは必要に応じてリアルタイムでどのクエリを最初に実行するかを決定し、それに応じてsubqueryの出力を使用します。
-
括弧の使用：Subqueryは別のクエリ内にネストされていることを区別するために、括弧で囲む必要があります。
以下では、テーブルt1とt2および関連するSQLを使用して、subqueryの基本的な特徴と使用法を紹介します。テーブル作成文は以下の通りです：
create table t1
(
c1 bigint,
c2 bigint
)
DISTRIBUTED BY HASH(c1) BUCKETS 3
PROPERTIES ("replication_num" = "1");
create table t2
(
c1 bigint,
c2 bigint
)
DISTRIBUTED BY HASH(c1) BUCKETS 3
PROPERTIES ("replication_num" = "1");
サブクエリの分類
サブクエリが返すデータの特性に基づく分類
サブクエリは、返すデータの特性に基づいてスカラサブクエリと非スカラサブクエリに分類できます：
1. スカラサブクエリ
常に単一の値を返すサブクエリ（基本的に1行1列のRelationと同等）。サブクエリがデータを返さない場合、NULL値を返します。スカラサブクエリは理論的には単一値式が許可される場所であればどこにでも記述できます。
2. 非スカラサブクエリ
Relationを返すサブクエリ（スカラサブクエリの戻り値とは異なり、このRelationは複数の行と列を含むことができます）。サブクエリがデータを返さない場合、空集合（0行）を返します。非スカラサブクエリは理論的にはrelation（集合）が許可される場所であればどこにでも記述できます。
以下の例では、スカラサブクエリと非スカラサブクエリを示しています（括弧内の2つのサブクエリについて、t2が空のテーブルの場合、2つのサブクエリが返す結果は異なります）：
-- Scalar subquery, when t2 is an empty table, the subquery returns the scalar value null
select * from t1 where t1.c1 > (select sum(t2.c1) from t2);
-- Non-scalar subquery, when t2 is an empty table, the subquery returns an empty set (0 rows)
select * from t1 where t1.c1 in (select t2.c1 from t2);
外部クエリの列を参照するかどうかに基づく分類
サブクエリは、外部クエリの列を参照するかどうかに基づいて、相関サブクエリと非相関サブクエリに分類できます：
1. 非相関サブクエリ
外部クエリの列を参照しないサブクエリ。非相関サブクエリは多くの場合、独立して計算でき、外部クエリが使用するための対応する結果を一度だけ返します。
2. 相関サブクエリ
メインクエリ（外部クエリとも呼ばれる）から1つ以上の列を参照するサブクエリ（参照される外部列は多くの場合、サブクエリのWHERE条件内にあります）。相関サブクエリは多くの場合、外部に関連付けられたテーブルに対するフィルタリング操作と見なすことができます。外部テーブルの各データ行に対して、サブクエリが計算され、対応する結果を返します。
以下の例は、相関サブクエリと非相関サブクエリを説明しています：
-- Correlated subquery, the subquery internally uses the column t1.c2 from the outer table
select * from t1 where t1.c1 in (select t2.c1 from t2 where t2.c2 = t1.c2);
-- Non-correlated subquery, the subquery internally does not use any columns from the outer table t1
select * from t1 where t1.c1 in (select t2.c1 from tt2);
Dorisでサポートされるサブクエリ
Dorisはすべての非相関サブクエリをサポートし、以下のように相関サブクエリを部分的にサポートします：
-
WHERE句とHAVING句での相関スカラーサブクエリをサポートします。 -
WHERE句とHAVING句での相関IN、NOT IN、EXISTS、NOT EXISTS非スカラーサブクエリをサポートします。 -
SELECTリストでの相関スカラーサブクエリをサポートします。 -
ネストされたサブクエリについて、Dorisは直接の親クエリとの相関のみをサポートし、親を超えた外部クエリとのレベル横断的な相関はサポートしません。
相関サブクエリの制限事項
相関スカラーサブクエリの制限事項
-
相関条件は等価条件である必要があります。
-
サブクエリの出力は、GROUP BY句なしの単一の集約関数の結果である必要があります。
-- Single aggregate function without GROUP BY, supported
select * from t1 where t1.c1 < (select max(t2.c1) from t2 where t1.c2 = t2.c2);
-- Equivalent rewritten SQL as follows:
select 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;
-- Non-equality condition, not supported
select * from t1 where t1.c1 = (select max(t2.c1) from t2 where t1.c2 > t2.c2);
-- No aggregate function, not supported
select * from t1 where t1.c1 = (select t2.c1 from t2 where t1.c2 = t2.c2);
-- With aggregate function but includes GROUP BY, not supported
select * from t1 where t1.c1 = (select max(t2.c1) from t2 where t1.c2 = t2.c2 group by t2.c2);
相関 (NOT) EXISTS 副問合せの制限事項
- 副問合せに OFFSET と LIMIT の両方を含めることはできません。
-- With LIMIT but no OFFSET, supported
select * from t1 where exists (select t2.c1 from t2 where t1.c2 = t2.c2 limit 2);
-- Equivalent rewritten SQL as follows:
select * from t1 left semi join t2 on t1.c2 = t2.c2;
-- With OFFSET and LIMIT, not supported
select * from t1 where exists (select t2.c1 from t2 where t1.c2 = t2.c2 limit 2, 3);
相関 (NOT) IN サブクエリの制限事項
-
サブクエリの出力は単一列である必要があります。
-
サブクエリにLIMITを含めることはできません。
-
サブクエリに集約関数やGROUP BY句を含めることはできません。
-- Supported subquery
select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2);
-- Equivalent rewritten SQL as follows:
select * from t1 left semi join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2;
-- Subquery output is multiple columns, not supported
select * from t1 where (t1.a, t1.c) in (select t2.c1, t2.c from t2 where t1.c2 = t2.c2);
-- Subquery with LIMIT, not supported
select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2 limit 3);
-- With GROUP BY clause, not supported
select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2 group by t2.c1);
-- With aggregate function, not supported
select * from t1 where t1.c1 in (select sum(t2.c1) from t2 where t1.c2 = t2.c2);
ネストしたサブクエリの制限事項
現在、直接の親クエリと直接相関するサブクエリのみがサポートされています。親クエリの外側の層との相関はサポートされていません。
以下の作成文を持つ別のテーブルt3があると仮定します：
create table t3
(
c1 bigint,
c2 bigint
)
DISTRIBUTED BY HASH(c1) BUCKETS 3
PROPERTIES ("replication_num" = "1");
-
サブクエリが直接の親クエリからの列のみを使用する場合にサポートされます：
select
t1.c1
from
t1
where not exists (
select
t2.c1
from
t2
where not exists (
select
t3.c1
from
t3
where
t3.c2 = t2.c2
) and t2.c2 = t1.c2
); -
最も内側のサブクエリが直接の親クエリの列
t2.c2と最も外側のクエリの列t1.c1の両方を使用する場合はサポートされません：select
t1.c1
from
t1
where not exists (
select
t2.c1
from
t2
where not exists (
select
t3.c1
from
t3
where
t3.c2 = t2.c2 and t3.c1 = t1.c1
)
);
Mark Join
where条件において、(not) inや(not) existsを使用したサブクエリと他のフィルタリング条件で構成されるor関係の句は、正しい結果を生成するために特別な処理が必要です。以下に例を示します：
select
t1.c1,
t1.c2
from t1
where exists (
select
t2.c1
from t2
where
t1.c2 = t2.c2
) or t1.c1 > 0;
このSQLのexists句をleft semi joinを使って直接実装した場合、left semi joinのセマンティクスに従って、t1.c2 = t2.c2を満たすt1の行のみが出力されます。しかし、実際にはt1.c1 > 0という条件を満たす行も出力されるべきです。これを実現するために、Mark Joinのメカニズムが導入されます。
right semi joinは類似していますが、左右のテーブルが異なります。ここでは、left semi joinを例として使用します。
SQLの例は以下の通りです：
-- This SQL cannot be executed and is only for demonstration purposes
select
tx.c1,
tx.c2
from
(
select
t1.c1,
t1.c2,
mark_join_flag
from
t1 left (mark) semi join t2 on t1.c2 = t2.c2
) tx
where
tx.mark_join_flag or tx.c1 > 0;
Mark Joinと通常のleft semi joinの違いは、通常のleft semi joinが条件を満たす左側のテーブルの行を直接出力するのに対し、Mark Joinは元の左側のテーブルに追加のフラグ列（例ではmark_join_flag）を付けて出力することです。このフラグ列はtrue、false、またはnullの値を取ります。フラグの値はjoin条件式t1.c2 = t2.c2によって決定され、各行に対応するフラグ値が設定されます。フラグ値の計算は以下の表に示されています：
| t1.c1 | t2.c1 | mark_join_flag |
|---|---|---|
| 1 | 1 | TRUE |
| 1 | 2 | FALSE |
| 1 | NULL | NULL |
| NULL | 1 | NULL |
| NULL | NULL | NULL |
このフラグを使用することで、whereフィルタリング条件をwhere mark_join_flag or t1.c1 > 0として書き直すことで、正しい結果を取得できます。
使用上の注意点
スカラーサブクエリの出力は単一の値である必要があるため、サブクエリが複数行のデータを返す場合はランタイムエラーが報告されます。
相関スカラーサブクエリについて
相関量詞サブクエリを使用する場合、相関条件を満たすサブクエリが複数行のデータを返すと、ランタイムエラーが報告されます。
以下のSQLの例を参照してください：
-- If there are more than 1 row in the t2 table that satisfies t1.c2 = t2.c2 in the associated scalar subquery, a runtime error will be reported
select t1.*, (select t2.c1 from t2 where t1.c2 = t2.c2) from t1;
-- Example error message
ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT][E33] correlate scalar subquery must return only 1 row
非相関スカラサブクエリの場合
Dorisは実行時にassert num rowsオペレータを追加します。サブクエリが複数行のデータを返す場合、実行時エラーが報告されます。
以下のSQLの例を参照してください：
-- Non-correlated scalar subquery, will report an error if table t2 has more than 1 row of data
select t1.*, (select t2.c1 from t2) from t1;
-- Example error message
ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[CANCELLED]Expected EQ 1 to be returned by expression