IBM Support

LI73927: DB2 INSTANCE MAY ABEND EXECUTING A QUERY CONTAINING A PREDICATE ON MAX/MIN APPLIED ON A CASE EXPRESSION WITH 1 OR MORE GROUP BY

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A query containing a predicate on the output of a MAX or MIN
    function where the
    subselect containing the MAX or MIN function has more 1 or more
    GROUP BY and
    output columns and the input to the MAX or MIN function is a
    CASE expression,
    may abend the DB2 instance.
    
    An example scenario for such an abend to occur follows:
    
    -- DDL
    CREATE TABLE T (C1 INT, C2 CHAR, C3 INT, C4 INT, C5 INT, C6
    INT);
    
    -- Query that may abend the instance
    SELECT C1,
           C2,
           C3,
           C4,
           C5,
           C6
    FROM   (SELECT C1 AS C1,
                   MAX(CASE WHEN C2 = '1'
                            THEN 1
                            ELSE 0
                       END
                      ) AS C2,
                   C3 AS C3,
                   C4 AS C4,
                   C5 AS C5,
                   C6 AS C6
            FROM   T
            GROUP BY C1, C3, C4, C5, C6
           ) AS Q
    WHERE  C2 = 1;
    
    The stack trace from the abend may look something like:
    
    ossDumpStackTrace
    OSSTrapFile4dump
    sqlo_trce
    sqloEDUCodeTrapHandler
    __restore_rt
    sqlnq_pidnwEmP13sqlnq_memPoolRi
    address: 0xnnnnnnnnnnnnnnnn
    sqlnr_gb_ppdto_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_any_ppdfr
    sqlnr_sel_ppdfr_fqun_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_qun_local_ppd
    sqlnr_local_magic_detail
    sqlnr_localmagic_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_magic_detail_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_endqtb_action
    ...
    
    This problem was first introduced in DB2 Version 9.1 Fix Pack 5
    and DB2 Version
    9.5 Fix Pack 2. It will be fixed starting with DB2 Version 9.1
    Fix Pack 7 and
    DB2 Version 9.5 Fix Pack 4.
    

Local fix

  • No feasible workaround exists.
    

Problem summary

  • DB2 INSTANCE MAY ABEND EXECUTING A QUERY CONTAINING A PREDICATE
    ON MAX/MIN APPLIED ON A CASE EXPRESSION WITH 1 OR MORE GROUP BY
    

Problem conclusion

  • A query containing a predicate on the output of a MAX or MIN
    function where the
    subselect containing the MAX or MIN function has more 1 or more
    GROUP BY and
    output columns and the input to the MAX or MIN function is a
    CASE expression,
    may abend the DB2 instance.
    
    An example scenario for such an abend to occur follows:
    
    -- DDL
    CREATE TABLE T (C1 INT, C2 CHAR, C3 INT, C4 INT, C5 INT, C6
    INT);
    
    -- Query that may abend the instance
    SELECT C1,
           C2,
           C3,
           C4,
           C5,
           C6
    FROM   (SELECT C1 AS C1,
                   MAX(CASE WHEN C2 = '1'
                            THEN 1
                            ELSE 0
                       END
                      ) AS C2,
                   C3 AS C3,
                   C4 AS C4,
                   C5 AS C5,
                   C6 AS C6
            FROM   T
            GROUP BY C1, C3, C4, C5, C6
           ) AS Q
    WHERE  C2 = 1;
    
    The stack trace from the abend may look something like:
    
    ossDumpStackTrace
    OSSTrapFile4dump
    sqlo_trce
    sqloEDUCodeTrapHandler
    __restore_rt
    sqlnq_pidnwEmP13sqlnq_memPoolRi
    address: 0xnnnnnnnnnnnnnnnn
    sqlnr_gb_ppdto_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_any_ppdfr
    sqlnr_sel_ppdfr_fqun_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_qun_local_ppd
    sqlnr_local_magic_detail
    sqlnr_localmagic_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_magic_detail_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_endqtb_action
    ...
    
    This problem was first introduced in DB2 Version 9.1 Fix Pack 5
    and DB2 Version
    9.5 Fix Pack 2. It will be fixed starting with DB2 Version 9.1
    Fix Pack 7 and
    DB2 Version 9.5 Fix Pack 4.
    

Temporary fix

  • No feasible workaround exists.
    

Comments

APAR Information

  • APAR number

    LI73927

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-12-01

  • Closed date

    2009-05-28

  • Last modified date

    2009-05-28

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

    LI73924

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

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       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:
28 May 2009