DB2 - Problem description
Problem IT30466 | Status: Closed |
DB2 MAY PRODUCE INCORRECT RESULTS WHEN EXECUTING QUERIES CONTAINING JOINS WITH DIFFERING FLOATING-POINT PRECISIONS | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
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. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * DB2 may produce incorrect results when executing queries * * containing joins with differing floating-point precisions * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to version v11.1m4fp5 * **************************************************************** | |
Local Fix: | |
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)). | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT30467 IT30469 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 01.10.2019 01.03.2020 01.03.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |