(DBD::)SQLiteにおけるORDER BYの最適化と検索結果の並び順について

最初に結論を書いておきますが、これは正しい使い方をできている方ならまったく気にする必要がない記事です。要するに「ORDER BYを指定していないのにSELECT文の返値に一定の並び順を期待する方が間違い」というだけの話なんですが、先日のQA Hackathonで潜在的な問題が存在していることが浮き彫りになったので、念のため共有しておきます。

さて、SQLiteは2012年12月12日にリリースされたバージョン3.7.15でクエリオプティマイザを強化し、その結果、ORDER BYがより積極的に最適化されるようになりました。

ただ、DBD::SQLiteはしばらくリリースから遠ざかっていたため、この変更がPerl界隈の開発者の目に触れるようになったのはつい最近、2013年4月4日に1.38_02がリリースされたときのことでした。

このときどのモジュールのどのテストがこけるようになったのかはまだ把握していません。私のところに届いたのは最小限の検証スクリプトだけなのですが、たしかに以下のスクリプトをDBD::SQLite 1.38_02と、最適化以前のバージョンである1.38_01で実行すると、最後の行の出力がかわります(1.38_01ではSELECT * FROM cdの場合と同じくFoo, Barの順に出てくるのですが、1.38_02ではSELECT title FROM cdの方だけBar, Fooの順に出てくるようになります)。そのため、「不適切な方法でテストをしていると」、1.38_02からは並び順がそろわないことになり、テストがこけるようになります。

use 5.010;
use strict;
use warnings;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect('dbi:SQLite::memory:', undef, undef, {
  AutoCommit => 1,
  sqlite_allow_multiple_statements => 1,
});

$dbh->do(<<'SQL');
CREATE TABLE cd (id integer primary key, title unique, year);
INSERT INTO cd (title, year) VALUES ('foo', 2000);
INSERT INTO cd (title, year) VALUES ('bar', 2001);
SQL

say $DBD::SQLite::VERSION;
say $DBD::SQLite::sqlite_version;
say Dumper $dbh->selectall_arrayref('SELECT * FROM cd');
say Dumper $dbh->selectall_arrayref('SELECT title FROM cd');

この件については問題の大きさを検証するため、QA Hackathonの最中に特別にスモーカーを回してもらっていたようで、その後何も言ってこないところをみると結果的にそれほど大きな問題にはならなかったものと思われますが、気になる方は、SQLiteにはreverse_unordered_selectsというプラグマが用意されているので、それぞれのテストの先頭でこのプラグマを発行するか、ソースに手を加えることをいとわないなら、dbdimp.cのsqlite_db_login6に以下の一行を追加して様子を見てみるとよいでしょう。

    sqlite_exec(dbh, "PRAGMA reverse_unordered_selects = ON");

これでテストがこけるようなら、暗黙のうちにSELECT文の結果が一定の順序で返ってくることを期待していた可能性が高い、ということになります。

実際、DBD::SQLite自身にもこの問題に引っかかっているテストが2つありました(リポジトリの方では直しておきました)。DBD::SQLiteをアップグレードしたときに急にテストがこけるようになった場合は、このような問題が関係している可能性があることも覚えておいていただけるとさいわいです。