IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    LI72795

  • Reported component name

    DB2 UDB WSE LIN

  • Reported component ID

    5765F3504

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-11-30

  • Closed date

    2008-05-07

  • Last modified date

    2008-05-07

  • 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

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
07 May 2008