JR29427: IMPROVEMENT TO COALESCE AND VALUE SCALAR FUNCTION PROCESSING

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

• One usage of COALESCE (or VALUE) function is to replace NULL
  value with a meaningful value to the users
  in the result set. When the first argument of COALESCE is known
  to not producing any NULL value, the arguments
  following this argument could be ignore. Subsequently, DB2 can
  rewrite the expression of the COALESCE function
  to the first argument.
  
  When an ORDER BY clause consists of expressions, DB2 processes
  the ORDER BY clause after the expressions are evaluated
  by sorting the expressions in the SELECT clause and the
  expressions of the ORDER BY clause that are not in the SELECT
  clause.
  When the width of the summation of the aforementioned
  expressions and/or the number of expressions exceeds the maximum
  length of a row and/or maximum number of columns given in the
  DB2 documentation titled "SQL and XQuery limits," DB2 returns
  a SQL1585N error.
  

Local fix

• The rewrite of COALESCE (or VALUE) function to its first
  argument when DB2 can deduce that the first argument could not
  return a NULL value could benefit the query processing in many
  ways. One of the benefit, when rewriting COALESCE
  function in an ORDER BY clause, is to avoid SQL1585N error. This
  is because DB2 has a choice to group columns in an ORDER BY
  clause to small sets of columns from the same tables and then
  process the ORDER BY for each smaller set of columns at the
  processing of individual tables.
  

Problem summary

• Users affected:
  All Users on V95 FP1 and FP2
  
  Problem Description:
  One usage of COALESCE (or VALUE) function is to replace NULL
  value with a meaningful value to the users
  in the result set. When the first argument of COALESCE is known
  to not producing any NULL value, the arguments
  following this argument could be ignore. Subsequently, DB2 can
  rewrite the expression of the COALESCE function
  to the first argument.
  
  When an ORDER BY clause consists of expressions, DB2 processes
  the ORDER BY clause after the expressions are evaluated
  by sorting the expressions in the SELECT clause and the
  expressions of the ORDER BY clause that are not in the SELECT
  clause.
  When the width of the summation of the aforementioned
  expressions and/or the number of expressions exceeds the maximum
  length of a row and/or maximum number of columns given in the
  DB2 documentation titled "SQL and XQuery limits," DB2 returns
  a SQL1585N error.
  
  LOCAL FIX:
  The rewrite of COALESCE (or VALUE) function to its first
  argument when DB2 can deduce that the first argument could not
  return a NULL value could benefit the query processing in many
  ways. One of the benefit, when rewriting COALESCE
  function in an ORDER BY clause, is to avoid SQL1585N error. This
  is because DB2 has a choice to group columns in an ORDER BY
  clause to small sets of columns from the same tables and then
  process the ORDER BY for each smaller set of columns at the
  processing of individual tables.
  

Problem conclusion

• First fixed in DB2 UDB Version V95, FixPak 3
  

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:

Fix information

• Fixed component name

  DB2 UDB WSE WIN

• Fixed component ID

  5765F3501

Applicable component levels

• R950 PSY

     UP