Snowflake における GROUP BY / ORDER BY とカラムエイリアスの処理順

こんにちは、Snowflake でサポートエンジニアをやっている @indigo13love です。

まずはこの(あまりよろしくない)クエリを見てください。

create or replace table t1 (c1 varchar, c2 varchar) as
select * from values ('1', 'one'), ('1', 'odin');

select c1::int c1, array_agg(c2), grouping(c1) from t1
group by grouping sets (c1, c2)
order by grouping(c1) desc;

これを Snowflake で実行すると、結果はこうなります。

C1	ARRAY_AGG(C2)	GROUPING(C1)
1	["one", "odin"]	0
NULL	["one"]		1
NULL	["odin"]	1

GROUPING(C1) を DESC すなわち降順でソートしているにもかかわらず、なぜか 0 → 1 の順、つまり昇順にソートされています。

一見結果不正に見えますが、実は直感的でないだけで Snowflake は正しく処理しています。

この記事では、なぜこの出力が正しいかを説明していきます。

(本記事は所属する組織の公式見解ではなく、個人的なプラクティスの共有になります。)

前提: GROUPING() とは何か

その前に、まずは GROUPING() について説明したいと思います。

GROUPING() はカラム名やそれに類する式を引数に取り「その行がそのカラム(または式)によって集約されているか」を 0 (集約されている)、1 (集約されていない) のいずれかで返します。

https://docs.snowflake.com/ja/sql-reference/functions/grouping.html

GROUPING（<式>）は、 <式> でグループ化された行に対して 0 を返し、<式>でグループ化されていない行に対して 1 を返します。

前述のクエリにおける集約は GROUP BY GROUPING SETS (C1, C2) で行われており、これは C1 と C2 でそれぞれ GROUP BY した結果が UNION されて返ってくる形となります。

したがって、GROUPING(C1) は C1 で集約された行のみに 0 が返り、C2 で集約された行には 1 が返ります。

原因調査

直感的には結果不正バグに見えるので、実際なぜこんなことが起きているのか確認してみましょう。

Snowflake では、すべてのクエリについて Query Profile を取得し、実行計画のビジュアライゼーションを提供しています。

まずは、当該クエリの Query Profile を見てみましょう。

シンプルなクエリなので、わかりやすい構造になっています。

TableScan がテーブルからデータを読み込み、GroupingSets が集約し (GROUP BY)、Sort がソートし (ORDER BY)、Result が結果を返しています。

また、この画面では Result が選択されているので、それぞれ TO_NUMBER(T1.C1), ARRAY_AGG(T1.C2), GROUPING_ID(T1.C1) が結果として返されていることがわかります。

では、それぞれのステップがどう動作しているかを確認してみましょう。それぞれのボックスをクリックすることで、その操作の詳細が確認できます。

Sort:

GroupingSets:

さて、間違い探しの時間です。ちなみにサポートエンジニアをやるにあたって間違い探しは非常に重要な能力で、鍛えていくとブラウザの画面上から差分が浮かび上がってきたり、適当にログをスクロールしてるとおかしな行が浮かび上がってきたりするようになります。

これは結構簡単な問題でしたね。

Sort (=ORDER BY) はソートキーとして TO_NUMBER(T1.C1) すなわち t1.c1::int を使っています。

しかし、Results の GROUPING() 関数と、GroupingSets (=GROUP BY) は T1.C1 すなわちキャスト前の値を使っています。

つまり、前述のクエリは、本質的には下記と同一のクエリだということがわかりました。

select
    t1.c1::int,
    array_agg(t1.c2),
    grouping(t1.c1)
from t1
group by grouping sets (t1.c1, t1.c2)
order by grouping(t1.c1::int) desc;

したがって、当該クエリにおいて ORDER BY 句内の GROUPING() の引数　t1.c1::int は、GROUP BY で使用されている t1.c1 とは異なるため、全行が「t1.c1::int では集約されていない」という判定となり、全行に対して 1 が返るため、ソート順不定となり、前述の間違っているように見える結果が返った、ということになります。

つまり Snowflake では、

カラム名 → GROUP BY → カラムエイリアス → ORDER BY

の順に解決されているであろうことが推測されます。

原因、それは…

同名のカラムエイリアス、すなわち c1::int c1 です。

これにより、c1 が t1.c1 を指しているのか t1.c1::int を指しているのかわからなくなってしまっています。

例えば、MySQL でこれを実行すると c1 が ambiguous であるという警告が出ます。

mysql> select cast(c1 as signed) c1, group_concat(c2) from t1 group by c1;
+------+------------------+
| c1   | group_concat(c2) |
+------+------------------+
|    1 | one,odin         |
+------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1052 | Column 'c1' in group statement is ambiguous |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

今回のようにコンパイラが混乱するだけでなく、SQL の可読性自体にもかなり悪い影響があるので、なるべく同名のカラムエイリアスを回避したほうがいいでしょう。

解決策

最もシンプルな解決策は「カラムエイリアスを使わない」か、「別名のカラムエイリアスを使う」ことです。

カラムエイリアスを使わない

select c1::int, array_agg(c2), grouping(c1::int) from t1
group by grouping sets (c1::int, c2)
order by grouping(c1::int) desc;

このケースでは、すべてのタイミングで c1 の int へのキャストが実行されるので、問題なく一貫性のある処理になります。

別名のカラムエイリアスを使う

select c1::int c1_new, array_agg(c2), grouping(c1_new) from t1
group by grouping sets (c1_new, c2)
order by grouping(c1_new) desc;

このケースでは、c1_new が解決されるまで GROUP BY を解決できなくなるため、処理順が

カラム名 → カラムエイリアス → GROUP BY → ORDER BY

に変わり、問題が解決します。

カラムの型を変える

また、そもそも読み出す際にキャストに頼ること自体、データの一貫性の観点でもオプティマイザの観点でもあまりいいプラクティスではないので、そもそも c1 カラム自体を int にするべきでしょう。

例えば、c1 が varchar である以上、このカラムに数値文字列だけが格納されているかどうかは Snowflake のレベルではまったく保証されていません。

ちなみに…

おそらくご推察の通り、この動作は実装依存です。Snowflake がたまたまこのように動作しているというだけで、他のデータベースも同じ動作をするとは限りません。(ちなみに Oracle は同じ動作)

例えば、PostgreSQL は、前述のクエリに対して直感的な結果を返します。

postgres=# create table t1 (c1 varchar(10), c2 varchar(10));
CREATE TABLE
postgres=# insert into t1 values ('1', 'one'), ('1', 'odin');
INSERT 0 2
postgres=# select c1::int c1, array_agg(c2), grouping(c1) from t1
group by grouping sets (c1, c2)
order by grouping(c1) desc;
 c1 | array_agg  | grouping
----+------------+----------
    | {odin}     |        1
    | {one}      |        1
  1 | {one,odin} |        0
(3 rows)

まとめ

この記事の事象自体は、おそらくそんなに遭遇することのないニッチな動作についてのノウハウなので、知っていて損はないですが、さほどこれ自体が重要な情報ではありません。

この記事で伝えたかったことは、

• 一見、結果不正っぽくても正しく動作している可能性がある(結構ある)
• Query Profile はパフォーマンスの問題だけでなく結果不正の問題でも役に立つ
• 人間サイドの常識を信用せず、クエリコンパイラの気持ちになって考える
• 間違い探し力は超重要

ということです。

結果不正の調査だけずっとやっていたいぐらい結果不正の調査が大好物なので、皆様からのケース起票お待ちしております。