PH32355: INCORRECT RESULT WITH NO ROWS RETURNED FOR QUERY FROM TABLE FUNCTION USING LITERAL

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This incorrect output problem happens in the following
  conditions:
  1. The query references a nested table UDF that is created with
  option NOT DETERMINISTIC or EXTERNAL ACTION.
  2. A literal constant is used as input parameter to the parent
  table UDF.
  3. In the definition of the parent table UDF, the input
  parameter mentioned in 2 is referenced in an expression that is
  passed to the child table UDF as input parameter.
  4. In the definition of the child table UDF, the input parameter
  mentioned in 3 is referenced in a predicate, where the query
  block that contains this predicate also contains another
  predicate that uses implicit cast.
  
  Keywords: DB2INCORR/K INCORROUT SQLINCORR SQLINCORROUT
  SQLTABLEUDF SQLTUDF
  

Local fix

• BYPASS/CIRCUMVENTION:
  Use NO EXTERNAL ACTION and DETERMINISTIC to create table UDFs.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of nested SQL                      *
  * table user defined functions                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrect output may occur for an SQL                        *
  * statement that satisfies the following                       *
  * conditions:                                                  *
  * 1. The query references a nested SQL                         *
  * table user defined function (UDF).                           *
  * 2. The parent and child SQL table UDFs                       *
  * are both created with option                                 *
  * NOT DETERMINISTIC or EXTERNAL ACTION.                        *
  * 3. When the parent SQL table UDF is                          *
  * invoked, a literal constant is used as                       *
  * one of its input parameters.                                 *
  * 4. In the body of the parent SQL table                       *
  * UDF, the input parameter mentioned                           *
  * in 3 is referenced in an expression                          *
  * that is passed to the child SQL table                        *
  * UDF as an input parameter.                                   *
  * 5. In the body of the child SQL table                        *
  * UDF, the input parameter mentioned                           *
  * in 4 is referenced in a predicate,                           *
  * where the query block that contains                          *
  * this predicate also contains another                         *
  * predicate that uses implicit cast.                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Here is an example to show the failing case.
  CREATE TABLE MYT1(C1 VARCHAR(4), C2 VARCHAR(4), C3 VARCHAR(4));
  CREATE TABLE MYT2(C1 VARCHAR(4), C2 INT);
  INSERT INTO MYT1 VALUES ('ABCD', 'abcd', '1234');
  INSERT INTO MYT2 VALUES ('ABCD',  1234 );
  CREATE FUNCTION MYTUDF1(P1 VARCHAR(4), P2 VARCHAR(4))
    RETURNS TABLE (X1 VARCHAR(4))
    LANGUAGE SQL
    READS SQL DATA EXTERNAL ACTION NOT DETERMINISTIC
    RETURN SELECT MYT1.C1 FROM MYT1 LEFT JOIN MYT2
               ON MYT1.C3 = MYT2.C2
            WHERE MYT1.C1 = P1 AND MYT1.C2 = P2;
  CREATE FUNCTION MYTUDF2(P1 VARCHAR(4))
    RETURNS TABLE (X1 VARCHAR(4))
    LANGUAGE SQL
    READS SQL DATA EXTERNAL ACTION NOT DETERMINISTIC
    RETURN SELECT * FROM TABLE(MYTUDF1(P1, LCASE(P1)));
  SELECT * FROM TABLE(MYTUDF2('ABCD'));
  When processing the above SELECT statement, Db2 did not handle
  MYTUDF1's input parameter LCASE(P1) correctly. As a result,
  the query returns 0 row while 1 row is expected.
  

Problem conclusion

• Db2 code has been modified to handle the aforementioned
  statement correctly.
  Additional keywords: SQLTABLEUDF SQLTUDF SQLINCORR INCORROUT
                       SQLINCORROUT DB2INCORR/K
  

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:

  UI73972

Modules/Macros

• DSNXOB2  DSNXOW2F DSNXOW2C DSNXOV1  DSNXOW2D
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI73972

     UP21/02/24 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.