OracleDBのUPDATE文でORA-01427エラーに苦しめられた

GW突入前、というかもう突入した後とも言い得る5月はじめ、仕事で保守しているシステムで、OracleDBのUPDATE文でORA-01427エラーに苦しめられました。

ORA-01427は、「単一行副問合せにより2つ以上の行が戻されます」というエラーメッセージに明らかな通り、SQLのDML構文中、サブクエリの結果としてスカラー値（単一行）が戻されることが期待される箇所で、実際には複数行の結果が戻されたときに発生するものです。

問題のUPDATE文はだいたい以下のような構造でした：

UPDATE foos
SET foos.foo4 = (
		SELECT baz(foos.foo3, bars.bar3)
		FROM bars
		WHERE ((foos.foo0 = bars.bar0 
		AND foos.foo1 = bars.bar1)
                OR foos.foo2 = bars.bar2)
		AND bars.bar9 = '...'
	)
WHERE
	foos.foo9 = '...';

関係するのは（ここでは名前を伏せる必要があるので）foosとbarsという2テーブルで、UPDATE文はbarsに対するサブクエリの結果を使ってfoosを更新するものでした。bazは（もちろんこれも仮名で）何かしらの算術演算を行う関数と考えてください。

ここでサブクエリのWHERE句に登場するbars.bar0とbars.bar1は2つセットでbarsテーブルのプライマリキーを構成しています。WHERE句の1・2行目foos.foo0 = bars.bar0 AND foos.foo1 = bars.bar1という条件に不備はありません。つまり、この条件を評価した結果、barsテーブルから2レコードが返されることはありえません。

一方、bars.bar2はプライマリキーでもなければ一意性制約の付与されたカラムでもなく、事実このテーブルではbars.bar2の値に重複が見られました。WHERE句の3行目の式foos.foo2 = bars.bar2では複数レコードが選択されてしまうケースがあるのです。元となるユーザが作成したCSVに重複があり、それがこのテーブルに取り込まれていました。これはすぐわかり、すぐ修正手配もできました。

ところがこのbars.bar2の重複を除去してもなお、ORA-01427エラーが発生します。この原因を見つけるのに手こずりました（私自身は作業指示者というか上長の上長みたいな立場にあり、本格的調査には参加していませんでしたが。まあチームとして手こずったということです）。GW前というかGW中という時季にあってメンバーの精神衛生に致命的ダメージです。

結論を言えば重複はfoosテーブルにも存在しておりそれが原因でした。通常のUPDATE文であれば更新対象が複数レコードだろうが単一レコードだろうがエラーになったりはしません。しかしこのUPDATE文ではUPDATE対象テーブルfoosのカラムがサブクエリの結果セットを定義するSELECT句内で参照されています（baz(foos.foo3, bars.bar3)）。そしてサブクエリのWHERE句に記述された条件式(foos.foo0 = bars.bar0 AND foos.foo1 = bars.bar1) OR foos.foo2 = bars.bar2では、foosの複数レコードが選択されるケースがあったのです。で、これもユーザのCSVが遠因でした。

わかってしまえばどうということもない話ですが、「ORA-01427 ＝ サブクエリの問題」と考えたとき、どうしてもそのサブクエリの対象となっているテーブルだけに目が行ってしまいやすく、かつ、UPDATE ... SET foos.foo4 = (SELECT ...)
...;という見るからにスカラー値が求められている箇所にばかり注意が向けられやすい。結果、今回のような視野狭窄に陥るわけです。反省。。