IT27875: DB2 MAY COPY SELECTIVITY WHEN A SELECTIVITY CLAUSE IS USED ON A PREDICATE THAT'S COLUMN EQUIVALENT (C1=C2 AND C1=C3)

Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * all                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.1.4.7 and issue DB2_SELECTIVITY='CGSSEL1'      *
  ****************************************************************
  

Problem conclusion

• Fix is included in 11.1.4.7. To enable the fix, issue
  DB2_SELECTIVITY='CGSSEL1'
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IT27876 IT39702

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP