PM54599: INCORROUT NO ROW FOUND WITH PREDICATE WHICH CONTAINS SCALAR FULL SELECT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT NO ROW FOUND WITH SUBQUERY IN BETWEEN PREDICATE.
  
  DB2INCORR/K
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 for z/OS who have a query that  *
  *                 has a scalar full select predicate, or has   *
  *                 a prediate which has a CASE expression       *
  *                 that contains a subquery. There is also an   *
  *                 index created on the column expression       *
  *                 that matches the predicate.                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: If a predicate contains scalar full     *
  *                      select or an CASE expression that has   *
  *                      a subquery, then it is not indexable.   *
  *                      But if there is an index created        *
  *                      on the column expression, the optimizer *
  *                      may select the predicate as matching    *
  *                      or screening by mistake, which may      *
  *                      result in incorrect output.             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  If a predicate contains scalar full select, or contains a
  CASE expression which has a subquery, then it is not indexable.
  But if there is an index created on the column expression,
  the optimizer may select the predicate as matching or
  screening by mistake, which may result in incorrect output.
  
  Example 1:
  SELECT * FROM T1
  WHERE YEAR(C1) BETWEEN 1992 AND (SELECT MAX(YEAR) FROM T2)
  
  The BETWEEN predicate contains a scalar full select,
  and there is also an index created on column expression
  YEAR(C1). The optimizer may select this index and use the
  BETWEEN predicate as a matching predicate, which may get
  incorrect output.
  
  Example 2:
  SELECT * FROM T1
  WHERE YEAR(C1) IN
  (
    CASE WHEN EXISTS(SELECT 1 FROM T2)
    THEN 2011
    ELSE 2012
    END
  );
  
  For the above query, the IN predicate contains a CASE
  expression, and there is a subquery in the CASE expression.
  There is also an index created on column expression
  YEAR(C1). The optimizer may select this index and use the
  IN predicate as a matching predicate, which may get
  incorrect output.
  
  ADDITIONAL KEYWORDS:
  SQLINCORR INCORROUT SQLINCORROUT DB2INCORR/K SQLSUBQUERY
  SQLSCALAR SQLFULLSELECT SQLSCALARFULSEL SQLCASE
  

Problem conclusion

• Code is changed to fix the problem above.
  

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:

  UK82538 UK82539

Modules/Macros

• DSNXOEXC DSNXOGBM DSNXOW2F DSNXOW2G
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK82538

     UP12/10/28 P F210

• R910 PSY UK82539

     UP12/10/28 P F210

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.