DB2 FOR LUW / DB2FORLUW / A50 - DB2

Db2 may return incorrect results when executing IUD statements
containing a subquery with the following properties:

- The subquery contains correlated predicates.
- The subquery contains aggregation.
- The subquery references another subquery or
common-table-expression that returns unique records (for example
via aggregation or DISTINCT).

For example:

insert into mytable3
  with mycte as (
    select mytable.c1 c1, mytable2.c1 c2
      from mytable, mytable2
      where mytable.c2 = mytable2.c2
      group by mytable.c1, mytable2.c1
    )
  select distinct c1
    from (select c1,
          (select sum(c2)
             from mycte
             where mycte.c1 = mycorr.c1 and mycte.c2 = 1) as
mysum
            from mytable mycorr )
    where mysum > 0;

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 10.5 Fix Pack 11 or higher                    *
****************************************************************

The incorrect results can be avoided by using the following
registry setting via db2set or an Optimization
profile/guideline:
DB2COMPOPT=NO_GBPRDPU

not known / see Local fix

forerunner  : 
follow-up : IT29981 IT29982 

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

12.08.2019
25.02.2020
25.02.2020