MySQLとPostgreSQLのランダム関数の挙動の違い（小ネタ）

MySQL Advent Calendar 2020 - Qiita の11日目の記事であり、同時に、PostgreSQL Advent Calendar 2020 - Qiita の11日目の記事です。

両データベースについての挙動差のお話です。

MySQLとRAND関数の挙動の整理 その1 - なからなLife
MySQLとRAND関数の挙動の整理 その2 - なからなLife
と、今月に入ってランダム関数について書くのは３回目ですね。

前の２回はMySQLに限った話だったので、そのPostgreSQL版を書いても良かったのですが、焼き増し感が強いので、ちょっと違う方向で。
今度は参照・検索ではなく、生成時の話です。

ランダム値生成関数の解説

MySQLのマニュアル（日本語はMySQL 5.6しか無いのでコチラで）

・RAND(), RAND(N)
0 <= v < 1.0 の範囲内で、ランダムな浮動小数点値 v を返します。定数整数引数 N が指定されている場合は、カラム値の反復可能なシーケンスを生成するシード値として使用されます。次の例では、RAND(3) で生成される値のシーケンスが、発生した両方の場所で同じです。
https://dev.mysql.com/doc/refman/5.6/ja/mathematical-functions.html#function_rand

PostgreSQLのマニュアル

関数	戻り値型	説明
random()	dp	0.0 <= x < 1.0の範囲の乱数値

https://www.postgresql.jp/document/12/html/functions-math.html

どちらも、「0.0 <= x < 1.0の範囲の乱数値」を生成します。
データ型が浮動小数点値であることも一緒です。

ランダム関数を使ってテストデータを作ってみる

環境は、MySQL8.0.22、および、PostgreSQL12.5です。

MySQLの「FLOOR(10 + RAND() * 10)」、PostgreSQLの「FLOOR(10 + RANDOM() * 10)」は、「10～19」を生成します。*1

MySQL

mysql> SELECT * FROM test_seq LIMIT 5;
+--------+
| col_id |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
+--------+
5 rows in set (0.00 sec)

mysql> SELECT
    -> (SELECT FLOOR(10 + RAND() * 10)) AS A,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS B,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS C,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS D,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS E,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS F,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS G,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS H,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS I,
    -> (SELECT FLOOR(10 + RAND() * 10)) AS J
    -> FROM test_seq
    -> LIMIT 5;
+----+----+----+----+----+----+----+----+----+----+
| A  | B  | C  | D  | E  | F  | G  | H  | I  | J  |
+----+----+----+----+----+----+----+----+----+----+
| 17 | 14 | 18 | 18 | 17 | 10 | 19 | 15 | 19 | 10 |
| 13 | 15 | 17 | 10 | 19 | 16 | 15 | 14 | 19 | 13 |
| 19 | 14 | 16 | 19 | 16 | 12 | 14 | 15 | 15 | 19 |
| 10 | 14 | 19 | 16 | 15 | 14 | 15 | 15 | 10 | 17 |
| 16 | 17 | 10 | 17 | 16 | 18 | 14 | 16 | 19 | 16 |
+----+----+----+----+----+----+----+----+----+----+
5 rows in set (0.00 sec)

PostgreSQL

postgres=# SELECT * FROM test_seq LIMIT 5;
 col_id
--------
      1
      2
      3
      4
      5
(5 rows)

postgres=# SELECT
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS A,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS B,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS C,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS D,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS E,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS F,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS G,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS H,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS I,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS J
postgres-# FROM test_seq
postgres-# LIMIT 5;
 a  | b  | c  | d  | e  | f  | g  | h  | i  | j
----+----+----+----+----+----+----+----+----+----
 17 | 18 | 13 | 11 | 15 | 15 | 16 | 11 | 15 | 14
 17 | 18 | 13 | 11 | 15 | 15 | 16 | 11 | 15 | 14
 17 | 18 | 13 | 11 | 15 | 15 | 16 | 11 | 15 | 14
 17 | 18 | 13 | 11 | 15 | 15 | 16 | 11 | 15 | 14
 17 | 18 | 13 | 11 | 15 | 15 | 16 | 11 | 15 | 14
(5 rows)

PostgreSQL側、列方向にはランダムに値が生成されていますけど、行方向にはランダムになってませんね。。。

PostgreSQLでMySQLと同じ状態を作れるか

UPDATEなら、MySQLと同じような乱数表になります。
※後述する追記も読んでください

