DB2 - Problembeschreibung
Problem IC71982 | Status: Geschlossen |
NESTED-LOOP JOIN WITH EARLYOUT FOR GROUPBY CLAUSES, YIELDS INCORRECT RESULTS WHEN JOIN COLUMNS ARE OF DIFFERENT DATA TYPES | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
When a query that meets all the following conditions is executed, fewer results may be returned: - Several tables are joined using columns with different data types - Nested-loop join is selected as the join method - The output from the nested-loop join method is included into the Group by clause and the Early Out flag is marked as GROUPBY The following scenarios indicate the problem: 1. Joining two tables with different data types. To demonstrate this problem, create two tables for the test. Create the tables such that the columns being joined (t1.i1 and t2.i1) have different data types. create table t1 (i1 bigint not null, c2 char(250)) create index i1 on t1 (i1) create table t2 (i1 smallint not null, i2 bigint) create index i2 on t2 (i1,i2) 2. Run the query with different data types (bigint and smallint) in predicate and the result goes directly into the Group by clause. select t2.i1, max(t2.i2) from t1, t2 where t1.i1=t2.i1 group by t2.i1 3. When the Nested-loop join is selected as the join method. Access Plan: ----------- Total Cost: 22.8806 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 99 GRPBY ( 2) 22.8709 3 | 99 ^NLJOIN ( 3) 22.8655 3 /-----+------\ 99 12.1111 IXSCAN IXSCAN ( 4) ( 5) 0.0455361 7.57676 0 1 | | 99 1199 INDEX: DB2INST1 INDEX: DB2INST1 I1 I2 Q2 Q1 3) NLJOIN: (Nested Loop Join) Arguments: --------- EARLYOUT: (Early Out flag) GROUPBY Predicates: ---------- 3) Predicate used in Join Predicate Text: -------------- (Q2."I1" = Q1."I1") In addition, the following message may be seen in the db2diag.log: 2011-02-09-18.07.54.671541+060 I891882A2516 LEVEL: Severe PID : 4567040 TID : 23716 PROC : db2sysc 1 INSTANCE: instdb NODE : 001 DB : SAMPLE APPHDL : 1-48010 APPID: APP1 AUTHID : AUTH1 EDUID : 23716 EDUNAME: db2agent (SAMPLE) 1 FUNCTION: DB2 UDB, data services, sqlrxcon, probe:219 DATA #1 : String, 117 bytes ASSERTION FAILED!!! ASSERTION EXPRESSION: convertNumRc == 0 SOURCE FILE NAME: sqlrxcon.C SOURCE FILE LINE NUMBER: 208 DATA #2 : Codepath, 8 bytes 0 DATA #3 : String, 72 bytes CONTACT DB2 SERVICE, assert a numeric conversion has unexpectedly failed DATA #4 : String, 13 bytes convertNumRc: DATA #5 : Hex integer, 4 bytes 0x80160008 ... with the following stack: CALLSTCK: [0] 0x0900000005FDA0BC pdLog + 0xEC [1] 0x0900000005B3E91C sqlrxcon2__FP8SQLRXNLSP10sqlz_valueT2 + 0x210 [2] 0x0900000005B845CC @147@keypartnd__FP7SQLI_CBP10sqlz_valuePPcP17COMP_FUNC_ELEMENTPl + 0x24 [3] 0x09000000068CB03C sqliComparePrefixCompPg__FP7SQLI_CBP14SQLI_PAGE_DESCP8SQLD_KEYP1 2SQLI_KEYDATAP9SQLI_SLOTPlP14COMP_FUNC_VECTPc + 0xAF8 [4] 0x09000000068D43EC sqliComparePrefixCompPg__FP7SQLI_CBP14SQLI_PAGE_DESCP8SQLD_KEYP1 2SQLI_KEYDATAP9SQLI_SLOTPlP14COMP_FUNC_VECTPc@glue634 + 0x74 [5] 0x0900000005F2784C sqliCompareGeneric__FP7SQLI_CBP14SQLI_PAGE_DESCP8SQLD_KEYP12SQLI _KEYDATAP9SQLI_SLOTPlP14COMP_FUNC_VECTPc + 0x4 [6] 0x09000000068E6898 sqliSearchFromCache__FP7SQLI_CBP11SQLI_SFGLOBPUl + 0x46C [7] 0x090000000608A0E0 .sqlirdk.fdpr.clone.2009__FP8sqeAgentP10SQLI_IXPCRP9SQLD_IXCBUlP 8SQLD_KEYP12SQLI_KEYDATAP9sqli_scanPi + 0x50 [8] 0x0900000005F67564 .sqldIndexFetch.fdpr.clone.290__FP8SQLD_CCBP8SQLZ_RID + 0x1A4 [9] 0x0900000005F67D9C sqldRowFetch__FP8sqeAgentP8SQLD_CCBUlT3PP10SQLD_VALUEP8SQLZ_RIDT 3P12SQLD_ID_LISTP9SQLP_LSN8 + 0x55C Under some conditions, the instance may trap. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * When a query that meets all the following conditions is * * executed, fewer results may be returned: * * - Several tables are joined using columns with different * * data types * * - Nested-loop join is selected as the join method * * - The output from the nested-loop join method is included * * into the Group by clause and the Early Out flag is marked as * * GROUPBY * * * * The following scenarios indicate the problem: * * * * 1. Joining two tables with different data types. To * * demonstrate this problem, create two tables for the test. * * Create the tables such that the columns being joined (t1.i1 * * and t2.i1) have different data types. * * create table t1 (i1 bigint not null, c2 char(250)) * * create index i1 on t1 (i1) * * create table t2 (i1 smallint not null, i2 bigint) * * create index i2 on t2 (i1,i2) * * 2. Run the query with different data types (bigint and * * smallint) in predicate and the result goes directly into the * * Group by clause. * * select t2.i1, max(t2.i2) from t1, t2 where t1.i1=t2.i1 group * * by t2.i1 * * 3. When the Nested-loop join is selected as the join method. * * * * Access Plan: * * ----------- * * Total Cost: 22.8806 * * Query Degree: 1 * * Rows * * RETURN * * ( 1) * * Cost * * I/O * * | * * 99 * * GRPBY * * ( 2) * * 22.8709 * * 3 * * | * * 99 * * ^NLJOIN * * ( 3) * * 22.8655 * * 3 * * /-----+------\ * * 99 12.1111 * * IXSCAN IXSCAN * * ( 4) ( 5) * * 0.0455361 7.57676 * * 0 1 * * | | * * 99 1199 * * INDEX: DB2INST1 INDEX: DB2INST1 * * I1 I2 * * Q2 Q1 * * 3) NLJOIN: (Nested Loop Join) * * Arguments: * * --------- * * EARLYOUT: (Early Out flag) * * GROUPBY * * Predicates: * * ---------- * * 3) Predicate used in Join * * Predicate Text: * * -------------- * * (Q2."I1" = Q1."I1") * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 UDB Version 9.5 fix pack 7. * **************************************************************** Users affected: All users. Problem Description: Using the nested-loop join method, with earlyout indicator for groupby clauses, yields incorrect results when join columns are of different data types. . The problem occurs when the nested-loop join method, along with EARLYOUT indicator for groupby clauses, is used with a join clause that joins two columns with different data types. . | |
Local-Fix: | |
Execute the following and then recycle the instance: db2set DB2_REDUCED_OPTIMIZATION=NO_NLJN_EO_FOR_GB | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows | |
Lösung | |
This problem was first fixed in DB2 UDB Version 9.5 fix pack 7. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC73581 IC73582 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 18.10.2010 05.01.2011 18.02.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP7 | |
Problem behoben lt. FixList in der Version | |
9.1.0.7 | |
9.5.0.7 |