SQL：ORDER BYで並び替えたSELECT文をUNION ALLで結合した場合に順番が変わってしまう

Question

商品名と日付で複数入力可能な検索機能を作成しており、
PHPにて以下のSQLを自動生成するようにしています。

入力
> りんご,2020-04-16
> みかん,2020-05-02
> レモン,2020-03-12

SELECT p.*
FROM (
      SELECT *
      FROM(
            SELECT * FROM product
            WHERE product = 'りんご' AND sold_date <= '2020-04-01'
            ORDER BY product , sold_date DESC
            limit 1
      )

UNION ALL

SELECT p.* categories.*
FROM (
      SELECT *
      FROM(
            SELECT * FROM product
            WHERE product = 'みかん' AND sold_date <= '2020-05-01'
            ORDER BY product , sold_date DESC
            limit 1
)

UNION ALL
・
・
・
） AS p

入力された商品名かつ指定された日付より古い最新のものを１件取得するSQLで、
入力の件数に応じてSELECT文をUNION ALLで接続しています。

結果としては欲しいものが返ってくるのですが、
日付順で勝手にソートされてしまっており、
入力された順番に取ってこれるように修正したいと考えております。

現在の結果
> みかん,2020-05-01,売上,カテゴリー,・・・
> りんご,2020-04-11,売上,カテゴリー,・・・
> レモン,2020-03-12,売上,カテゴリー,・・・

求める結果
> りんご,2020-04-11,売上,カテゴリー,・・・
> みかん,2020-05-01,売上,カテゴリー,・・・
> レモン,2020-03-12,売上,カテゴリー,・・・

UNION ALLだと順番は変わらないという内容が多いのですが、
UNION前にそれぞれでORDER BYを使用していた場合は順番が変動してしまうのでしょうか。
問題点などご指摘をお願いしたいです。

すみませんが、どうぞよろしくお願い致します。- リスト

Comments

[sazi]

別質問の性能改善に関係していると思うんですが、見たところ、Window関数を使用する代わりにunionにしているようですね。
インデックスのチューニングは行ったのでしょうか？

[yowayowanitohei]

sazi様

ご回答ありがとうございます。
UNIONにすると速度の問題がかなり改善しました。。。
これで正しいデータが取れるならこのままやってしまいたいなと考えています。
（以前のご回答アンサー付けておらず申し訳ないです）

[sazi]

インデックスは追加してその上でSQLを変更しているのですね。
あの実行計画から言える事では、or 条件をunion している事での改善ではなく、
（Window関数を使用する代わりの）SQL構造変更による改善でしょうから、誤解なきよう。

[sazi]

インデックスは追加しておらず、unionで早くなったというなら、それはたまたま、limit 1のデータが早くに見つかっただけで、データの後方にあるような場合は、遅くなるはずですから、性能が安定しない事になりますよ。

[sazi]

(質問が変更されたので削除)

[yowayowanitohei]

sazi様
言葉足らずですみません
index追加後に同じ処理だと変わらず原因を探っていました。
その結果or・or・or・で取得よりもunion allの方がかなりスピードが出ていたためこの修正になった次第です。

また、ご指摘ありがとうございます。
sold_date <= '2020-04-01'
正しいものは上記でした。本文修正させていただきました。

[sazi]

> union allの方がかなりスピードが出ていたため

対象データをピンポイントに絞る事で高速になったという事ですね。
実行計画のその後は、別質問に経過報告して貰えれば、アドバイス出来る事はあるかもしれません。

Answer 1

PostgreSQL のマニュアルでも、

UNIONは、query2の結果をquery1の結果に付加します（しかし、この順序で実際に行が返される保証はありません）

とあるので、結合した結果の並び順は（それぞれで order by していたとしても）保証されない、と見るべきでしょう。確実にするならば m.ts10806 さんのいうように、union した結果に対してさらに order by することになるでしょう。

Comments

[yowayowanitohei]

tacsheaven様
ご回答ありがとうございます。
マニュアル確認不足でした。。。
日付ランダムでもう少し多めの検索を行ってみたのですが、
どうやら日付でソートされているわけでもなく、取得結果の順番は
よくわからないものでした・・・

「サブクエリでorder by」にいまいちよくイメージができておらず、
そのように修正した場合でも入力順に表示させるには入力自体を日付順で並べ替える必要があり
日付順がランダムな検索を行った場合は表示がおかしくなってしまう
ということなのでしょうか。

重ねての質問で申し訳ございません。

Answer 2

結局はレコード結果を積み重ねてるに過ぎませんので、内部的にソートされたものはそのまま下に下に重なるだけだと思います。
全体のレコードからソートを担保したいのでたらunionした塊を1つの大きなサブクエリにして、そいつに対してorder byするのが確実です。

Comments

[yowayowanitohei]

m.ts10806 様
ご回答ありがとうございます。
日付ランダムでもう少し多めの検索を行ってみたのですが、
どうやら日付でソートされているわけでもなさそうでした・・・

「サブクエリでorder by」にいまいちよくイメージができておらず、
そのように修正した場合でも入力順に表示させるには入力自体を日付順で並べ替える必要があり
日付順がランダムな検索を行った場合は表示がおかしくなってしまう
ということなのでしょうか。

重ねての質問で申し訳ございません。

[m.ts10806]

最上部に被せるだけです。
select
*
from (
今のSQL
)
order by 〜

[yowayowanitohei]

できました！
ダミーで番号を振って、それに対してorder by でソートすることによって
検索した順番を正しく表示させることができました！