IT18745: DB2 MIGHT PRODUCE SQL0901N WHEN EXECUTING QUERY WITH GROUPBY ANDOUTER JOIN

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
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error 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>
  

Local fix

• Workaround: None
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 11.1 Mod 2 Fix Pack 2 or higher               *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 11.1 Mod 2 Fix Pack 2
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels