TimberLandChapelのTech　Blog

えー，TLC です。

今年も残すところ「今日だけ」になってしまいました。

今年の半ばに始まったこのコミュニティステージですが，

幸いなことに様々な方に訪れていただくことができました。

来年はもっともっとコンテンツを増やしてよりパワーアップさせていくつもりです。

さて，

TLC からのささやかな感謝の気持ちといってはなんですが，

３１日１７：００までにTLC にメールくださった方に

TLC の年賀マンガ２００６をお送りします。

件名に【TLCの年賀マンガ希望】と書いてメールください。

ついでにTLC.com にユーザー登録していただけるとよろこびます。

メールでの配信となりますので，いつ届くかはちょっと保障いたしかねますが，気長に待ってやってください。

それでは，残りの２００５年も無事終わりますように。

SQL Server 2005 で，

自己結合＋サブクエリ＋集計関数（ややこしい。。。）

を使用すると，不思議な実行プランによって，不思議な結果が返されます。

問題の実行プラン↓

どうも，Max 関数を両方のサブクエリに含めてあげると，オプティマイザが悪さをしている感じでしょうか？

2005 用テストクエリ

/*
 2005.12.24 TimberLandChapel
 Self Join Test SQL Server 2000 / SQL Server 2005 for 2005
*/
--Table Drop Section
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[JoinTest]') AND type in (N'U'))
DROP TABLE [dbo].[JoinTest]
GO

--Test Table Create
CREATE TABLE [dbo].[JoinTest](
[Column1] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column2] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column3] [nvarchar](50) COLLATE Japanese_CI_AS NULL
) ON [PRIMARY]
GO

--Test Data Insert
INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','1','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','2','-100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','3','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A002','1','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A002','2','-100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A002','3','-100')
GO

--Show Test Table
SELECT * FROM [JoinTest]
GO

--With Max Query
--This query returns false result.
SELECT * FROM
((SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0 GROUP BY [Column1]) AS [TABLE1]
INNER JOIN
(SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST]
GROUP BY [Column1]) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[MaxColumn2] = [TABLE2].[MaxColumn2])
GO

--Without Max Query
SELECT * FROM
((SELECT [Column1], [Column2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0) AS [TABLE1]
INNER JOIN
(SELECT [Column1], [Column2] FROM [Test].[dbo].[JoinTEST] WHERE [Column3] > 0 ) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[Column2] = [TABLE2].[Column2])
GO

--Max Query Only
SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0 GROUP BY [Column1]

--Correlated Subquery Test
SELECT [Column1], Column2 FROM [Test].[dbo].[JoinTEST] AS [Outer]
WHERE [Column2] = (SELECT TOP 1 [Column2] FROM [Test].[dbo].[JoinTest] AS [Inner] WHERE [Inner].[Column3] < 0 AND [Inner].[Column1] = [Outer].[Column1] ORDER BY [Column2] DESC)
GO

--With Correlated Subquery Test
SELECT * FROM
((SELECT [Column1], Column2 AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST] AS [Outer]
WHERE [Column2] = (SELECT TOP 1 [Column2] FROM [Test].[dbo].[JoinTest] AS [Inner] WHERE [Inner].[Column3] < 0 AND [Inner].[Column1] = [Outer].[Column1] ORDER BY [Column2] DESC)) AS [TABLE1]
INNER JOIN
(SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST]
GROUP BY [Column1]) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[MaxColumn2] = [TABLE2].[MaxColumn2])
GO

2000用テストクエリ

/*
 2005.12.24 TimberLandChapel
 Self Join Test SQL Server 2000 / SQL Server 2005 for 2000
*/
--Table Drop Section
IF  EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[dbo].[JoinTest]') AND type in (N'U'))
DROP TABLE [dbo].[JoinTest]
GO

--Test Table Create
CREATE TABLE [dbo].[JoinTest](
[Column1] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column2] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column3] [nvarchar](50) COLLATE Japanese_CI_AS NULL
) ON [PRIMARY]
GO

--Test Data Insert
INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','1','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','2','-100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','3','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A002','1','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A002','2','-100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A002','3','-100')
GO

--Show Test Table
SELECT * FROM [JoinTest]
GO

--With Max Query
--This query returns right result.
SELECT * FROM
((SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0 GROUP BY [Column1]) AS [TABLE1]
INNER JOIN
(SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST]
GROUP BY [Column1]) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[MaxColumn2] = [TABLE2].[MaxColumn2])
GO

