DB2 FOR LUW / DB2FORLUW / B10 - DB2

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.

****************************************************************
* USERS AFFECTED:                                              *
* All                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description and install this fix.                  *
****************************************************************
* RECOMMENDATION:                                              *
* See Error Description and install this 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;

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

14.09.2018
29.11.2019
29.11.2019