LI72795: Duplicate ORDER BY column in the window order clause of an OLAP function causes a crash

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• DB2 V9.5 may crash when compiling a query
  containing an OLAP function with a duplicate ORDER BY column in
  the window order clause. For example:
  
  SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM
  MYTABLE
  
  The stack trace after the trap will show entries for
  "sqlnq_pid5arity" and "sqlnq_normalize_scalag_PB_and_OB_clause":
  
  00002B2B16F36A61 ossDumpStackTrace + 0x007d
          (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1)
  00002B2B16F3275E _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x00aa
          (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1)
  00002B2B13452E20 sqlo_trce + 0x0342
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B1348FB3F sqloEDUCodeTrapHandler + 0x0105
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B10E96C10 __invoke_dynamic_linker__ + 0x0380
          (/lib64/libpthread.so.0)
  00002B2B11EBEC54 _ZN9sqlnq_pid5arityEv + 0x0000
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B12EFBDEA
  _Z39sqlnq_normalize_scalag_PB_and_OB_clauseP9sqlnq_pidS0_P3loc +
  0x0116
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B12EFF140
  _Z22sqlnq_scalar_aggregatePP8stknode_i10actiontypePhP3loc +
  0x3022
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  
            PROBLEM SUMMARY:
  This is a new defect in DB2 V9.5 LUW.  An optimization
  introduced in V9.5 to remove redundant ORDER BY columns in
  aggregation specifications can cause an invalid memory access
  when the specification contains two or more duplicate ORDER BY
  columns.  Earlier versions of DB2 are not affected by this
  problem.
  

Local fix

• Where possible, rewrite your query to eliminate the duplicate
  column specification.  For example,
  
  SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM
  MYTABLE
  
  becomes:
  
  SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2) FROM
  MYTABLE
  

Problem summary

• ERROR DESCRIPTION: DB2 V9.5 may crash when compiling a query
  containing an OLAP function with a duplicate ORDER BY column in
  the window order clause. For example:
  
  SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM
  MYTABLE
  
  The stack trace after the trap will show entries for
  "sqlnq_pid5arity" and "sqlnq_normalize_scalag_PB_and_OB_clause":
  
  00002B2B16F36A61 ossDumpStackTrace + 0x007d
          (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1)
  00002B2B16F3275E _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x00aa
          (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1)
  00002B2B13452E20 sqlo_trce + 0x0342
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B1348FB3F sqloEDUCodeTrapHandler + 0x0105
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B10E96C10 __invoke_dynamic_linker__ + 0x0380
          (/lib64/libpthread.so.0)
  00002B2B11EBEC54 _ZN9sqlnq_pid5arityEv + 0x0000
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B12EFBDEA
  _Z39sqlnq_normalize_scalag_PB_and_OB_clauseP9sqlnq_pidS0_P3loc +
  0x0116
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  00002B2B12EFF140
  _Z22sqlnq_scalar_aggregatePP8stknode_i10actiontypePhP3loc +
  0x3022
          (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1)
  
  Only DB2 V9.5 is affected.
  

Problem conclusion

• First fixed in DB2 UDB Version V9.5 fixpak 2
  

Temporary fix

• When possible, rewrite the query to eliminate the duplicate
  column specification. For example,
  SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM
  MYTABLE
  
  becomes:
  
  SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2) FROM
  MYTABLE
  

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 UDB WSE LIN

• Fixed component ID

  5765F3504

Applicable component levels

• R950 PSN

     UP