IT32967: QUERY COMPILATION FAILS WITH SQLCODE -421 WHEN OLAP SPECIFICATION USED AS ARGUMENT TO INLINED SQL FUNCTION

Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• If a subselect is used in a set operation, the select-list of
  the subselect includes an inlined SQL function reference, and an
  argument to the SQL function is an OLAP specification, then
  query compilation may fail with SQLCODE -421.  For example:
  
     CREATE OR REPLACE FUNCTION MYFUNCTION (i INT)
       RETURNS INT
       LANGUAGE SQL
     RETURN i;
  
     select * from t1
       union all
     select myfunction(row_number() over ())) from t2;
     SQL0421N  The operands of a set operator or a VALUES clause
  do not have the
     same number of columns.  SQLSTATE=42826
  

Local fix

• There are three workarounds:
  
  1. Change the order of the UNION/EXCEPT/INTERSECT oprands
  2. Change the inlined SQL UDF to compiled by specifying BEGIN
  without ATOMIC, e.g.:
  
    CREATE OR REPLACE FUNCTION MYFUNCTION (i INT)
       RETURNS INT
       LANGUAGE SQL
    BEGIN
      RETURN i; --
    END;
  
  3. Change the subquery to a fullselect by surrounding it with
  parentheses, e.g.:
  
    select * from t1
      union all
    ( select myfunction(row_number() over ())) from t2 );
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 version 11.1.4.7                              *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 version 11.1.4.7
  

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:

  IT38217

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP