DB2 - Problembeschreibung
Problem IC96725 | Status: Geschlossen |
CASE EXPRESSION PREDICATES MIGHT BE INCORRECTLY REWRITTEN in DB2 v10.1 Fixpack 3 | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
A CASE expression predicate might be incorrectly rewritten. The following conditions must be fulfilled to cause an incorrect case expression predicate to occur: 1. The predicate is <CASE expression> IS NULL. 2. The CASE expression is in the form of "CASE WHEN <col1> <relop1> <literal11> THEN <literal12> WHEN <col2> <relop2> <literal21> THEN <literal22> WHEN <col3> <relop3> <literal31> THEN <literal32> ... ELSE <colX> END". If N is any positive integer number, then <relopN> can be any comparison operator out of ("<", "<=", "<>", "=", ">=", ">") as well as IS NULL and IS NOT NULL. <literalNM> can be any constant, special register, host variable, or parameter marker. colN is a simple column. The same column can be repeated in WHEN clauses. colX from the ELSE clause must be also present in at least one WHEN clause. Also, colX must be defined as nullable. The symptom of the problem is the above predicate is mistakenly transformed to an OR predicate with a subterm that misses "colX IS NULL." In those rows whose colX is NULL, this might result in those rows being incorrectly filtered by the rewrite predicate so the query block returns less rows. Running the NULLIF command might also lead to this problem. NULLIF(col1, <literal1>) is same as CASE WHEN <col1> = <literal1> THEN NULL ELSE <col1> END. If your database is an Oracle compatible database, using the predicate NULLIF(col,1") IS NULL will not have the CASE expression predicate problem. Issue will not occur in DB2 v10.1 Fixpack 2 and lower, but will affect DB2 v10.1 Fixpack 3 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 v10.1 FP4. * **************************************************************** | |
Local-Fix: | |
1. Change query optimization level to 0 or 1. The command is "db2 set current query optimization 0" 2. If all columns in CASE expression are identical and all <relop> are "=", manually rewrite the CASE expression predicate to DECODE(colX, <literal11>, <literal12>, <lteral21>, <literal22>, <literal31>, <literal32>, ..., <colX>) IS NULL. | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
Fixed in DB2 v10.1 FP4. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC97615 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 08.10.2013 08.05.2014 26.05.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.4 |