« PHPからのJavaScript出力でエラー Main VistaでVisual Studio C# »

SQLiteのサブクエリの落とし穴

SQLite 3.6.20での話。

次のような2つのテーブル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時に限らないようなので、当面の原因っぽい「サブクエリの落とし穴」に変更しました。

1 Comment

[…] SQLite のサブクエリの落とし穴 | 怒涛のめもめもリンク集 […]

Leave a comment

Your comment