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

Subscribe

You can track all active APARs for this component.

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));
```

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
```

APAR Information

LI72727

• Reported component name

DB2 UDE ESE LIN

5765F4104

910

CLOSED PER

NoPE

NoHIPER

NoSpecatt

2007-11-20

2009-12-02

2009-12-02

Modules/Macros

• ```ENG_SQNO
```

Fix information

• Fixed component name

DB2 UDE ESE LIN

5765F4104

Applicable component levels

• R910 PSN

UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Modified date:
16 October 2021