IT26298: SQL SCALAR FUNCTION DECLARED WITH BEGIN ATOMIC AND CONSISTING OFA SINGLE RETURN STATEMENT MAY PRODUCE WRONG RESULTS

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

APAR status

• Closed as program error.

Error description

• When a SQL scalar function is defined with BEGIN ATOMIC -  i.e.
  Compound SQL (inlined)- and the function body contains only a
  single RETURN statement, a query that references this function
  may return a wrong result.  For example, the SELECT statement
  below should return zero rows:
  
     CREATE TABLE T ( ID int, NAME VARCHAR(10 ) );
     insert into T (ID, NAME) values (1,'FRED'), (2,'IRENE');
  
     CREATE FUNCTION F ()
     RETURNS INTEGER
     NO EXTERNAL ACTION
     BEGIN ATOMIC
        RETURN 1; --
     END;
  
     SELECT * FROM T
     WHERE F() = (SELECT ID FROM T WHERE NAME = 'IRENE');
  
     ID          NAME
     ----------- ----------
               1 FRED
               2 IRENE
  
       2 record(s) selected.
  

Local fix

• Recreate the SQL function without the BEGIN ATOMIC, e.g.:
  
    CREATE OR REPLACE FUNCTION F ()
    RETURNS INTEGER
    NO EXTERNAL ACTION
       RETURN 1;
  
  Alternatively, recreate the function without the ATOMIC keyword
  -  i.e. Compound SQL (compiled):
  
     CREATE FUNCTION F ()
     RETURNS INTEGER
     NO EXTERNAL ACTION
     BEGIN
        RETURN 1; --
     END;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description and install this fix.                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * See Error Description and install this fix.                  *
  ****************************************************************
  

Problem conclusion

• See Error Description and install this fix.
  

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:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSY

     UP