--Without Max Query
SELECT * FROM
((SELECT [Column1], [Column2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0) AS [TABLE1]
INNER JOIN
(SELECT [Column1], [Column2] FROM [Test].[dbo].[JoinTEST] WHERE [Column3] > 0 ) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[Column2] = [TABLE2].[Column2])
GO

--Max Query Only
SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0 GROUP BY [Column1]

--Correlated Subquery Test
SELECT [Column1], Column2 FROM [Test].[dbo].[JoinTEST] AS [Outer]
WHERE [Column2] = (SELECT TOP 1 [Column2] FROM [Test].[dbo].[JoinTest] AS [Inner] WHERE [Inner].[Column3] < 0 AND [Inner].[Column1] = [Outer].[Column1] ORDER BY [Column2] DESC)
GO

--With Correlated Subquery Test
SELECT * FROM
((SELECT [Column1], Column2 AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST] AS [Outer]
WHERE [Column2] = (SELECT TOP 1 [Column2] FROM [Test].[dbo].[JoinTest] AS [Inner] WHERE [Inner].[Column3] < 0 AND [Inner].[Column1] = [Outer].[Column1] ORDER BY [Column2] DESC)) AS [TABLE1]
INNER JOIN
(SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST]
GROUP BY [Column1]) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[MaxColumn2] = [TABLE2].[MaxColumn2])
GO

ちょっと，今詳しく試している時間が取れないので，

とりいそぎメモメモ。

ニュースグループの記事です。

2005 で自己結合＋サブクエリを JOIN すると，Hash Match／Marge Join されない実行プラン（TOP とかが動く）になります。

View に分離してもだめなところまでは確認。

--[2005 用]---
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[JoinTest]') AND type in (N'U'))
DROP TABLE [dbo].[JoinTest]
GO

CREATE TABLE [dbo].[JoinTest](
[Column1] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column2] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column3] [nvarchar](50) COLLATE Japanese_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','1','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','2','-100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','3','100')
GO

SELECT * FROM [JoinTest]
GO

SELECT * FROM
((SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0 GROUP BY [Column1]) AS [TABLE1]
INNER JOIN
(SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST]
GROUP BY [Column1]) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[MaxColumn2] = [TABLE2].[MaxColumn2])
GO
--[2005 用おわり]---

--[2000 用]---
IF  EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[dbo].[JoinTest]') AND type in (N'U'))
DROP TABLE [dbo].[JoinTest]
GO

CREATE TABLE [dbo].[JoinTest](
[Column1] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column2] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
[Column3] [nvarchar](50) COLLATE Japanese_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','1','100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','2','-100')

INSERT INTO [Test].[dbo].[JoinTest] ([Column1],[Column2],[Column3])
     VALUES ('A001','3','100')
GO

SELECT * FROM [JoinTest]
GO

SELECT * FROM
((SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM
[Test].[dbo].[JoinTEST] WHERE [Column3] < 0 GROUP BY [Column1]) AS [TABLE1]
INNER JOIN
(SELECT [Column1], Max(Column2) AS [MaxColumn2] FROM [Test].[dbo].[JoinTEST]
GROUP BY [Column1]) AS [TABLE2]
ON
[TABLE1].[Column1] = [TABLE2].[Column1]
AND [TABLE1].[MaxColumn2] = [TABLE2].[MaxColumn2] )
--[2000 用おわり]---

Visual Studio 2005，SQL Server 2005 の日本語製品版が

ついに MSDN サブスクリプションダウンロードから取得できるようになりました。

長かった。

でもたどり着きましたね。

関係者のみなさん，ありがとうございます。

さて，ここから本格的に使い倒していくシナリオがスタートしますよ。

内閣官房が運営している「情報セキュリティセンター」がいよいよ政府のセキュリティ基準の確固たる策定に向けて動き出す。

12月半ばにも「第1次情報セキュリティ基本計画(仮称)」を策定すると発表して，

この基本計画にはパブリックコメントによる意見召集も行うそうだ。

セキュリティは常に技術とのいたちごっこ。

一度作ったら万能という方法論はあまりない。（基本的な考え方は普遍だけれど）

どれだけスピードをあげて実行力のある基準で防御を固められるかが見所でしょうか？

大いに議論の余地がありそうです。

TLC の４コマ劇場に「トップハンドラ」を追加しました。

またしても久しぶりの更新です。

どうぞー。

フルサイズはこちら ↓

http://blogs.timberlandchapel.com/blogs/timberlandchapel/articles/530.aspx

４コマのバックナンバーはこちら ↓

http://timberlandchapel.com/commic.html

第３回 TLC.com Workshop 開催決定。

http://blogs.timberlandchapel.com/blogs/workshop/archive/2005/12/06/519.aspx

次回 Workshop の開催が決定しました。

今回はえムナウさんがセッションリーダーを務めてくださいます。

詳細は，追って後ほど。

ロゴはこちらから

http://blogs.timberlandchapel.com/blogs/workshop/archive/2005/12/10/528.aspx