PM61434: INCORROUT OCCURS FOR A QUERY CONTAINING A SUBSELECT WITH AN XMLTABLE UNDER AN EXISTS PREDICATE.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• An incorrect result OCCURS for a QUERY CONTAINING a
  SUBSELECT WITH an XMLTABLE UNDER an EXISTS PREDICATE.
  A RETROFIT of the fix for defect 152604 is needed.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS users of       *
  *                 EXISTS predicates and the XMLTABLE function. *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 returns an incorrect result for a   *
  *                      query that contains a SELECT statement  *
  *                      with an EXISTS predicate and uses the   *
  *                      XMLTABLE function in a subquery.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 returns an incorrect result for a query that contains a
  SELECT statement with an EXISTS predicate and uses the XMLTABLE
  function in a subquery.
  
  
  The following example can illustrate the problem.
  
  1. Create two tables and populate them.
  
     CREATE TABLE T1 (C1 INTEGER, X1 XML);
  
     INSERT INTO T1 (C1,X1)
      VALUES (20,'<a><c>21</c></a>');
  
     INSERT INTO T1 (C1,X1)
      VALUES (20,'<a><c>20</c></a>');
  
  
     CREATE TABLE T2 (C1 INTEGER, C2 INTEGER);
  
     INSERT INTO T2 (C1, C2)
      VALUES (20, 20);
  
     INSERT INTO T2 (C1, C2)
      VALUES (20, 21);
  
  
  2. Issue a SELECT statement that uses an EXISTS predicate
     and the XMLTABLE function is in the subquery.
  
     SELECT C1,C2 FROM T2
     WHERE EXISTS
           (SELECT * FROM T1,
                          XMLTABLE('/a'
                           PASSING X1
                           COLUMNS
                           "C2"  INTEGER path 'c' ) AS X
            WHERE T2.C1 = T1.C1 AND
                  T2.C2 = X.C2)
           OR C2 = 100;
  
  
  
     The query returns this result.
  
        +---------------------------------+
        |       C1       |       C2       |
        +---------------------------------+
      1_|             20 |             20 |
        +---------------------------------+
  
     However, the following result is expected.
  
        +---------------------------------+
        |       C1       |       C2       |
        +---------------------------------+
      1_|             20 |             20 |
      2_|             20 |             21 |
        +---------------------------------+
  
  DB2 code does not handle the XMLTABLE function for an EXISTS
  predicate properly.  This leads to an incorrect result when a
  subsequent row is fetched to qualify.
  

Problem conclusion

• The code in DB2 has been modified to not return an incorrect
  result in the scenario described.
  
  Additional Keywords: SQLXMLTABLE SQLEXISTS SQLINCORR INCORROUT
                       SQLINCORROUT 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:

  UK78575 UK78576

Modules/Macros

• DSNNQMD  DSNNQTOP DSNXRBND DSNXRBN9 DSNXRDOC
  DSNXRDO9 DSNXRPUF DSNXRXML DSNXRXM9
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK78575

     UP12/05/23 P F205

• R910 PSY UK78576

     UP12/05/23 P F205

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.