なぜ、一番上から取得するのか

Question

下記のテーブルからuriaが最大のものを取得する際、

MariaDB [db1]> select * from tb;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A103 |  101 |    4 |
| A102 |   54 |    5 |
| A104 |  181 |    4 |
| A101 |  184 |    4 |
| A103 |   17 |    5 |
| A101 |  300 |    5 |
| A102 |  205 |    6 |
| A104 |   93 |    5 |
| A103 |   12 |    6 |
| A107 |   87 |    6 |
+------+------+------+

select * from tb where uria in (select max(uria) from tb);というSQL文で取得できます。

+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A101 |  300 |    5 |
+------+------+------+
1 row in set (0.04 sec)

ところでselect bang,max(uria),tuki from tb;というSQL文を発行すると

+------+-----------+------+
| bang | max(uria) | tuki |
+------+-----------+------+
| A103 |       300 |    4 |
+------+-----------+------+
1 row in set (0.00 sec)

というレコードが取得できます。
カラムbangとtukiの値は、tbテーブルの一番上の部分から、uriaは、関数の通りtbテーブル内で一番大きい数を取得しています。
ここで、素朴な疑問なのですが、なぜ、一番上の部分から取得しようとしたのでしょうか。仕様と言われるとそれまでですが、何か理由があるのでしょうか。

Answer 1

素朴な疑問なのですが、なぜ、一番上の部分から取得しようとしたのでしょうか。

MySQLの気まぐれです。何が取られるかは、全く保証されません。

サーバーは各グループから任意の値を自由に選択できるため、同じ値でなければ、選択した値は不確定です（MySQL 5.6 リファレンスより）。

Answer 2

まず、 maxを使ったときに、他のカラムが取得できる動作は、最近のMySQLではデフォルトでオフになっているはず。参考資料、環境を見直されることをおすすめします。

で、私の回答なんですが、

• なぜ一番上から取ったと言えるのでしょうか？

というレコードが取得できます。
カラムbangとtukiの値は、tbテーブルの一番上の部分

と書いておられる部分なんですが、私には、bangは最初の表で言う9番目の、tukiは4番目の値が取られているように見えて仕方がありません。なぜ一番上を取ったと言えるのでしょうか？たしかにbangを9番目の、tukiを4番目のレコードから取ったというのは、両方を1番目から取ったと考えるより変です。ただ、この明らかに変な主張を否定できないのが、他の方も仰っておられる"不定"であるということだと思います。

• 一番目が取られるのは当然

最初の表を出したクエリもorder byされていません。つまり、最初の表の順序が本来的に不定なはずです。このとき、"仕様上は不定である"といっても使っているデータベースはどこかには実装が存在するのですから、取りやすい、取りにくいがあるはずです。つまり、最初の表は、現に存在するあなたのデータベースで、結果として取得しやすかったり、表示しやすかった順で表示されている、と考えられます。
であれば、また別のクエリをしたとき、制約のかかっていないデータが、最も取得・表示しやすい第一のレコードになっているのは、必然ではないでしょうか。逆に、指定もしていないのに、全テーブルから取得した後ろから30%の位置にある行が取得できるほうが、どういう仕組みがあるんだろう、と悩むべきことに思えます。

RDBは設計として集合の操作です。集合それ自身には順序性がありません。結果として上から並んでいることを暗黙に順序がある(一番目とか二番目とか)と認識するのは誤解のもとでしかないです。

Comments

[maisumakun]

MySQL 8.0ではデフォルトで取れなくなっていたようです。ありがとうございます。

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

Answer 3

そもそもgroup byしないでmaxを取る場合はそのカラムだけしか拾えません

select max(uria) from tb;

他のカラムを一緒に拾うのは本来であれば文法エラーです

select bang,max(uria),tuki from tb;

MySQLのバージョンによってはエラーにならないこともありますが
それは「しょうがないからなにか適当な（いい加減な）データを表示してやるか」
というMySQLの優しさでしかありません。

それはたとえば

select bang,max(uria),min(uria),tuki from tb;

としたとき、bangやtukiは最大値のものを取るのか最小値のものをとるのかわからないですよね？
つまりなにか適当なものをとってくるのです。

なお一般に最大値のデータをとるにはこうします

select * from tb as t1 where not exists(select 1 from tb where uria>t1.uria);

Answer 4

仕様については、maisumakun さんが書かれている通りです。

補足をしておくと、
select bang,max(uria),tuki from tb;
という SQL 文は、使用方法的に誤りであるということです。

Answer 5

DB では通常、明示的に order by しない限り、結果の並び順は不定です。

ただ、実際の動作として捉えた場合、取得時に利用したインデックスの順に並んでいることが予想されます。
フルスキャンになる場合はレコード群の先頭から順に処理しているでしょうから、その場合はレコードが登録された順番になることが予想されます。
※update が delete->insert の順で行われるような DB の場合、順番が変わる可能性もあります

そして、その結果を集計する場合の動作を考えると、まず得られた結果をソートしているとしても、ソート前の順序を壊さないソート（安定ソート）を使っているかどうかで結果が変わります。概ね安定ソートを使っているとは思うのですが、その辺は RDBMS 固有のアルゴリズムの部分なのでうかがい知れるものではありません。

以上のような理由で、たまたまその順番になったに過ぎません。

Answer 6

素朴な疑問なのですが、なぜ、一番上の部分から取得しようとしたのでしょうか。仕様と言われるとそれまでですが、何か理由があるのでしょうか。

内容的には他の方と重複してしまいますが、標準SQLでは不正となるMySQLの独自拡張仕様です。
12.19.3 MySQL での GROUP BY の処理
ONLY_FULL_GROUP_BY を有効にすると、この MySQL拡張が無効になりますので、設定されてはどうでしょうか。

Answer 7

仕様と言われるとそれまでですが、何か理由があるのでしょうか。

重箱の隅をつつくようで申し訳ないですが、
「仕様」はそもそもが「理由」が含まれているものです。
なので「仕様」ということでそれまでにされては？

技術者として興味・探求心があるのは結構なことなのですが、
自身が作ったものでなければ深く追ったところで利用者がどうにかできるわけでもなく、
既に回答が出ているように、確実に保証されているものではないので、
そのような仕様であると理解した上で、
気にしなくて済むようにorder byは何であっても入れるように習慣づけるまたは
必ずorder byを入れて取得するような設計をするだけかと思います。