IBM Support

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

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

  • 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":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 November 2019