postgres=# CREATE TABLE test_tbl (
postgres(#   test_tbl_id INTEGER NOT NULL,
postgres(#   a varchar(100),
postgres(#   b varchar(100),
postgres(#   c varchar(100),
postgres(#   d varchar(100),
postgres(#   e varchar(100),
postgres(#   f varchar(100),
postgres(#   g varchar(100),
postgres(#   h varchar(100),
postgres(#   i varchar(100),
postgres(#   j varchar(100),
postgres(#  CONSTRAINT test_tbl_pkey PRIMARY KEY (test_tbl_id)
postgres(# );
CREATE TABLE
postgres=# INSERT INTO test_tbl (test_tbl_id)
postgres-# SELECT col_id FROM test_seq
postgres-# LIMIT 5;
INSERT 0 5
postgres=# SELECT * FROM test_tbl;
 test_tbl_id | a | b | c | d | e | f | g | h | i | j
-------------+---+---+---+---+---+---+---+---+---+---
           1 |   |   |   |   |   |   |   |   |   |
           2 |   |   |   |   |   |   |   |   |   |
           3 |   |   |   |   |   |   |   |   |   |
           4 |   |   |   |   |   |   |   |   |   |
           5 |   |   |   |   |   |   |   |   |   |
(5 rows)

postgres=# UPDATE test_tbl SET
postgres-#  a = FLOOR(10 + RANDOM() * 10),
postgres-#  b = FLOOR(10 + RANDOM() * 10),
postgres-#  c = FLOOR(10 + RANDOM() * 10),
postgres-#  d = FLOOR(10 + RANDOM() * 10),
postgres-#  e = FLOOR(10 + RANDOM() * 10),
postgres-#  f = FLOOR(10 + RANDOM() * 10),
postgres-#  g = FLOOR(10 + RANDOM() * 10),
postgres-#  h = FLOOR(10 + RANDOM() * 10),
postgres-#  i = FLOOR(10 + RANDOM() * 10),
postgres-#  j = FLOOR(10 + RANDOM() * 10)
postgres-# ;
UPDATE 5
postgres=# SELECT * FROM test_tbl;
 test_tbl_id | a  | b  | c  | d  | e  | f  | g  | h  | i  | j
-------------+----+----+----+----+----+----+----+----+----+----
           1 | 15 | 15 | 17 | 10 | 16 | 12 | 10 | 13 | 11 | 10
           2 | 17 | 16 | 12 | 15 | 17 | 11 | 18 | 12 | 16 | 13
           3 | 16 | 18 | 12 | 15 | 13 | 14 | 13 | 12 | 19 | 14
           4 | 17 | 11 | 12 | 10 | 13 | 17 | 19 | 14 | 14 | 18
           5 | 16 | 16 | 19 | 17 | 17 | 14 | 10 | 19 | 18 | 11
(5 rows)

このとおり、きれいな乱数（？）になりました。

PostgreSQLでは、SELECTを使用しないと、きれいなランダムになるらしい。（追記：2020/12/11）

PostgreSQL全機能バイブルやMariaDB&MySQL全機能バイブルを執筆された鈴木啓修さん（id:interdb）から

各columnに(SELECT FLOOR(10+RANDOM()*10))を使うと、クエリ実行時に一度だけ各SELECT文のRANDOM()関数が実行されるので、各columnに同じ値が表示されます。
（略：SELECT版の実行計画）
SELECTを使わなければ、以下のようにColumn, row毎にRANDOM()が呼ばれます。
（略：SELECTなし版の実行結果）

というコメント頂いたのですが、コメント欄だと実行計画や実行結果の表示が崩れてしまう、ということで、こちらで再現したものを貼り付けます。

コメントありがとうございます！！！

SELECT版の実行計画

postgres=# EXPLAIN ANALYZE SELECT
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS A,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS B,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS C,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS D,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS E,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS F,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS G,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS H,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS I,
postgres-# (SELECT FLOOR(10 + RANDOM() * 10)) AS J
postgres-# FROM test_seq
postgres-# LIMIT 5;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.20..0.27 rows=5 width=80) (actual time=0.030..0.032 rows=5 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 3 (returns $2)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 4 (returns $3)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 5 (returns $4)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 6 (returns $5)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 7 (returns $6)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 8 (returns $7)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 9 (returns $8)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   InitPlan 10 (returns $9)
     ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
   ->  Seq Scan on test_seq  (cost=0.00..242008.16 rows=16777216 width=80) (actual time=0.029..0.030 rows=5 loops=1)
 Planning Time: 0.155 ms
 Execution Time: 0.072 ms
(24 rows)

SELECTなし版の実行結果

postgres=# SELECT
postgres-#  FLOOR(10 + RANDOM() * 10) AS A,
postgres-#  FLOOR(10 + RANDOM() * 10) AS B,
postgres-#  FLOOR(10 + RANDOM() * 10) AS C,
postgres-#  FLOOR(10 + RANDOM() * 10) AS D,
postgres-#  FLOOR(10 + RANDOM() * 10) AS E,
postgres-#  FLOOR(10 + RANDOM() * 10) AS F,
postgres-#  FLOOR(10 + RANDOM() * 10) AS G,
postgres-#  FLOOR(10 + RANDOM() * 10) AS H,
postgres-#  FLOOR(10 + RANDOM() * 10) AS I,
postgres-#  FLOOR(10 + RANDOM() * 10) AS J
postgres-# FROM test_seq
postgres-# LIMIT 5;
 a  | b  | c  | d  | e  | f  | g  | h  | i  | j
----+----+----+----+----+----+----+----+----+----
 10 | 14 | 11 | 19 | 16 | 15 | 12 | 16 | 19 | 12
 19 | 10 | 15 | 17 | 19 | 12 | 13 | 16 | 19 | 17
 14 | 10 | 10 | 14 | 17 | 18 | 11 | 11 | 11 | 14
 15 | 11 | 16 | 16 | 17 | 12 | 10 | 14 | 11 | 17
 19 | 11 | 14 | 12 | 18 | 13 | 12 | 16 | 18 | 16
(5 rows)

ついでに、SELECTなし版の実行計画

postgres=# EXPLAIN ANALYZE SELECT
postgres-#  FLOOR(10 + RANDOM() * 10) AS A,
postgres-#  FLOOR(10 + RANDOM() * 10) AS B,
postgres-#  FLOOR(10 + RANDOM() * 10) AS C,
postgres-#  FLOOR(10 + RANDOM() * 10) AS D,
postgres-#  FLOOR(10 + RANDOM() * 10) AS E,
postgres-#  FLOOR(10 + RANDOM() * 10) AS F,
postgres-#  FLOOR(10 + RANDOM() * 10) AS G,
postgres-#  FLOOR(10 + RANDOM() * 10) AS H,
postgres-#  FLOOR(10 + RANDOM() * 10) AS I,
postgres-#  FLOOR(10 + RANDOM() * 10) AS J
postgres-# FROM test_seq
postgres-# LIMIT 5;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.57 rows=5 width=80) (actual time=0.015..0.018 rows=5 loops=1)
   ->  Seq Scan on test_seq  (cost=0.00..1919729.76 rows=16777216 width=80) (actual time=0.014..0.016 rows=5 loops=1)
 Planning Time: 0.085 ms
 Execution Time: 0.037 ms
(4 rows)

MySQLではどうか？

SELECTを使っても使わなくても、結果も実行計画も同じでした。
実行計画（MySQL5.7）の比較だけ貼っておきます。

mysql> EXPLAIN SELECT
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS A,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS B,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS C,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS D,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS E,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS F,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS G,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS H,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS I,
    ->  (SELECT FLOOR(10 + RAND() * 10)) AS J
    -> FROM test_seq
    -> LIMIT 5;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | test_seq | NULL       | index | NULL          | PRIMARY | 4       | NULL | 15547276 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
1 row in set, 11 warnings (0.00 sec)


mysql> EXPLAIN SELECT
    ->  FLOOR(10 + RAND() * 10) AS A,
    ->  FLOOR(10 + RAND() * 10) AS B,
    ->  FLOOR(10 + RAND() * 10) AS C,
    ->  FLOOR(10 + RAND() * 10) AS D,
    ->  FLOOR(10 + RAND() * 10) AS E,
    ->  FLOOR(10 + RAND() * 10) AS F,
    ->  FLOOR(10 + RAND() * 10) AS G,
    ->  FLOOR(10 + RAND() * 10) AS H,
    ->  FLOOR(10 + RAND() * 10) AS I,
    ->  FLOOR(10 + RAND() * 10) AS J
    -> FROM test_seq
    -> LIMIT 5;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | test_seq | NULL       | index | NULL          | PRIMARY | 4       | NULL | 15547276 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

まとめ

• SELECTによる1行分の評価においては、MySQL、PostgreSQLともに、関数の出現（呼び出し）回数分、新しい乱数を生成させる。
• SELECTによる複数行分の評価においては、MySQLは毎回新しい乱数を生成させるが、PostgreSQLは新しい乱数を生成しない。
  • ただし、Updateのときは、MySQLと同じような挙動になる。
  • 追記：SELECTを使わなければ、MySQLと同じようにすべてランダム生成される！

ていうか、これ「ランダム関数の」挙動差ではなく、「関数全般」の挙動差として認識しておいたほうが良い？

この件が掲載されていたかどうかは忘れたけど、RDBMS毎に同じような関数がある／ないを調べるには、この本が割と便利。

［改訂第4版］SQLポケットリファレンス

あとは、それぞれのDBについてしっかり勉強しようね、っていうお話なので、いつもの推しごと。

MySQL徹底入門 第4版 MySQL 8.0対応

PostgreSQL徹底入門 第4版 インストールから機能・仕組み、アプリ作り、管理・運用まで

*1:この検証のランダム値自体はなんでもよかったのですが、なんとなく、他の用事で使ったものをそのまま流用しているだけです。