10.3.17-MariaDBでRAND()を使ったSQLの結果が１レコードに定まらない理由を知りたいです。

Question

CREATE TABLE data(
 id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,datum TEXT
 ,created_at datetime  default current_timestamp
 ,updated_at timestamp default current_timestamp on update current_timestamp
);

    $limit = 1000000;
    for($i=0;$i<$limit;$i++){
        $unique_id = uniqidReal(128);


        DB::insert('insert into `data` (datum) values (?)', [
            $unique_id
        ]);
    }

SELECT * From data where id = CEIL(RAND() * (select count(*) from data));

上記のSQLでSQLの結果が１レコードに定まりません。結果が０レコードは納得いくのですが、結果が２レコードや３レコードの場合があります。納得いきません。
よろしくお願いいたします。

Comments

[m.ts10806]

DB設計とサンプルデータなしにはアドバイス不可能です。
CREATE TABLE文とINSERT文をご提示ください。

[Touhoku]

承知しました。

[m.ts10806]

Laravelのコードで提示されると、コピペで確認できないのですけど。
MariaDbからエクスポートしたものが良いのですが。

[Touhoku]

SELECT SQLから分かるように、プライマリキーカラムのidが1から100万まで入っているテーブルとお考え下さい。datumカラムはただの飾りです。よろしくお願いいたします。

[m.ts10806]

CEIL(RAND() * (select count(*) from data))
上記が何を返すか幾つか確認はしてみましたか？

[Touhoku]

画像のように整数の値を１つのみ返します。

[attercop]

事象からするとレコードごとにRANDが評価されているように思えますね。
Qiitaの以下の記事だとINNER JOINして比較しているのでそういう挙動なのかもしれません。
https://qiita.com/iri/items/b82795b6a3c0a8df62a5

[Touhoku]

なるほど。貴重な記事ありがとうございます。そういうものだと割り切ってしまうのがよいのかもしれません。

Answer 1

サブクエリのキャッシュ

SELECT * From data where id = CEIL(RAND() * (select count(*) from data));

これはサブクエリを持つクエリです。サブクエリは相関項目を持たない。一般にこの動作は、サブクエリを実行して結果をキャッシュ、キャッシュを使って外部クエリが実行される。

ここから推測。キャッシュされるのは、(select count(*) from data))の結果だけで、式 CEIL(RAND() * <キャッシュされたサブクエリ>) は毎回実行されると考えると、辻褄が会うのではないでしょうか。次のようにクエリを変更します。

SELECT * From data where id = (SELECT RID FROM (SELECT CEIL(RAND() * (select count(*) from data)) AS RID)R);

式全体をキャッシュしたので１件だけヒットすると推測します。内部結合を使う方法は他の方が回答されています。あるいは、withを使う以下の方法でもCEIL式までキャッシュできる。SQLをバラバラにしています。

WITH RND(R) AS (
SELECT RAND()
), CNT(C) AS (
select count(*) from data
), CIL(RID) AS (
SELECT CEIL(R*C) from RND cross join CNT
)
SELECT * From data where id = (SELECT RID FROM CIL);

クエリとサブクエリの関係

クエリとサブクエリの関係は思ったより複雑です。２つのクエリごとにサブクエリがキャッシュされるようです。

WITH RND(R) AS (
SELECT RAND()
), CNT(C) AS (
select count(*) from data
), CIL(RID) AS (
SELECT CEIL(R*C) from RND cross join CNT
)
SELECT * From data where id = (SELECT RID FROM CIL)
UNION ALL
SELECT * From data where id = (SELECT RID FROM CIL);

CEIL(RAND() * (select count(*) from data))はレコードごとに実行される

理由は、idと上の式の値が等しいかを判定するために、式を評価するからです。次のSQLを考えてください。これらの条件式はレコードごとに評価しています。

select * from data where id % 2 = 0 limit 5;
select * from data where id = power(ceil(rand()*10000),0);

最後に、質問の現象はどのテーブルでも再現できます。where条件に ceil(rand() * (select count(*) from <table>))と書けばよい。

