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

DB2 Version 9.5 Fix Pack 4  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

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