DB2 - Problem description
Problem IT15362 | Status: Closed |
WRONG RESULT IS POSSIBLE IF SQL CONTAINS "NULL IS NULL" IN SUB-BRANCH OF AN OR | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
DB2 may produce wrong result if the following conditions are met: 1) The query has OR predicate 2) At least one of the branches of OR has AND predicate 3) The AND predicate has NULL IS NULL as one branch predicate e.g. select * from t where (NULL IS NULL and c1='Y' ) or (c1='N' ) This issue only exists on V10.5fp7 . | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * This issue only exists on V10.5fp7 * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to V10.5fp8 * **************************************************************** | |
Local Fix: | |
1.Remove "NULL IS NULL" predicate from the query. 2. Change the value of DFT_QUERYOPT to 3 (NOTE: We may have performance issue with this setting ) db2 update db cfg using DFT_QUERYOPT 3 immediate 3. Set the QUERYOPT value specific to the query using Optimizer Guidelines: db2set DB2_OPTPROFILE=YES db2 terminate db2stop db2start db2 connect to testdb db2 "select * from tmp where (NULL IS NULL and IAN_STATUS='Y' ) or (1 IS NULL and IAN_MOVE_STATUS='' ) /* <OPTGUIDELINES> <QRYOPT VALUE='3'/> </OPTGUIDELINES> */" | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 19.05.2016 26.09.2016 07.11.2016 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |