PH34045: INCORRECT SORT ORDER IN OUTPUT OF SELECT STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The problem may occur when a query satisfies following
  conditions:
  - a query has a OLAP and ORDER BY;
  - the col referenced in OLAP is a subset of ORDER BY cols;
  - the col referenced in OLAP is involved in a join predicate or
  a IN subquery pred.
  
  KEYWORDS:DB2INCORR/K INCORROUT SQLINCORR SQLINCORROUT SQLORDERBY
  SQLOLAP SQLIN
  

Local fix

• BYPASS/CIRCUMVENTION:
  The query reconstruction to explicitly or implicitly cast
  wrongly ordered column to INT in ORDER BY clause.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 for z/OS who have a                      *
  * query with an Online analytical processing                   *
  * (OLAP) specification and an ORDER BY                         *
  * clause.                                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An incorrect output problem may occur                        *
  * when a query satisfies the following                         *
  * conditions:                                                  *
  * 1. A query has an OLAP and an ORDER BY                       *
  *    clause;                                                   *
  * 2. The column referenced in the OLAP                         *
  *    is a subset of ORDER BY columns;                          *
  * 3. The column referenced in the OLAP                         *
  *    is covered by a join predicate or                         *
  *    involved in a IN subquery.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  If a query contains an Online analytical processing(OLAP)
  specification and an ORDER BY clause, and the column referenced
  in the OLAP is a subset of ORDER BY columns, an incorrect
  output problem may occur if the column referenced in the OLAP
  is covered by a join predicate or involved in a IN subquery.
  Example:
  SELECT T1.C1, T1.C2, T1.C3,
  COUNT(*) OVER(PARTITION BY T1.C1) AS ROWS_TO_FETCH
  FROM T1
  WHERE T1.C2<?
  AND T1.C1 IN (SELECT T2.C1 FROM T2
                WHERE T1.C2>?)
  ORDER BY T1.C1, T1.C2, T1.C3
  ;
  This query has an OLAP on column T1.C1, which is a subset of
  ORDER BY columns and involved in a IN subquery. The result set
  of the non-correlated subquery may be materialized and stored
  in DSNWFQB(02), and a join predicate may be generated as T1.C1=
  DSNWFQB(02).C1. For this query, Db2 may miss a sort plan for
  the ORDER BY, which results in an incorrect output problem.
  Additional keywords:
  SQLACCESSPATH SQLINCORR INCORROUT SQLINCORROUT DB2INCORR/K
  SQLOLAP SQLORDERBY
  

Problem conclusion

• Code is updated to get correct output for the above query.
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI74443

Modules/Macros

• DSNXOTS1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI74443

     UP21/03/23 P F103 ¢

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.