乱数を条件としたselectで期待する結果を取得できない理由を知りたい

Question

前提・実現したいこと

PostgreSQLで最後のselectの結果が1ではなく0,1,2,3のいずれかになる
1ではない理由がわからない

create table rand_test(testid int);
insert into rand_test values (0);
insert into rand_test values (1);
insert into rand_test values (2);
select count(*) from rand_test where testid=trunc(random() * 3);

発生している問題・エラーメッセージ

必ず1になると期待していたがそうではない

試したこと

PostgreSQL9, 11での結果です。

Answer 1

random()がwhereでの評価の度に呼び出されるからです。

サブクエリーなどで、random()呼び出しを1度だけにすると、期待した結果が得られます。

select count(*)
from rand_test 
where testid =(select trunc(random() * 3))

追記

試してみたら、サブクエリーでは駄目でしたね。
With式なら大丈夫でした。

with rand as (
  select trunc(random() * 3) as random_value
)
select count(*)
from (values (0),(1),(2)) as rand_test(testid) 
      inner join rand
      on rand_test.testid=rand.random_value

Comments

[tanimae]

あー、そうか全データ分random()が行われているのか

[tanimae]

> 試してみたら、サブクエリーでは駄目でしたね。
２０回くらいの結果ではOKでしたが。
まあ、withのほうが確実ですね。
ありがとうございます！！

Answer 2

ヒント

select count(*) from rand_test where testid = 10 ;

って存在しないデータをSELECT したらどうなりますか？

Comments

[tanimae]

必ず0になります。これは期待通りですね。

[tanimae]

もう少しヒントください。全然わからないです。。

[Orlofsky]

SELECT random() AS ANS1, random() * 3 AS ANS2, trunc(random() * 3) AS ANS1 ...
の結果と比べるとか？

[tanimae]

見落としていたのはrandom()が全レコードで評価される点です。
解決しました！ありがとうございます。