MySQL

担当 タイトル案、本文全般：私 環境・サンプルコード系全般：Claude SQL検証解説：Claude ヘッダ生成 TL;DR 暇つぶしに書いたタロットカードアプリで踏んだバグの話。 PostgreSQLの仕様の読み間違えによるバグが思ったより分かりづらかったのでその流れと判明した具体的な原因、そしてそれらにまつわる検証内容を記事にする。 アプリはdocker compose環境下で動くようになっている。 時系列 タロットカードの結果が4回連続逆位置・・・気の所為か タロットカードアプリを書いた。コード自体はほぼCodexで生成した。細部の実装までは見てない。 実際に使ってみて4日、逆方向が妙に続いている。 確率としては 1/2^4 = 1/16 = 0.0625（6.25%） 約6%程度の可能性があるので、まあ・・・気の所為レベルではある。 とはいえ、少し気になったのでCodexに「逆位置が妙に続いているので実装に問題ないか調べてほしい」と指示→結論だけいえば「問題ありません」というニュアンスの解答が戻ってきたので安心（？）して閉じた。 5回目も逆方向 でも実装に問題ないと言われてるし、偶然か 6回目も逆方向は流石におかしい というわけで再度Codexに調査を依頼。 とはいえ、抽象的な指示だと同じような結果になることはわかったので 実際にバックアップを取得した上で、実際に無限にタロットを引けるようにして検証コード作ってもらって検証して調査するように指示した。 結果、以下のSQLに原因があった。 SELECT id, number, name, upright_meaning, reversed_meaning, random() < 0.5 AS is_reversed FROM tarot_cards ORDER BY random() LIMIT 1; このSQLには二箇所random()が存在するが、 PostgreSQLで実行するとなんとrandom()の結果が使い回されてしまうようだ。 今回の実装では大アルカナのみなのでカードは22枚用意した。 ORDER BY random() LIMIT 1は、乱数が最も小さいカードを選ぶため22個の乱数から最小値を選んだ場合、その値が0.5以上になるのは"22個すべてが0.5以上"の場合だけであるため 確率は (1/2)^22 = 1 / 4,194,304 となる。 つまり元の実装では 逆位置：約99.999976% 正位置：約0.000024% となる。 そりゃ全部逆になるわけだ。 修正SQL WITH picked_card AS MATERIALIZED ( SELECT id, number, name, upright_meaning, reversed_meaning FROM tarot_cards ORDER BY random() LIMIT 1 ), picked AS MATERIALIZED ( SELECT picked_card.*, random() < 0.5 AS is_reversed FROM picked_card ) SELECT id, number, name, CASE WHEN is_reversed THEN 'reversed' ELSE 'upright' END, CASE WHEN is_reversed THEN reversed_meaning ELSE upright_meaning END FROM picked; 素直にWITHで区切ったところ、期待通りにタロットカードの位置がバラついた。 ...