PI61893: INCORROUT WITH UPDATE STATEMENT AND CORRELATED SUBQUERY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A INCORROUT conditions occurs when a UPDATE statement is coded
  with a correlated subquery that contains a DISTINCT and FETCH
  FIRST ROW only, and more than one row qualifies for the update.
  The access path is a non-matching index scan for sort avoidance.
  
  Additional Keywords:
  DB2INCORR/K
  SQLDISTINCT
  SQLUPDATE
  SQLFFNR
  SQLSUBQUERY
  SQLACCESSPATH
  

Local fix

• SQL rewrite to remove distinct or fetch first one row.
  
  An Example:
  UPDATE SESSION.TABLEA  A
  SET (A,B,C) = (
  -- SELECT DISTINCT B.A, B.B, B.C
  SELECT B.A, B.B, B.C
  FROM TABLEB B
  WHERE B.COL1 = A.COL1
   FETCH FIRST ROW ONLY
  )
  WHERE A.A IS NULL
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 v11 users of queries that contain    *
  *                 DISTINCT and the FETCH FIRST ROW ONLY clause *
  *                 and use index skipping.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result set (less rows than *
  *                      expected and/or unexpected row          *
  *                      contents) can be returned for a query   *
  *                      that contains a DISTINCT, a FETCH FIRST *
  *                      ROW ONLY clause, or both, if index      *
  *                      skipping is chosen for data access.     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result set (less rows than expected and/or
  unexpected contents) can be returned for a query that contains a
  DISTINCT, the FETCH FIRST ROW ONLY clause, or both, if index
  skipping is chosen for data access.
  
  The following example demonstrates a failing case.
  
  The inner table which is referenced in the subquery is defined
  as the following.
  
    CREATE TABLE T2 (
          "APPLICATION" CHAR(20) FOR SBCS DATA NOT NULL
         ,"REGION" CHAR(20) FOR SBCS DATA NOT NULL
         ,"TYPE" CHAR(12) FOR SBCS DATA NOT NULL
         ,"COMPLEX" CHAR(8) FOR SBCS DATA NOT NULL
         ,"DSN_SCOPE" CHAR(26) FOR SBCS DATA NOT NULL
         ,"GOVERNED" CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
         ,"BATCH_OWNER" CHAR(8) FOR SBCS DATA WITH DEFAULT NULL
         )
         IN ....
             CCSID    EBCDIC ;
  
  The index that is chosen for accessing the data for this table
  is defined as follows.
  
    CREATE  INDEX T2_IX3"
      ON  T2  (
       "APPLICATION" ASC
       ,"REGION" ASC
       ,"TYPE" ASC
       ,"COMPLEX" ASC
       ,"DSN_SCOPE" ASC) ... ;
  
  Please note that the first three leading columns of the index
  are included in the DISTINCT statement. Index skipping is set
  up during access path selection to help improve performance
  for the sorting process for the DISTINCT.
  
  The failing query which contains both a DISTINCT and a
  FETCH FIRST ROW ONLY clause looks like this.
  
         UPDATE SESSION.$ADM_PROFILE A
         SET (APPLICATION,REGION,TYPE) = (
             SELECT DISTINCT B.APPLICATION, B.REGION, B.TYPE
             FROM T2 B
             WHERE B.COMPLEX = A.COMPLEX
             AND COALESCE(B.BATCH_OWNER,B.DSN_SCOPE) = A.SCOPE
             FETCH FIRST ROW ONLY
             )
         WHERE A.APPLICATION IS NULL ;
  
  In the reported case, 2 rows are expected but only 1 row is
  returned. The contents of the rows returned can also be
  incorrect or unexpected.
  
  It should be noted that it is not necessary to have both a
  DISTINCT and FETCH FIRST ONE ROW clause in a given query to
  receive an incorrect result.  An incorrect result can be
  returned if there is a DISTINCT, FETCH FIRST ONE ROW clause, or
  both.
  
  Furthermore, it should be noted that a similar problem, 'less
  rows than expected', can be returned when using the FETCH FIRST
  N ROWS clause and index skipping. An example of such a failing
  query is as follows.
  
  Step 1. Create Table T1 with an index IX1 and insert 4 rows.
  
    CREATE TABLE T1 (C1 INT, C2 INT, C3 INT);
  
    CREATE INDEX IX1 ON T1 (C1);
  
    INSERT INTO T1 VALUES(1,2,3);
    INSERT INTO T1 VALUES(1,1,2);
    INSERT INTO T1 VALUES(2,2,3);
    INSERT INTO T1 VALUES(2,1,2);
  
  
  Step 2. Perform the following query.
  
    SELECT * FROM T1 A, TABLE (
    SELECT DISTINCT C1 FROM T1 B
    WHERE A.C3 > B.C2
    ORDER BY 1
    FETCH FIRST 2 ROWS ONLY
    ) TX (C1),
    T1 C
    WHERE TX.C1 = C.C1
    QUERYNO 2 ;
  
  Step 3.  Check the results.  The expected result is that 16 rows
  are returned.  However, only 12 rows are returned.  So, 'less
  rows than expected' are returned.
  

Problem conclusion

• The code is modified in DB2 to not choose index skipping for
  certain queries that contain a DISTINCT, a FETCH FIRST ONE ROW
  clause, or both.  This will prevent the reported incorrect
  result from being returned.
  
  Additionally, the 'less rows than expected' result being
  returned if using a FETCH FIRST N ROWS clause with index
  skipping is also corrected.
  
  Additional Keywords: SQLACCESSPATH SQLDISTINCT SQLFF1R SQLFFNR
                       SQLINCORR SQLINCORROUT INCORROUT
                       DB2INCORR/K
  

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:

  UI39452

Modules/Macros

• DSNXOPRP DSNXRCLS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI39452

     UP16/07/30 P F607 Ž

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.