DB2 FOR LUW / DB2FORLUW / B10 - DB2

Incorrect results may be observed for queries with following
characteristic:
- query with UNION
- UNION is joined to SELECT DISTINCT with predicate
- one leg of union is a join, rows in joined tables are not
unique

In optimized statement DISTINCT is pulled up above the UNION
level, what may cause wrong results.

Sample query:

select *
from
(SELECT inf.T1C10
        FROM T1 inf
  UNION ALL
  ( SELECT A.T2C1
      FROM T2 a
    INNER JOIN T3 R
      ON A.T2C1 = r.T3C1
    WHERE ( a.T2C4 IS NOT NULL )
  )
) MAIN
INNER JOIN (SELECT DISTINCT T4C2,T4C3 FROM t4 WHERE T4C1 IN(
'A1', 'A2')) acc
ON    (1=1)
;

Optimized Statement:
-------------------
SELECT
  DISTINCT Q10.$C0 AS "T1C10",
  Q3.T4C2 AS "T4C2",
  Q3.T4C3 AS "T4C3"
FROM
  REPRO.T4 AS Q3,
  (SELECT
     Q9.$C0
   FROM
     (SELECT
        Q5.T2C1
      FROM
        REPRO.T3 AS Q4,
        REPRO.T2 AS Q5
      WHERE
        (Q5.T2C1 = Q4.T3C1) AND
        Q5.T2C4 IS NOT NULL
      UNION ALL
      SELECT
        Q7.T1C10
      FROM
        REPRO.T1 AS Q7
     ) AS Q9
  ) AS Q10
WHERE
  Q3.T4C1 IN ('A1', 'A2')

As a workaround DISTINCT can be rewritten to GROUP BY.
select *
from
(SELECT inf.T1C10
        FROM T1 inf
  UNION ALL
  ( SELECT A.T2C1
      FROM T2 a
    INNER JOIN T3 R
      ON A.T2C1 = r.T3C1
    WHERE ( a.T2C4 IS NOT NULL )
  )
) MAIN
INNER JOIN (SELECT T4C2,T4C3 FROM t4 WHERE T4C1 IN( 'A1', 'A2')
GROUP BY T4C2,T4C3 ) acc
ON    (1=1)
;

****************************************************************
* USERS AFFECTED:                                              *
* all                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to DB2 Version 11.1 Fix Pack m4fp6 or later          *
****************************************************************

****************************************************************
* USERS AFFECTED:                                              *
* all                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to DB2 Version 11.1 Fix Pack m4fp6 or later          *
****************************************************************

Problem was first fixed in DB2 Version 11.1 Fix Pack m4fp6

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

04.11.2020
15.03.2021
29.03.2021