PH32690: INCORROUT WHEN A SAME EXPRESSION ALIAS APPEARS IN THE SELECTION LIST MULTIPLE TIMES AND THIS QUERY RUNS ON PARALLELISM

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Description:
  The problem is when a same expression alias appears in the
  selection list multiple times and this query runs on
  parallelism, then a reset information for the multiple
  expression columns may be missed in the parallelism child task.
  Hence, cause the incorrect output.
  An example query looks like the following, the expression is a
  CASE expression:
  SELECT C1, C2, EXP1 AS CX, EXP1 AS CY, C3, ...
  FROM (SELECT C1, C2,
  CASE WHEN ... END AS EXP1,
  C3, ...
  FROM T1 ...
  WHERE ...
  ) AS TX
  WHERE ....
  
  The CASE expression is defined AS EXP1, and it appears in the
  selection list twice (AS CX and CY). It could be used inside any
  build in function (ie. SUBSTR(EXP1, 1, 3) AS CX) or select out
  directly.
  The incorrect out is the value of the expression EXP1 will
  always contain the first qualified value. The same value will be
  returned from the same child task. If multiple child tasks found
  qualified rows, then the different child task may return the
  different first qualified row value.
  For example, if child task 1 found the first qualified value of
  EXP1 is "ABC", then all the rows returned by child task 1 on the
  column EXP1 will be "ABC". If child task 2 found the first
  qualified value of EXP1 is "XYZ", then all the rows returned by
  child task 2 on the column EXP1 will be "XYZ".
  
  Keywords: SQLCASE DB2PARALL/K PARALLELISM SQLPARALLELISM
  

Local fix

• Disable the parallelism:
  SET CURRENT DEGREE = '1';
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 11 for z/OS and Db2 12 for z/OS                      *
  * users of parallelism.                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An incorrect output may occur when                           *
  * the following condition met:                                 *
  * 1. a query selection list contains an                        *
  * expression with an AS clause and this                        *
  * AS name is referenced multiple times                         *
  * in the selection list                                        *
  * 2. and the query runs on parallel.                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An incorrect output may occur when the following condition
  met:
  1. a query selection list contains an expression with an AS
  clause and this AS name is referenced multiple times in the
  selection list (Note: this AS name may be referenced directly,
  or this AS name may be referenced in a SETFUNCTION or SCALAR
  FUNCTION, etc.).
  2. and this query runs on parallel.
  When child task processing the expression, a re-set function
  is missing in the action. Hence, the child task only returns
  the first qualified value without re-set then re-evaluate the
  next row.
  The nature of the incorrect output is returning the same
  number of rows as correct output, but the selection list
  columns that referencing this AS name may contains incorrect
  value. Following is an example of such failing query:
  SELECT TX.CX, SUBSTR(TX.CX, 1, 5), TX.C1, ...
    FROM (SELECT C1, ...,
                 CASE WHEN ... ELSE ... THEN .. END  AS CX,
                 ...
            FROM T1, ...
           WHERE ...
          ) AS TX
  Note: TX.CX is referenced twice in the selectin list and those
  two columns may return incorrect value. The same child task
  will reurn the same value within that child task, but each
  child task could return different value. For example, assuming
  3 rows returned from child task 1 and the correct value is
  'A1', 'A2', and 'A3'; 2 rows returned from child task 2 and the
  correct value is 'B1' and 'B2'. Then the incorrect output for
  the CX column could be: 'A1', 'A1', 'A1' (from child task 1)
  and 'B1', 'B1' (from child task 2).
  

Problem conclusion

• Db2 has been modified to add the missing re-set function in
  the child task, so it will process the re-set and then
  re-evaluate the expression for each row.
  Additional Keywords:
  DB2PARALL/K
  PARALLELISM
  SQLPARALLELISM
  DB2INCORR/K
  INCORROUT
  SQLINCORR
  SQLINCORROUT
  SQLCASE
  

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:

  UI74052 UI74060

Modules/Macros

• DSNXGTSK
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI74052

     UP21/02/27 P F102 ¢

• RC10 PSY UI74060

     UP21/02/27 P F102 ¢

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.