SQLのLAST関数の謎

こちらのブログの方はずいぶん久々の書き込みとなりました。
ネタがなかったわけではなくとあるトラブルが原因でその対応、決算事務、新規物件が並行して走ることになってどたばた進行で四苦八苦していました。今回はそのとあるトラブルについて書こうと思います。

数年前に作成したある会社の販売管理システムで決算に伴う年次繰越で正常に売掛残高が繰り越されないというトラブルの報告があったのがきっかけでした。
こちらで実行すると同じデータでも正しい結果が出るので再現性がありません。ネットワークで動作するソフトなので他のクライアントを使っていない状態でやり直してみてください連絡しました。すると、そこは直ったけど別の所が合いませんとのこと。どうも、やるたびに500社以上ある取引先の中で数件正常に繰越ができないようで、おかしくなる顧客も件数もランダムに変化するようです。

実は以前にも売掛金集計リストで残高の計算が狂うというトラブルがありました。このときに対処したやり方で残高更新処理を手直ししてみました。確かに発生件数は減っているようですが、直ったわけではありませんでした。

このシステムはMS Access 2000上で構築しているものです。一般の事務員さんが使うようにメニューを作ってAccess を意識しなくてもいいようにしています。また、１台をのぞいてランタイムで動作するという環境でサーバー上にデータベースをおいて３台のクライアントで処理しています。

私は以前IBMの汎用機でDB2というデータベース上でSQLを使ったシステム開発をしたことがあるのでSQLはそれなりに使えます。そこでできることはSQLで処理することにしてVBAレベルでごりごり処理をしないようにしています。

原因を調査していくと残高を管理している売掛テーブルから残高を求めるSQLから正しい結果が返っていないことがわかりました。
売掛テーブルの構成はこんな感じです。

　顧客　　　月　　　　　繰越　　　　　売上　　　　　回収　　　　残高
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
　A社　　　　５　　　　１０００　　　　　５００　　　　　８００　　　　７００
　A社　　　　６　　　　　７００　　　　　５００　　　　１０００　　　　２００
　A社　　　　７　　　　　２００　　　　１０００　　　　　２００　　　１０００
　　　　　
各月の残高を求めるにはその月のレコードの残高欄を見ればいいのですが上のデータで９月の残高を知りたいと思ったときが問題なんです。ふつうに考えれば７月の残高をそのまま持ってくればいいので、９月以前で検索して月順にソートした最後のレコードの残高を求めるという風にすればいいわけです。そのためにSQLにはLast関数という関数があります。最初に異常を見つけたときにはソートに不具合でもあって複雑なSQLを書くとうまく処理できないのではないかと考えて一度ソートだけをする中間テーブルを持つようにしました。この対策で私がテストした範囲では問題は見つかりませんでした。ところが、何ヶ月かに後におかしいと言ってきましたが、そのときには再現できずやり直したら直ったと言われました。

今回は年次繰越の処理なので全件確認する必要があって確かにやるたびに異なる数件がおかしくなっていることが確認できました(ということは過去にも発生していた？)。ソート用の中間ファイルを作るという対策でも０にはできないこともわかりました。

原因がわからずいろいろと調べているとＭＳＤＮで衝撃的な記述がありました。

この記述によるとLast関数は並べ替え指定に関係なくテーブルに最後に挿入されたレコードを返すということらしいのです。ということは偶然にも私がソート済みの中間ファイルを作成するように修正したことは正しかったのですが、実際には頻度は減ってもおかしな現象は完全になくなりませんでした。どうも、最後に挿入されたというわけではなく物理的に格納されている場所が最後になっているといった条件になっているようです。そう考えると実行するたびに不具合のでる場所が変わってくるのも何となく納得できます。データの削除と追加を繰り返すと当然物理的なブロックの利用状況が変わるからです。

どう対処するかに悩みましたが、結局取引のない月も残高レコードが存在するように最初に最大12ヶ月分作ってしまうことにしました。

　顧客　　　月　　　　　繰越　　　　　売上　　　　　回収　　　　残高
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
　A社　　　　５　　　　１０００　　　　　５００　　　　　８００　　　　７００
　A社　　　　６　　　　　７００　　　　　５００　　　　１０００　　　　２００
　A社　　　　７　　　　　２００　　　　１０００　　　　　２００　　　１０００
　A社　　　　８　　　　１０００　　　　　　　０　　　　　　　０　　　１０００
　A社　　　　９　　　　１０００　　　　　　　０　　　　　　　０　　　１０００
　A社　　　１０　　　　１０００　　　　　　　０　　　　　　　０　　　１０００
　A社　　　１１　　　　１０００　　　　　　　０　　　　　　　０　　　１０００
　A社　　　１２　　　　１０００　　　　　　　０　　　　　　　０　　　１０００

こんな感じです。
とりあえずこれで問題の解決はできました。

Last関数は標準的なSQLの関数だと思うのですが、問題はその仕様が独自仕様ということなんです。MSは仕様だと言い切っていますが、どう考えても変じゃないでしょうか。それとも私の勉強不足でこれがSQLでは普通なんでしょうか。