SQLのランダム関数がランダムすぎる、あるいはランダムでない
ネタ元はこちら:
create table my_table (my_number integer); insert into my_table values (0), (1);
としたテーブルに対して
select * from my_table where id = <ランダム関数を用いて求めた整数値を2で割った余り>; // 例: MySQLの場合 select * from my_table where id = (floor(rand() * 100) % 2);
このようなSQLを実行すると、どのRDBMSの場合でも、結果は必ずどちらか1レコードのみが得られる…わけではない、と。
0行だったり2行だったりするRDBMSもあります。
理由はMySQLのリファレンスに書いてあったのですが、
WHERE 句内の RAND() は、WHERE が実行されるたびに再評価されます。
ということで、1レコード目の評価では0だったとして、2レコード目の評価時にも0である保証はない、ということですね。
一方で、SQL Serverではそうではなく、上記のような状況で常に同じ値を返しますので、どちらの評価時にも0、あるいはどちらの評価時にも1の2パターンになります。
気になったので、ランダム関数がどちらのRDBMS方式なのか、メジャーなものを対象に調べてみました。
- MySQL5.7.10型
- Oracle 11g
- PostgreSQL9.4.5
- sqlite3.8.10.2
- SQL Server 2014型
- (なし)
結果として、SQL Serverだけ特殊、ということになりました。
そういえばASEはどうなんだろう…と検索してみたところ(仮想環境的なものは見つかりませんでした)、オンラインマニュアルに次のような記述がありました。
Unlike rand, rand2 is computed for each returned row when it is used in the select list.
というわけで、SQL Serverと同じ~rand~以外に、~rand2~という関数も備えているようです。
(ただ、used in the select list という意味がわかってません。もしかしたらwhere句では使えないってことなのかも…?)