DB2 - Problembeschreibung
Problem IT01616 | Status: Geschlossen |
QUERIES WITH XMLTABLE FUNCTIONS MIGHT RETURN INCORRECT RESULTS WHEN MORE THAN ONE EQUAL PREDICATE IS USED IN WHERE CLAUSE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
If more than one XMLTABLE columns are selected and there is a predicate on the XMLTABLE in the where clause, then incorrect results might be returned. In following query columns NAME and AREA in the same XMLTABLE (T2 in this case) are filtered by equal predicates in "where" clause and some records might be omitted in the final results: select T2.NAME, T2.AREA from MOVE, XMLTABLE('$MOVEINFO/listing/locations/building' Columns "DEPT" VARCHAR(15) PATH '@dept') as T1, XMLTABLE('$MOVEINFO/listing/items/item' Columns "NAME" VARCHAR(20) PATH 'name', "AREA" VARCHAR(10) PATH 'area') as T2 where T2.NAME = 'laser printer' and T2.AREA = 'common' As a best practice, it is better to not put the XMLTABLE predicate in "where" clause, as the original query does. Use the XPATH predicate instead. After pushing down the predicate to XPATH, the predication can be done when doing navigation, which generally improves performance. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 9.7.0.10. * **************************************************************** | |
Local-Fix: | |
A) Pushing down predicates to XPATH is a recommended solution. sample predicates from one of where clause: where T2.NAME = 'laser printer' can be pushed down to XPath instead: XMLTable('$MOVEINFO/listing/items/item[name="laser printer"]' Same for 'AREA'. B) Disabling Multiple Extraction Points (MEP) using db2set variable: db2set DB2_COMPILER_XML_OVERRIDES=1 db2stop force && db2start | |
Lösung | |
The problem is first fixed in DB2 version 9.7.0.10. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 07.05.2014 10.11.2014 17.02.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.0.10 | |
Problem behoben lt. FixList in der Version | |
9.7.0.10 |