redshiftの公式ドキュメントを読んでて知ったのですが、そうみたいです。
MySQLでも同じ挙動でした。
恥ずかしながら最近初めて知ったので、勢いで書きました。。。
以下、redshiftで検証してみます。
どういうことか
こんなデータがあったとします。
> \d purchase
Table "public.purchase"
Column | Type | Modifiers
-----------+-----------------------------+-----------
date | timestamp without time zone |
userid | character varying(12) |
productid | character varying(12) |
cnt | integer |
> select * from purchase;
date | userid | productid | cnt
---------------------+--------+-----------+-----
2021-01-02 00:00:00 | u006 | p002 | 3
2021-01-03 00:00:00 | u007 | p003 | 8
2021-01-02 00:00:00 | u004 | p001 | 10
2021-01-02 00:00:00 | u005 | p001 | 5
2021-01-01 00:00:00 | u001 | p001 | 3
2021-01-01 00:00:00 | u002 | p001 | 1
2021-01-01 00:00:00 | u003 | p003 | 10
2021-01-01 00:00:00 | u002 | p002 | 1
2021-01-03 00:00:00 | u008 | p001 | 2
2021-01-04 00:00:00 | u009 | p001 | 2
(10 rows)
productidはvarchar(12)です。
実は、productid='p001'のうちどれかに、末尾に空白を仕込んでいます。
p001の売上個数を集計してみます。
> select productid, sum(cnt) from purchase where productid = 'p001' group by productid;
productid | sum
-----------+-----
p001 | 23
(1 row)
特に問題なさそうです。
次は、無理やりですが、こんな感じで集計してみます。
> select 'A' || productid || 'A', sum(cnt) from purchase where productid = 'p001' group by 1;
?column? | sum
-------------+-----
Ap001A | 8
Ap001 A | 10
Ap001 A | 5
(3 rows)
= で一つに指定してるのに？複数の値？？となります。
非常に、直感に反する結果です。が、仕様です。
あと、こんなクエリ生成する場面なさそうですが、極論でいくとこんな感じです。
> select * from _tmp_purchase where productid = 'p001 ';
date | userid | productid | cnt
---------------------+--------+-----------+-----
2021-01-02 00:00:00 | u004 | p001 | 10
2021-01-02 00:00:00 | u005 | p001 | 5
2021-01-01 00:00:00 | u001 | p001 | 3
2021-01-01 00:00:00 | u002 | p001 | 1
2021-01-03 00:00:00 | u008 | p001 | 2
2021-01-04 00:00:00 | u009 | p001 | 2
(6 rows)
そもそも、わざわざこんな特殊な集計の仕方をしなければ気付きもしないですし、末尾の空白を意識せずに集計できて便利なシーンが多いでしょう。しかし、困るケースはありそうです。
どこで困るのか
集計する環境によって結果が変わる
redshiftやMySQLのように末尾の空白を無視する環境と、そうでない環境とで、集計結果が変わってしまいますね。つらいです。
関数の結果が一意に定まらないように見えてしまう
こっちの方が実害出そうですが（私もこれで気付きました）、その項目を使って別の値を出力することを想定している場合も要注意です。例えば len() で違いが出てしまいます。
> select *, len(productid) from purchase where productid = 'p001';
date | userid | productid | cnt | len
---------------------+--------+-----------+-----+-----
2021-01-02 00:00:00 | u004 | p001 | 10 | 6
2021-01-02 00:00:00 | u005 | p001 | 5 | 9
2021-01-01 00:00:00 | u001 | p001 | 3 | 4
2021-01-01 00:00:00 | u002 | p001 | 1 | 4
2021-01-03 00:00:00 | u008 | p001 | 2 | 4
2021-01-04 00:00:00 | u009 | p001 | 2 | 4
(6 rows)
同じ入力に対して関数が複数の値を返しているように感じてしまいます。
「新商品はproductidが6桁以上という決まりだから len(productid)>=6 のレコードを抽出して新商品の売り上げを分析しよう！」みたいなことを考えて集計してしまうと、痛い目を見ます。
対処法
入れる前にトリム
もう、これに尽きるかと思います・・・。
各言語に、末尾の空白を除去する関数なりメソッドが用意されています。面倒でも、必ずトリムしましょう！
COPY時、TRIMBLANKSする
redshiftだとCOPYコマンドのオプションでありました。
ただこれだと、末尾の空白の有無が環境によって異なるという問題は解決しないので、前処理の時点でトリムを行う前提は設けておきたいですね。
おわりに
ちゃんと調べられてないですが、末尾の空白を勝手に削除・無視してくれる環境は多そうです。
しかし、それを知った上で活用するのと、知らないうちに末尾の空白が削除されていた状況とでは、事故率が変わると思います。
どこかのタイミングで改めてデータを確認してみましょう！