ORDER BY句の無いクエリは結果セットの順序を保証しないと100万回学んだのに、UNION ALLの結果がソートされないことを期待してしまいました。
※PostgreSQLです。また、「期待していたこと」と「旅は続く」のクエリおよび結果は、実際に実行したものを改変したイメージです。
期待していたこと
CREATE TABLE table1 AS (SELECT 1 AS col1); CREATE TABLE table2 AS (SELECT 2 AS col2); SELECT col1 FROM table1 UNION ALL SELECT col2 FROM table2;
期待した結果。
| col1 |
|---|
| 1 |
| 2 |
実際に得られた結果。
| col1 |
|---|
| 2 |
| 1 |
とても悲しい。以下、このような結果を期待した経緯が続きます。
UNIONはソートする（まちがっている）
まずはUNIONとUNION ALLのおさらい。
クエリ1 UNION [ALL] クエリ2
UNIONはクエリ1の結果にクエリ2の結果を付加します。ALLが指定されていない場合は、その結果から重複行を削除します。
クエリ1の結果とクエリ2の結果に重複行が存在しないのであればUNION ALLを使うべきです。UNIONは重複行を排除するためにコストの高いソートを実行します。その副作用として結果セットはソートされた状態で返却されます。
CREATE TABLE table1 AS (SELECT generate_series(2, 1, -1) AS col1); CREATE TABLE table2 AS (SELECT generate_series(3, 2, -1) AS col2); SELECT col1 FROM table1 UNION SELECT col2 FROM table2;
テーブル1の内容。
| col1 |
|---|
| 3 |
| 2 |
| 1 |
テーブル2の内容。
| col2 |
|---|
| 4 |
| 3 |
| 2 |
結果。
| col1 |
|---|
| 2 |
| 4 |
| 1 |
| 3 |
ソートされてないじゃん！！
、、、落ち着いて実行計画を見ましょう。
-- UNIONの実行計画 EXPLAIN SELECT col1 FROM table1 UNION SELECT col2 FROM table2;
HashAggregate (cost=160.25..211.25 rows=5100 width=4)
Group Key: table1.col1
-> Append (cost=0.00..147.50 rows=5100 width=4)
-> Seq Scan on table1 (cost=0.00..35.50 rows=2550 width=4)
-> Seq Scan on table2 (cost=0.00..35.50 rows=2550 width=4)
HashAggregateが選択されていました。都合が悪いのでoffにしておきましょう。
-- オプティマイザがHash Aggregateを選びにくくする set enable_hashagg = 'off'; -- UNIONの実行計画（リベンジ） EXPLAIN SELECT col1 FROM table1 UNION SELECT col2 FROM table2;
Unique (cost=461.57..487.07 rows=5100 width=4)
-> Sort (cost=461.57..474.32 rows=5100 width=4)
Sort Key: table1.col1
-> Append (cost=0.00..147.50 rows=5100 width=4)
-> Seq Scan on table1 (cost=0.00..35.50 rows=2550 width=4)
-> Seq Scan on table2 (cost=0.00..35.50 rows=2550 width=4)
ソートしてますね。結果も以下のとおり。
| col1 |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
今まさに直面したように、明示的にORDER BYを指定しない限り、順序は保証されません。PostgreSQLのキモチ次第です。UNIONの結果がソートされるなんて考えは浅はかなのです。
UNION ALLはソートしない（まちがっている）
「UNIONは重複行を排除するためにソートする」
→「UNION ALLは重複行を排除しない」
→「UNION ALLはソートしない」
浅はかな考えでしょう？
でも、私がソートしないと考えたのは他に理由がありそうです。UNION ALLは2つのクエリをガッチャンコするというイメージに支配されていたんですよ。↓が私の脳内です。
-- クエリ1 SELECT col1 FROM table1 UNION ALL -- クエリ2 SELECT col2 FROM table2;
それぞれ独立のクエリと考えていたようで、クエリ1の結果とクエリ2の結果が混ざることにモヤッとしたのかもしれません。SELECTの構文が答えを与えてくれます。
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
UNIONいた。ORDER BYの手前にいた。脳内イメージを書き換えます。
-- クエリ1 SELECT col1 FROM table1 -- クエリ1のUNION句 UNION ALL SELECT col2 FROM table2;
完全に理解した。
つまり↓は構文エラーになる。
-- クエリ1 SELECT col1 FROM table1 ORDER BY col1 -- クエリ1のUNION句 UNION ALL SELECT col2 FROM table2;
過去の私ならやっていそうですね。言ってやりましょう。
「「「目を醒ませ 僕らの世界が何者かに侵略されてるぞ」」」
完全に理解した現在の私ならば以下のように書きます。
-- クエリ1 SELECT col1 FROM table1 -- クエリ1のUNION句 UNION ALL SELECT col2 FROM table2 -- クエリ1のORDER BY句 ORDER BY col1;
スッキリしました。普通のSELECT文と認識できれば、UNION ALLが含まれていようと順序が保証されないことに納得です。とても当たり前のことを言っています。
何度でも自分に言い聞かせますが、ORDER BY句の無いクエリは結果セットの順序を保証しないし、UNIONは聴いたほうがいい。
- provided courtesy of iTunes
旅は続く
冒頭に戻ります。これでいいんです。
CREATE TABLE table1 AS (SELECT 1 AS col1); CREATE TABLE table2 AS (SELECT 2 AS col2); SELECT col1 FROM table1 UNION ALL SELECT col2 FROM table2 -- 明示的にソートする ORDER BY col1;
期待した結果。
| col1 |
|---|
| 1 |
| 2 |
実際に得られた結果。
| col1 |
|---|
| 2 |
| 1 |
は？
追記。↑超嘘つきました。
冒頭で本章のクエリと結果はイメージと書きましたが、ソートで指定していたカラムが実際には文字列でした。サンプルのように数値であれば期待したとおりに並びます。当たり前だ。文字列が思ってたとおりにソートされなかった原因はロケール関連。メモ程度ですが内容はこちら。
