DB2 - Problembeschreibung
Problem IT01256 | Status: Geschlossen |
QUERY MIGHT HAVE OR PREDICATE WRONGLY REMOVED RESULTING IN EXTRA ROWS IN THE RESULTS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
A query might return additional rows when it has an OR predicate that meets the following conditions: - The query has an OR predicate, called orPrd, that is either present in a table inner join query block or simply applied to a single table scan. - The orPrd has an IN predicate, called inPrd. - The inPrd predicate has pattern of <simple_base_columnX> IN (<column_or_literal1>, <column_or_literal2>, ...), where one of the <column_or_literal> is <simple_base_columnX> itself. An example predicate is "col1 in (constant1, constant2, col1, col2)", where col1 is not only IN predicate column but also an INlist element. - The inPrd column, <simple_base_columnX> as above for example, is defined as nullable. There is table rows whose columnX is the NULL value. - The query has no predicate other than orPrd that can reject the columnX Null value. For example, if query has relational comparison predicate such as columnX = columnY, columnX NULL values will be filtered out. To verify if the query hits this problem, use the db2exfmt or db2explin commands to generate db2exfmt a query explain plan. Search the query explain plan to verify if the original query's OR predicate is present in the section "Optimized Statement". This problem might cause the orPrd predicate to be incorrectly removed. This might result in the columX NULL value being incorrectly returned to the consumer query block. This error might make the final result inaccurate. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 10.5.0.4. * **************************************************************** | |
Local-Fix: | |
There are two workarounds for this issue: - Reduce query optimization to 0 or 1. A sample command is "db2 set current query optimization 0". - Manually rewrite that OR predicate. Change it from "<simple_base_columnX> IN (<column_or_literal1>, <column_or_literal2>, ...)" to "<simple_base_columnX> IS NOT NULL". | |
Lösung | |
The problem is first fixed in DB2 version 10.5.0.4. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 24.04.2014 15.09.2014 08.04.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.4 |