DB2 FOR LUW / DB2FORLUW / B50 - DB2

Db2 may produce incorrect results when executing a query
containing the following properties:

- The query contains an inner join.
- The join columns for the inner join have different data types
but are both floating-point data types.
- The query contains a local predicate with either operand as
one of the join columns.
- The other operand of the local predicate has a larger
precision than the join column that isn't referenced in the
local predicate
- The precision of the join column used in the local predicate
is equal to or smaller than the precision of the other join
column

For example:

CREATE TABLE T1 (DF16 DECFLOAT(16));
CREATE TABLE T2 (DEC8 DECIMAL(8));

INSERT INTO T1 VALUES 1, 2;
INSERT INTO T2 VALUES 1, 2;

SELECT T2.DEC8 FROM T1 INNER JOIN T2 ON T1.DF16 = T2.DEC8 WHERE
T2.DEC8 = CAST(1 AS DECIMAL(20));

Expected Results:

DEC16
----------
        1.

  1 record(s) selected.

Actual Results:

DEC16
----------
        1.
        2.

  2 record(s) selected.

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.5 Mod 2 Fixpack 0 or higher                *
****************************************************************

If possible, rewrite the local predicate so that both operands
use the same (or smaller) precision as the join column that
isn't referenced in the local predicate. For example:

SELECT T2.DEC8 FROM T1 INNER JOIN T2 ON T1.DF16 = T2.DEC8 WHERE
T2.DEC8 = CAST(1 AS DECIMAL(16));

In this example, the constant operand of the local equality
predicate has been changed to a DECIMAL(16) to match the
precision of column DF16 (which is a DECFLOAT(16)).

not known / see Local fix

forerunner  : IT30466 
follow-up : 

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

01.10.2019
18.02.2020
18.02.2020