LI73924: 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.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  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

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

Problem conclusion

• The fix was provided in SB
  

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:

  LI73927

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSY

     UP