[解決済み】PL/SQL SELECTに複数のCOUNT(DISTINCT xxx)を指定すると、予期しない結果になる。

質問

Oracle 11gアプリケーションのクエリを作成しようとしているのですが、問題が発生しました。

実際のシナリオを単純化して理解しやすくします（クライアントのデータを保護するためでもあります）。

• テーブルAはベースとなるテーブルです。このテーブルには既知の識別子があり、クエリに渡します。
• テーブルAの各エントリに対して、テーブルBに複数のエントリが存在する場合がある。テーブルBには、私が関心を持つ値が含まれている。
• テーブルBの各エントリに対して、テーブルCにも複数のエントリが存在する可能性がある。テーブルCには、私が関心を持つ別の値が含まれている。
• また、テーブルCにある目的の値と一致するかどうかわからない値のリストを含むXMLスニペットも持っています。
• このクエリはXMLに対して外部結合を行い、一致する値がある場合はその値を再度返し、そうでない場合はNULLを返します。

私がやりたいことは、渡した識別子、BとCの一意な値の数、結合のXML部分から一意な（そして非NULLの）値の数を取得することです。

現在の私のクエリーは

SELECT
    a.ID
  , COUNT(DISTINCT b.VAL) AS B_VAL
  , COUNT(DISTINCT c.VAL) AS C_VAL
  , COUNT(DISTINCT xml.VAL) AS XML_VAL
FROM a, b, c,
  XMLTABLE('/field1/collection/value' passing my_xml_type
    COLUMNS VAL VARCHAR2(50) PATH '.') xml
WHERE
      a.ID = b.SOME_ID
  AND b.OTHER_ID = c.OTHER_ID
  AND c.VAL = xml.VAL (+)

ここで、カウントを忘れて行だけを返すとすると、結果セットの例は次のようになります。

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      abc       123       123
X      abc       456       null
X      abc       789       789
X      abc       789       789

DESIRED: これで、distinct countsをしたいときに、返してくれるようにしたい。

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      1         3         2

ACTUALです。 しかし、私はそれらすべてをCOUNT(DISTINCT ...)として持っているとき、これは私が得ているものです。

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      1         1         1

ALTERNATIVE ...そして、もし私がカウントからDISTINCTを取ったら、私は得る。

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      1         4         3

DISTINCTは特定のB_VALの中だけで動作しているように見えますが、それを取り除くとすべての行で動作しますが、一意性が考慮されないのはなぜですか?

サブクエリとしてすべての結合を複製する必要がない、別の方法はありますか？私は完全にポイントを外しているのでしょうか？

(なお、私はDB開発者では全くなく、ただ手伝いに引っ張り出されただけなので、これが簡単な問題だったらごめんなさい...) 私は投稿する前に、Googleで検索し、このサイトを見て、答えを探しました。）

ありがとうございます。

XMLテーブル結合を取り除くと、B_VALとC_VALの間でcount distinctが問題なく動作することがわかりました...。おそらく、OracleがXMLテーブル結合を処理する方法がおかしいのでしょうか？

解決方法は？

Vincent のテストケースは 10.2.0.3 と 11.2.0.2 で動作しているので、もし 11g の以前のバージョンであれば、これはバグ 8816675: XMLexists query returns wrong results with a select DISTINCT の可能性があります。このバグの例では、以下の問題について言及しています。 count(distinct) . あなたは明示的にXMLexistsを使っているわけではありませんが、このバグはタイトルが示すよりも広範囲に影響を及ぼしているか、あるいは水面下で使われている可能性があります。

もしこれが問題で、パッチが当てられないのであれば、カウントしないバージョンをラップすることで回避できるかもしれませんが、それでもきれいなものではありませんね。

SELECT
    A_ID
    , COUNT(DISTINCT B_VAL) AS B_VAL
    , COUNT(DISTINCT C_VAL) AS C_VAL
    , COUNT(DISTINCT XML_VAL) AS XML_VAL
FROM (
SELECT a.ID as A_ID, b.VAL as B_VAL, c.VAL as C_VAL, xml.VAL as XML_VAL
FROM a, b, c
    , XMLTABLE('/field1/collection/value' passing my_xml_type
        COLUMNS VAL VARCHAR2(50) PATH '.') xml
WHERE a.ID = b.SOME_ID
AND b.OTHER_ID = c.OTHER_ID
AND c.VAL = xml.VAL (+)
)
GROUP BY A_ID;