IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    IT26298

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-09-14

  • Closed date

    2019-11-29

  • Last modified date

    2019-11-29

  • 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

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2022