LI72727: IMPROVE CARDINALITY ESTIMATE FOR PREDICATES WITH COMPLEX EXPRESS IONS THAT EVALUATE TO A CONSTANT ON THE SAME COLUM AS JOIN PRED.

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• Improve the cardinality estimate when we have predicates with
  complex expressions that evaluate
  to a constant at runtime on the same column as a join predicate.
  Here are a couple simple examples
  that underestimate the cardinality because  the predicates on
  the same column are treated
  independently:
  .
  -- example using complex expression
  select *
  from t1, t2
  where t1.c5=t2.c5
    and t1.c5 = (SELECT
                   CASE
                   WHEN dayofweek(current date)= 1
                   THEN Current Date - 2 Day
                   WHEN dayofweek(current date)= 2
                   THEN Current Date - 3 Day
                   ELSE Current Date - 1 day
                   END CASE
                 FROM SYSIBM.SYSDUMMY1);
  .
  -- another example of a complex expression that evaluates to a
  constant at runtime
  select count(*)
  from filter.tunnel1 t1, filter.tunnel2 t2
  where t1.c3 = t2.c3
    and t1.c3 = ? || cast(current date as char(10));
  

Local fix

Problem summary

• Improve the cardinality estimate when we have predicates with
  complex expressions that evaluate
  to a constant at runtime on the same column as a join predicate.
  Here are a couple simple examples
  that underestimate the cardinality because  the predicates on
  the same column are treated
  independently:
  .
  -- example using complex expression
  select *
  from t1, t2
  where t1.c5=t2.c5
    and t1.c5 = (SELECT
                   CASE
                   WHEN dayofweek(current date)= 1
                   THEN Current Date - 2 Day
  
                   WHEN dayofweek(current date)= 2
                   THEN Current Date - 3 Day
                   ELSE Current Date - 1 day
                   END CASE
                 FROM SYSIBM.SYSDUMMY1);
  .
  -- another example of a complex expression that evaluates to a
  constant at runtime
  select count(*)
  from filter.tunnel1 t1, filter.tunnel2 t2
  where t1.c3 = t2.c3
    and t1.c3 = ? || cast(current date as char(10));
  

Problem conclusion

• apar: li72727
  module: engn_sqno
  fixed >= v91 fpk8
  

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:

  LI72728

Modules/Macros

• ENG_SQNO
  

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSN

     UP