SQLiteのサブクエリの落とし穴
SQLite 3.6.20での話。
次のような２つのテーブルX,Yを用意して、Xにはname、Yにはそれぞれに対応づけしたyearを保存するものとした。
sqlite> CREATE TABLE X (id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO X (name) VALUES ('A');
sqlite> INSERT INTO X (name) VALUES ('B');
sqlite> INSERT INTO X (name) VALUES ('C');
sqlite> INSERT INTO X (name) VALUES ('D');sqlite> CREATE TABLE Y (id INTEGER PRIMARY KEY, year INTEGER, x_id INTEGER); sqlite> INSERT INTO Y (year, x_id) VALUES (2010, (SELECT id FROM X WHERE name='A')); sqlite> INSERT INTO Y (year, x_id) VALUES (2011, (SELECT id FROM X WHERE name='B')); sqlite> INSERT INTO Y (year, x_id) VALUES (2012, (SELECT id FROM X WHERE name='C')); sqlite> INSERT INTO Y (year, x_id) VALUES (2013, (SELECT id FROM X WHERE name='D'));
sqlite> SELECT Y.id,Y.year,X.name FROM Y LEFT JOIN X ON Y.x_id=X.id; 1|2010|A 2|2011|B 3|2012|C 4|2013|D
YからXを参照する際にXのidとして、x_idというカラム名にしている点に注意。
で、これを更新する際にまとめて指定しようとして WHERE 句での IN を使って正しくは
sqlite> UPDATE Y SET year=2014 WHERE x_id IN (SELECT id FROM X WHERE name IN ('B','C'));sqlite> SELECT Y.id,Y.year,X.name FROM Y LEFT JOIN X ON Y.x_id=X.id; 1|2010|A 2|2014|B 3|2014|C 4|2013|D
とすべきところを、うっかりYでのx_idに引きずられて、Xのテーブル指定時にもidとせず次のようにx_idとやってしまった。
sqlite> UPDATE Y SET year=2014 WHERE x_id IN (SELECT x_id FROM X WHERE name in ('B','C'));丸括弧内のサブクエリのSELECTでは存在しないカラム名を指定しているので、通常なら
sqlite> SELECT x_id FROM X WHERE name IN ('B','C');
Error: no such column: x_idとエラーになるのだが、サブクエリ内にあるとどうもエラーにならず、かつすべての項目が対象になってしまうらしい。
sqlite> UPDATE Y SET year=2014 WHERE x_id IN (SELECT x_id FROM X WHERE name in ('B','C'));sqlite> SELECT Y.id,Y.year,X.name FROM Y LEFT JOIN X ON Y.x_id=X.id; 1|2014|A 2|2014|B 3|2014|C 4|2014|D
なんてこった…（エラーが発生しないのは、UPDATEのときだけかと思ったら、他のSELECTなどでも同様のようだ）。
これは正しい動作なんだろうか？
サブクエリ内が空になるからかと、次のように試してみたが、
sqlite> UPDATE Y SET year=2014 WHERE x_id IN ();
sqlite> SELECT Y.id,Y.year,X.name FROM Y left JOIN X ON Y.x_id=X.id; 1|2010|A 2|2011|B 3|2012|C 4|2013|D
この場合はINの対象にマッチしないんだから、（当然）更新されなかった。
で、このときうっかりサブクエリ内のINの対象を()にしてしまったが、
sqlite> UPDATE Y SET year=2014 WHERE x_id IN (SELECT x_id FROM X WHERE name IN ());
sqlite> SELECT Y.id,Y.year,X.name FROM Y LEFT JOIN X ON Y.x_id=X.id; 1|2010|A 2|2011|B 3|2012|C 4|2013|D
カラム名でエラーが出ていて先の例からするとすべてが更新対象になりそうなのに、この場合は更新されなかった。
よくわからん…。
SQLiteの最新版は3.8.4.3らしいが、そちらでは動作が変わっているのかな？
[追記]
INに限らず、=でも同様にエラー時にすべてを対象に更新されてしまった。
sqlite> UPDATE Y SET year=2014 WHERE x_id=(SELECT x_id FROM X WHERE name='B');
sqlite> SELECT Y.id,Y.year,X.name FROM Y LEFT JOIN X ON Y.x_id=X.id; 1|2014|A 2|2014|B 3|2014|C 4|2014|D
[追記]
当初、タイトルを「updateの落とし穴」としていましたがupdate時に限らないようなので、当面の原因っぽい「サブクエリの落とし穴」に変更しました。
[…] SQLite のサブクエリの落とし穴 | 怒涛のめもめもリンク集 […]