結論

以下の2つのSQLは同じです。

SELECT * From data where id = CEIL(RAND() * (select count(*) from data));
SELECT id,datum,created_at,updated_at From (select *, CEIL(RAND() * (select count(*) from data)) from data) rnd where id=`CEIL(RAND() * (select count(*) from data))`;

補足

EXPLAINを使ってSQLを確認しました。以下のSQLはSUBQUERYでなくUNCACHEABLE SUBQUERYが表示されました。しかし対象が導出表だったので同じ結果が得られると判断しました。

MariaDB [...]> explain SELECT * From data where id = (SELECT R FROM (SELECT CEIL(RAND() * (select count(*) from data)) AS R) RND);
+------+----------------------+------------+--------+---------------+---------+---------+-------+--------+----------------+
| id   | select_type          | table      | type   | possible_keys | key     | key_len | ref   | rows   | Extra          |
+------+----------------------+------------+--------+---------------+---------+---------+-------+--------+----------------+
|    1 | PRIMARY              | data       | const  | PRIMARY       | PRIMARY | 8       | const | 1      | Using where    |
|    2 | UNCACHEABLE SUBQUERY | <derived3> | system | NULL          | NULL    | NULL    | NULL  | 1      |                |
|    3 | DERIVED              | NULL       | NULL   | NULL          | NULL    | NULL    | NULL  | NULL   | No tables used |
|    4 | SUBQUERY             | data       | index  | NULL          | PRIMARY | 8       | NULL  | 100239 | Using index    |
+------+----------------------+------------+--------+---------------+---------+---------+-------+--------+----------------+
4 rows in set (0.043 sec)

これ以外のSQLのEXPLAINの結果は想定どおりでした。

Comments

[xebme]

なぜ、式 CEIL(RAND() * <キャッシュされたサブクエリ>) が毎回実行されるのかには答えていません。ここから先は自分で調べるか、Stack Overflowで質問するか ... 。
<-- 愚問でした。一致判定(=)のために評価しないといけない。

[Touhoku]

なるほど。詳しい説明ありがとうございました。
特に
>これらの条件式はレコードごとに評価しています。
に納得がいきました。ベストアンサーお受け取りください。

Answer 2

確かに不思議ですね、WHERE句の中のRAND() の値が一度計算されて使い回されると考えると
複数行帰ってくるのは不思議です。
挙動から考えると１行判定するごとにRAND()の値が再計算されているのかなぁ…という気がしますが、裏付けるドキュメントが見つかりませんでした。
（RAND()をselectしてしまうとそこでさらに乱数が算出されてしまうので検証にならなかったです）

SQLの形を変えて、以下のようにすると期待する動作になると思います。

SELECT data.*, rdm.rnd_id From data
inner join (select CEIL(RAND() * (select count(*) from data)) as rnd_id) as rdm
   on rdm.rnd_id = data.id;

Answer 3

RAND()は0以上1未満の少数をランダムに返すものですので
ランダム要素がある限り結果が一意になることはないでしょう

Comments

[yambejp]

そもそもidが1から(訂正：0から)始まるユニークな整数の連番でないかぎり
ヒットしなかったり、複数レコードが表示されたりします

[Touhoku]

実はヒットしないものが出てくるのは問題ないのですが、２レコードや３レコードヒットする場合があって、これは何故だ？と質問した次第です。
一意の意味は取り違えていたので１レコードと修正しました。

[yambejp]

idがユニークではないのでは？

[Touhoku]

>ユニークな整数の連番でないかぎり
id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
としているのでユニークな整数の連番にも関わらず、複数レコードが出るのが不思議でなりません。

[yambejp]

ロジックが正しいならたしかにおかしいですね
複数表示されるときのIDはどうなっていますか？

[Touhoku]

複数表示されるときのIDの表示の仕方をご存じでしょうか？私は最終的なSELECT結果のidカラムを見る以外には分からないため、教えていただけないでしょうか？