JDBC でプリペアードステートメント を使ったクエリでハマった事

Java .NET SQL Server

Microsoft 的には、「パラメーター化されたクエリー」って言うのかな。
んでハマったポイントは、GROUP BY にパラメータの値を渡しているとエラーになる場合がある事。しかも実際の値で動かすと正常に動作するから性質が悪い。
具体的な例を書くと…

CREATE TABLE [Table1] ( 
  [Id] INT IDENTITY(1, 1) PRIMARY KEY, 
  [登録日] DATE, 
  [金額] NUMERIC(12, 0) 
) 

INSERT INTO [Table1] VALUES ('2000/01/01', 100)
INSERT INTO [Table1] VALUES ('2001/01/01', 100)
INSERT INTO [Table1] VALUES ('2002/01/01', 100)
INSERT INTO [Table1] VALUES ('2003/01/01', 100)
INSERT INTO [Table1] VALUES ('2004/01/01', 100)
INSERT INTO [Table1] VALUES ('2005/01/01', 100)
INSERT INTO [Table1] VALUES ('2006/01/01', 100)
INSERT INTO [Table1] VALUES ('2007/01/01', 100)
INSERT INTO [Table1] VALUES ('2008/01/01', 100)
INSERT INTO [Table1] VALUES ('2009/01/01', 100)
INSERT INTO [Table1] VALUES ('2010/01/01', 100)

DECLARE @小学校卒業年 INT
DECLARE @中学校卒業年 INT

SET @小学校卒業年 = 2003
SET @中学校卒業年 = 2006

SELECT 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= @小学校卒業年 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= @中学校卒業年 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END AS [年代], 
  SUM([金額]) AS [金額] 
FROM 
  [Table1] 
GROUP BY 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= @小学校卒業年 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= @中学校卒業年 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END 

DROP TABLE [Table1]

こんな感じの事を実現しようと Java 側で実装するクエリは、
※テーブルやデータは既に存在する前提

SELECT 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= ? THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= ? THEN '2.中学生時代' 
    ELSE '3.大人' 
  END AS [年代], 
  SUM([金額]) AS [金額] 
FROM 
  [Table1] 
GROUP BY 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= ? THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= ? THEN '2.中学生時代' 
    ELSE '3.大人' 
  END

こうなり、これを JDBC のプリペアードステートメント を使って実行すると、SQL SERVER 側では次の様なクエリが実行されます。
※大分端折ってますが、SQL Server Profiler で実行されたクエリを見るとこんな感じでした。*1

DECLARE @p0 INT
DECLARE @p1 INT
DECLARE @p2 INT
DECLARE @p3 INT

SET @p0 = 2003
SET @p1 = 2006
SET @p2 = 2003
SET @p3 = 2006

SELECT 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= @p0 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= @p1 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END AS [年代], 
  SUM([金額]) AS [金額] 
FROM 
  [Table1] 
GROUP BY 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= @p2 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= @p3 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END

これを実行すると、以下の様なエラーになります。

メッセージ 8120、レベル 16、状態 1、行 51
列 'Table1.登録日' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。

SELECT と GROUP BY で使っている変数名が異なっています、そのためエラーが発生しています。
でも、実際の値を使って実行すると次の様なクエリになりエラーになりません。このせいで何が原因か暫くわからなかった！

SELECT 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= 2003 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= 2006 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END AS [年代], 
  SUM([金額]) AS [金額] 
FROM 
  [Table1] 
GROUP BY 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= 2003 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= 2006 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END


ちなみに、.NET の SqlCommand クラス (System.Data.SqlClient) では、名前付きパラメータを使えるので、上記の様な事は起きません。
.NET 側で実装するクエリは、以下のような形になり SQL SERVER 側でも同じクエリで実行されました。
※これも SQL Server Profiler で確認*2

SELECT 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= @小学校卒業年 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= @中学校卒業年 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END AS [年代], 
  SUM([金額]) AS [金額] 
FROM 
  [Table1] 
GROUP BY 
  CASE 
    WHEN DATEPART(YYYY, [登録日]) <= @小学校卒業年 THEN '1.小学生時代' 
    WHEN DATEPART(YYYY, [登録日]) <= @中学校卒業年 THEN '2.中学生時代' 
    ELSE '3.大人' 
  END

※.NET でも、名前付きパラメータを使えないケース(OdbcCommand クラス (System.Data.Odbc) 等)では、Java と同様のエラーが起きます。

*1:SQL SERVER 2005 では、[http://msdn.microsoft.com/ja-jp/library/ff487303.aspx:title] を使って実行されてました。

*2:[http://msdn.microsoft.com/ja-jp/library/ms188001.aspx:title] を使って実行されていました。