DB2 - Problembeschreibung
Problem IC69060 | Status: Geschlossen |
UNEXPECTED RESULTS FOR A QUERY THAT CONTAINS MULTIPLE DISTINCT'S AND REGISTRY SETTING FOR ENHANCED_MULTIPLE_DISTINCT IS ENABLED. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 | |
Problembeschreibung: | |
Incorrect results for a query that contains multiple distinct's and registry setting for ENHANCED_MULTIPLE_DISTINCT is turned on. . Description: If the following conditions are true: a) The DB2 instance is configured with DPF (multiple partitions) and the registry variable DB2_EXTENDED_OPTIMIZATION is set to ENHANCED_MULTIPLE_DISTINCT. b) A query is run that contains multiple distinct operations (see example) c) The query has predicates that need to be evaluated without an index. That is, in order to qualify or disqualify the row during predicate evaluation, it has to fetch the row and read the columns to compare the predicate rather than reading just the index (A sargable predicate). Then the query may return incorrect results. For example: select sum(distinct col2), avg(distinct col3), sum(distinct col4) from test where col5.data = 'abcde' group by col1; The wrong results is that even rows that should be disqualified based on the predicate col5.data = 'abcde', are still included in the query incorrectly. Thus, any sums or averages that are performed on the data may have extra data used in those aggregate operations. | |
Problem-Zusammenfassung: | |
apr ic69060 fixed >= v91 fpk10 | |
Local-Fix: | |
If all of the local predicates can be resolved by doing an index scan as start/stop key predicates, (and not as sargable predicates), then this is a possible workaround to the problem. | |
verfügbare FixPacks: | |
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows | |
Lösung | |
Incorrect results for a query that contains multiple distinct's and registry setting for ENHANCED_MULTIPLE_DISTINCT is turned on. . Description: If the following conditions are true: a) The DB2 instance is configured with DPF (multiple partitions) and the registry variable DB2_EXTENDED_OPTIMIZATION is set to ENHANCED_MULTIPLE_DISTINCT. b) A query is run that contains multiple distinct operations (see example) c) The query has predicates that need to be evaluated without an index. That is, in order to qualify or disqualify the row during predicate evaluation, it has to fetch the row and read the columns to compare the predicate rather than reading just the index (A sargable predicate). Then the query may return incorrect results. For example: select sum(distinct col2), avg(distinct col3), sum(distinct col4) from test where col5.data = 'abcde' group by col1; The wrong results is that even rows that should be disqualified based on the predicate col5.data = 'abcde', are still included in the query incorrectly. Thus, any sums or averages that are performed on the data may have extra data used in those aggregate operations. | |
Workaround | |
If all of the local predicates can be resolved by doing an index scan as start/stop key predicates, (and not as sargable predicates), then this is a possible workaround to the problem. | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC69061 IC69062 IC71085 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 07.06.2010 14.06.2011 14.06.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
9.1.0.10 |