PM89329: INCORROUT NO ROWS RETURNED ON QUERY WITH A TABLE UDF

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

APAR status

  • Closed as program error.

Error description

  • INCORROUT NO ROWS RETURNED ON QUERY WITH A TABLE UDF
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of SQL             *
    *                 table functions.                             *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect out may occur for an SQL      *
    *                      statement that references an            *
    *                      SQL table function. Incorrect out may   *
    *                      occur for the following cases:          *
    *                      1. The SQL statement references an SQL  *
    *                      table function and a view.              *
    *                      2. The SQL statement references a       *
    *                      multi-level nested SQL table function   *
    *                      and the argument of the SQL table       *
    *                      function contains a column expression.  *
    *                      3. The SQL statement references an      *
    *                      SQL table function in the subquery      *
    *                      predicate and the SQL table function    *
    *                      is materialized.                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Incorrect output may occur for an SQL statement that references
    an SQL table function. Incorrect out put may occur for the
    following cases:
    1. The SQL statement references an SQL table function and a
    view.
    
     For Example,
    
        CREATE FUNCTION TUDF1(P1 INT)
          RETURNS TABLE(C1 INT)
          RETURN
            SELECT C1 FROM TEST1 WHERE C1 = P1;
    
        CREATE VIEW V1 (VC1) AS SELECT 1 FROM SYSIBM.SYSDUMMY1;
    
        SELECT * FROM TABLE (TUDF1(1))TX, V1;
    
    The SELECT statement above may receive incorrect output because
    DB2 did not process the argument of the SQL table function
    correctly.
    
    2. The SQL statement references a multi-lvel nested SQL table
    function and the argument of the SQL table function contains a
    column expression.
    
      For Example,
      CREATE FUNCTION UDTF121(P1 INT, P2 INT)
          RETURNS TABLE(C1 INT, C2 INT)
          RETURN
          SELECT P1, P2
            FROM SYSIBM.SYSDUMMY1
          UNION ALL
          SELECT P1, P2
            FROM SYSIBM.SYSDUMMY1;
    
      CREATE FUNCTION UDTF122(P1 INT, P2 INT)
          RETURNS TABLE(C1 INT, C2 INT)
          RETURN
          SELECT CX, CY FROM TABLE (UDTF121 (P1,P2))TX (CX,CY);
    
    
      CREATE FUNCTION UDTF123(P1 INT, P2 INT)
          RETURNS TABLE(C1 INT, C2 INT)
          RETURN
            SELECT TY.CX, TY.CY FROM
            TABLE (UDTF121 (P1,P2))TX (C1,C2),
            TABLE (UDTF122 (TX.C1,TX.C2))TY (CX,CY);
    
      SELECT * FROM TABLE (UDTF123(1,1))TX;
    
    The SELECT statement above may receive incorrect output because
    did not process the column expression TX.C1, TX.C2 in the
    argument list of SQL table function UDTF122 correctly.
    
    3. The SQL statement references an SQL table function in the
    subquery predicate and the SQL table function is materialized.
    
     For Example,
    
     CREATE TABLE T1 (C1 INT);
     CREATE FUNCTION TUDFA(P1 INT)
       RETURNS TABLE(C1 INT)
       RETURN
         SELECT CY FROM (SELECT RAND(), P1 FROM T2)TX (CX,CY);
    
     SELECT * FROM T1 WHERE C1 IN (
       SELECT * FROM TABLE (TUDFA(123))TY);
    
    
    The SELECT statement above may receive incorrect output
    because DB2 did not process the SQL table function in the
    subquery correctly.
    

Problem conclusion

  • DB2 was fixed to handle the SQL table function correctly.
    
    Additonal KeyWords:  SQLINLINE SQLTABLEUDF
                         SQLINCORR INCORROUT SQLINCORROUT
                         DB2INCORR/K SQLCODE180 SQLUDF
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM89329

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-05-20

  • Closed date

    2013-08-08

  • Last modified date

    2013-09-11

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

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

    UK96508

Modules/Macros

  • DSNXOB1  DSNXOEXC DSNXOJAJ DSNXOOS2 DSNXOVC  DSNXOVM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM89329

Modified date:

2013-09-11

Translate my page

Machine Translation

Content navigation