🖇️

UNIONする時の罠にハマった話(Snowflake)

に公開
SQL
Snowflake
tech

この記事は Snowflake Advent Calendar 2025 の4日目の記事です。

箸休め的にこんな仕様があるんだ〜という世間話になればいいかなと思います。（ゆるい記事なので気を抜いて読んでください）

とはいえ少しくらいは役に立つと嬉しいので、自身が参加しているプロジェクトでUNIONを使う際にベストプラクティス沿って適用できているっけ？というのを振り返る機会にしていただければ幸いです。

※UNION以外のちょっとしたSnowflakeの仕様に関しての脱線話も織り交ぜています。

早速ハマったSQLを再現

では、早速ハマったSQLを再現できるSQLを貼っておきます。ほんとかな〜って方は是非お手元のSnowsightで実行してみてください。

数値型と文字列型をUNION
select 1 as id
union all
select 1::varchar as id
;

ここでクイズです…！

この結果は何になるでしょうか？

答え
ID
1.00000
1.00000

ちなみにデータ型は数値型で返ってきます。

こちらを実行してみると分かります。

数値型と文字列型をUNIONしてtypeofで確認
with

_union_int_and_str as (
    select 1 as id
    union all
    select 1::varchar as id
)

select
    id,
    typeof(id)
from _union_int_and_str;

本当はCTASをしてみるとより明確に小数点以下のスケールなどの情報も見られるのでいいのですが、一旦おいておきます。

ではそろそろ解説を書いていきます。（答え合わせは済みましたか？）

データ型が違うカラムをUNIONしてはいけない

今回のSQLは数値型のカラムと文字列型のカラムで実際には数値にキャストが可能な値であるというケースです。

この場合は文字列が数値にキャストされるのですが、その際になぜかNUMBER(38,5)形式になってしまうというのが私が踏んだ罠でした。※正確には38桁なのかはデータによって異なります。

Snowflakeのドキュメント内にこの動作に関して明示的に書かれていそうな箇所はざっとみた感じなかったのですが、SnowflakeのUNIONのドキュメントには以下のような記載があります。

各列のデータ型が、異なるソースからの行全体で一貫していることを確認してください。UNION 演算子を使用し、不一致のデータ型をキャストする セクションにある例の1つは、データ型が一致しない場合に起こりうる問題と解決策を示しています。
https://docs.snowflake.com/ja/sql-reference/operators-query#general-usage-notes

そのセクションで示されていたのはまさにVARCHARとINTEGERが混じった場合の例で、文字列側の値が数値にキャストできないものなので、数値型にキャストしようとしてエラー（100038 (22018): Numeric value 'Smith, Jane' is not recognized）になるという問題です。VARCHARに明示的にキャストしましょうというのが解決策として記載されていました。

そりゃそうだな、と納得するのですが、なんでNUMBER(38,0)というデフォルトのINTEGERじゃなくてNUMBER(38,5)になるんじゃ〜というのが気になりポイントです。

今回はその気になりポイントには踏み込みませんが、整数値を扱っている時にふと小数点が勝手についてくる場合があるというのはSnowflakeでよく見かけるパターンなのです。

せっかくなので、その話題に脱線してみようかなと思います。

脱線話：Snowflakeで整数値を割り算すると整数値じゃなくなる場合がある

今度はこんなSQLの出力を当ててみてください（タイトルで盛大にネタバレ中）

整数値の割り算
select 1/1 as integer_division;
答え
INTEGER_DIVISION
1.000000

そうです。NUMBER(38,6)になって返ってきます。

5桁じゃないんかい!?

（…ツッコミどころはそこじゃない）

ドキュメントにこう仕様が書いてあります

Snowflakeは、分子のスケールが12より大きくない限り、分子のスケールに最大しきい値12桁まで6桁を追加することにより、出力の潜在的なオーバーフロー（連鎖除算による）とスケールの損失を最小限に抑えます。分子のスケールが12より大きい場合は、分子のスケールを出力スケールとして使用します。
https://docs.snowflake.com/ja/sql-reference/operators-arithmetic#division

ちなみにこの整数値同士の割り算が整数値ではないというのはRDBMS界隈では結構特殊な実装だったりします。基本的に整数同士の割り算は整数値になり、剰余は切り捨てられるのが多いです（体感）。

そんな脱線話をした上で元のUNIONの話に戻ります。

UNIONするときのベストプラクティス

それではここからは私がハマったUNIONの罠で苦労した経験を昇華させるためにベストプラクティスにまとめていきます。

そのプラクティスはこちらの3つです！

  • unionする前にカラムを明示的に並べる
  • unionする前にカラムを明示的にキャストする
  • union distinct と union all を区別するために union だけで書いてはいけない

3つ目はSQLFLuffのルールにもあるのでSQLFluffに怒られたらちゃんと従うようにしましょう

https://docs.sqlfluff.com/en/stable/reference/rules.html#rule-ambiguous.union

それではこれに違反した例を見てみましょう。

どこがダメでしょう（文脈がないけど）
SELECT * FROM hogehoge
UNION
SELECT * FROM fugafuga

最近はUNION BY NAMEという便利機能も出てきましたので順序問題はあんまり気にしなくてもいいかもしれません。例えばこんな感じでもいいかもしれません。

順番揃えない時は最低限BY NAMEをつけよう
SELECT * FROM hogehoge
UNION ALL BY NAME
SELECT * FROM fugafuga

（select * はカラムの追加・削除時に意図しない変更が入るリスクがあるため、一般的には推奨されませんが、ここでは一旦おいておきます）

一方で、データ型も明示的にキャストして揃えておくべきというのは今のところ便利機能では解決できない問題ですので、こんな感じがいいでしょう。

これが理想系
with

-- union前にちゃんとカラムの順番とキャストをしっかりとCTE内で整理しておく
hogehoge as (
    SELECT
        id::INTEGER as id,  --明示的なキャストと名前を統一
        name::VARCHAR as name,
        ...
    FROM ...
),

fugafuga as (
    SELECT
        id::INTEGER as id,
        name::VARCHAR as name,
        ...
    FROM ...
)

SELECT * FROM hogehoge
UNION ALL  -- ALLなのかDISTINCTなのかは明示的に書く
SELECT * FROM fugafuga

CTEでちゃんと綺麗にしていれば SELECT * も許されると思います。
個人的には長々としたSQLでUNIONが書かれていると分かりにくいので、UNIONの前後はシンプルに書いたほうがいいと思っています。（数行くらいが現実的な許容ライン）

最後に

UNIONを使う機会はそこまで頻度高くないと思いますが、今回のUNIONのベストプラクティスをレビューの観点に取り込んでみてはいかがでしょうか？

ちなみになんでこんな罠を引いたのかといえば、、、深い話になるのでまた別の機会に！

Discussion