トラブルシューティング：集合関数と外部参照

SQL Anywhere は、サブクエリでの集合関数の使用法を明確に規定した SQL/2008 標準に準拠しています。この変更は、Adaptive Server Anywhere の以前のバージョン用に記述された文の動作に影響を与えます。以前は有効だったクエリでエラーメッセージが生成され、結果セットが変わる可能性があります。

集合関数がサブクエリに使用され、集合関数の参照先カラムが外部参照の場合は、集合関数全体が外部参照として扱われます。集合関数はサブクエリ内ではなく外部ブロック内で計算され、サブクエリ内の定数となります。

サブクエリに含まれる外部参照の集合関数の使用には、次の制限が適用されます。

外部参照の集合関数を指定できるのは、SELECT リストまたは HAVING 句にあるサブクエリの中だけです。また、これらの句は、外部ブロックの隣になくてはなりません。
外部参照の集合関数に指定できるのは、1 つの外部カラム参照だけです。
ローカルカラム参照と外部カラム参照を、同じ集合関数に混在させることはできません。

新しい標準に関連する問題は、ローカル参照しか含まないように集合関数を書き換えると回避することができます。たとえば、サブクエリ (SELECT MAX(S.y + R.y) FROM S) がローカルカラム参照 (S.y) と外部カラム参照 (R.y) の両方で構成されていると、不正になります。このサブクエリは (SELECT MAX(S.y) + R.y FROM S) に書き換えることができます。書き換えると、集合関数はローカルカラム参照だけを持つことになります。外部参照の集合関数が SELECT や HAVING 以外の句に指定されている場合にも、同様の書き換えを使用できます。

例 1

次のクエリの場合、SQL Anywhere 7 以前のバージョンでは有効な結果が生成されました。

SELECT Name, 
      ( SELECT SUM( p.Quantity )
       FROM SalesOrderItems )
FROM Products p WHERE p.ID = 300;

Name	SUM(p.Quantity)
Tee shirt	30,716

SQL Anywhere 8 以降のバージョンでは、同じクエリを実行したときに、関数またはカラムの参照が GROUP BY 句の中になければならないというエラーメッセージが生成されます。SUM(p.Quantity) は外部参照として扱われ、外部クエリブロック内で計算されるため、この文は有効でなくなっています。上記のクエリは次のクエリと同等です。

SELECT Name,
       SUM( p.Quantity ) AS Z,
       ( SELECT Z 
            FROM SalesOrderItems )
   FROM Products p WHERE p.ID = 300;

ただし、集合関数は外部クエリブロック内で計算されるようになったため、外部クエリブロックはグループクエリとして扱われ、カラム名を SELECT リストの GROUP BY 句に記述する必要があります。その意味でもこのクエリは無効で、同じエラーメッセージが生成されます。SQL Anywhere 8 以降のバージョンで SQL Anywhere 7 以前のバージョンの最初のクエリと同じ結果セットを返すには、次のクエリを使用してください。

SELECT Name,
p.Quantity * ( SELECT COUNT( * ) FROM SalesOrderItems )
FROM Products p WHERE p.ID = 300;

例 2

SQL Anywhere 7 以前のバージョンでは、SUM(p.Quantity) はネストされた SELECT クエリブロックの内部で計算され、p.Quantity のみが外部参照として扱われたため、次のクエリでは 30,716 という結果が生成されました。

SELECT ( SELECT FIRST SUM( p.Quantity ) FROM SalesOrderItems ) AS ss 
FROM Products p WHERE p.ID = 300;

SQL Anywhere 8 以降のバージョンでは、SUM(p.Quantity) は外部参照として扱われ、外部クエリブロックの内部で計算されるため、同じクエリは 28 という結果を返します。つまり、上記のクエリは次のクエリと同等です。

SELECT DT.ss
FROM ( SELECT SUM( p.Quantity ) AS asum,
             ( SELECT FIRST asum FROM SalesOrderItems ) AS ss
      FROM Products p WHERE p.ID = 300 ) AS DT;

SQL Anywhere 8 以降のバージョンで SQL Anywhere 7 以前のバージョンの最初のクエリと同じ結果を返すには、次のクエリを使用してください。

SELECT p.Quantity * ( SELECT COUNT( * ) FROM SalesOrderItems ) AS ss
FROM Products p WHERE p.ID = 300;

参照

DocCommentXchange で意見交換できます