DB2 FOR LUW / DB2FORLUW / A50 - DB2

This behavior may happen when the following conditions are met;

- Registry variable DB2_SELECTIVITY has been set to YES
- There are more than 1 equivalent predicates using the same
column on the left so it's eligible for column equivalency

Furthermore, DB2 may under-estimate cost when the following
addditonal conditions are satisfied:
- Registry variable DB2_CORRELATED_PREDICATES=FORCE_CGS. This is
the default setting in 11.1 
- Column group statics are collected on the columns in the join
equality predicates such that DB2 optimizer may adjust filter
factor.

To reproduce,

create table t1(c1 int);
create table t2(c1 int);
create table t3(c1 int);

And get a plan and check the optimized query;


Original Statement:
------------------
select
  *
from
  t1,
  t2,
  t3
where
  t1.c1=t2.c1 selectivity .12 and
  t1.c1=t3.c1 selectivity .13


Optimized Statement
-------------------
SELECT
  T1.C1 AS "C1",
  T2.C1 AS "C1",
  T3.C1 AS "C1"
FROM
  JINHYON1.T3 AS T3,
  JINHYON1.T2 AS T2,
  JINHYON1.T1 AS T1
WHERE
  (T1.C1 = T3.C1 SELECTIVITY 0.130000) AND
  (T3.C1 = T2.C1 SELECTIVITY 0.120000)

The SELECTIVITY .12 is copied to T3.C1 = T2.C1 which is
incorrect

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Will be fixed in DB2 v11.1.                                  *
****************************************************************

N/A

not known / see Local fix

forerunner  : IT27875 
follow-up : 

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

24.01.2019
02.10.2020
02.10.2020