|
Problem IT18745
|
Status: Closed |
DB2 MIGHT PRODUCE SQL0901N WHEN EXECUTING QUERY WITH GROUPBY ANDOUTER JOIN |
| product: |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 |
| Problem description: |
DB2 might produce SQL0901N error message when executing a query
with the following pattern.
1) The query has one or more outer join(s)
2) The null producing subselect of the outer join has a group by
clause
3) The subselect below the group by has two or more identical
expressions in the projected columns.
eg.
SELECT T2.*
FROM T1 LEFT OUTER JOIN
( select T2.C1, T2.C2,
sum(CASE WHEN (T2.c3='20610') THEN 1 ELSE 0 END) as
col1,
sum(CASE WHEN (T2.c3='20610') THEN 1 ELSE 0 END) as
col2
FROM T2
GROUP BY T2.C1, T2.C2) T2
ON
(T1.C1 = T2.C1 and T2.C2 = T1.C2);
Stack trace produced:
<StackTrace>
pthread_kill + 0xD4
sqloDumpEDU + 0xC4
sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 + 0x110
sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x72C
sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x28
sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x234
sqlnn_erds__FiN41e + 0x140
sqlnr_gbpu_thru_OJ__FCP9sqlnq_qurPiCP3loc + 0x2F54
sqlnr_gbpu_thru_OJ__FCP9sqlnq_qurPiCP3loc + 0x354
sqlnr_gbpu_thru_OJ__FCP9sqlnq_qurPiCP3loc + 0x12C
sqlnr_qrwprep_phase2__FP3locPi + 0xE04
sqlnr_prep2_action__FP10sqlnr_qrwaPiP14sqlnr_progress + 0x4C
.sqlnr_comp.fdpr.clone.1544__FPiiP16sqlnr_rule_stateP10sqlnr_qrw
aP14sqlnr_progress + 0x1E8
sqlnr_seq__FPiP10sqlnr_qrwaP14sqlnr_progressP12sqlnr_rclass +
0xC4
sqlnr_rcc__FiP10sqlnr_qrwaPiP14sqlnr_progress + 0xC4
sqlnr_exe__FP9sqlnq_qur + 0x1174
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_enviT7PP9sqlnq_qur + 0x2CEC
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_env + 0x38
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
LOCK_INFOP16sqlra_cached_varPiT11_Pb + 0x868
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4U
sUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14SQLP_LOC
K_INFOPP
16sqlra_cached_varT12_bT19_Pb + 0x10C8
sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x2194
sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x190
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface + 0xDE50
@72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x1B84
@72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x1B84
@72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x1778
@72@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA8
@72@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x5F8
RunEDU__8sqeAgentFv + 0x4BA88
RunEDU__8sqeAgentFv + 0x120
EDUDriver__9sqzEDUObjFv + 0x134
sqloEDUEntry + 0x390
</StackTrace> |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 11.1 Mod 2 Fix Pack 2 or higher *
**************************************************************** |
| Local Fix: |
Workaround: None |
| available fix packs: |
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 11.1 Mod 2 Fix Pack 2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.01.2017
28.06.2017
28.06.2017 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
