PI75077: WHEN MIN SCALAR FUNCTION INVOLVED EMPTY STRING, THE SUBSEQUENT ROW RESULT MIGHT BE WRONG.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• create table tmp (
  pk int,
  d_vchr VARCHAR(254)    FOR MIXED DATA NOT NULL,
  n_vchr VARCHAR(254)    FOR SBCS DATA
  ) CCSID UNICODE;
  insert into tmp values (1, '','');
  insert into tmp values (2, 'a','a');
  
  SELECT MIN('32', D_VCHR) FROM TMP;
  returns 2 empty strings incorrectly.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of queries with MIN/MAX scalar     *
  * function on varying string column with empty string data.    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An incorrect result (wrong data returned) can be returned    *
  * from a query that contains MIN/MAX scalar function on        *
  * varying string column with empty string data. The subsequent *
  * row result could be wrong.                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result (wrong data returned) can occur from a query
  that contains MIN/MAX scalar function on varying string column
  with empty string data. The subsequent row result could be
  wrong.
  
  The following example helps to illustrate the failure.
  CREATE TABLE TMP (
  
                    PK INT,
  
                    D_VCHR VARCHAR(254) FOR MIXED DATA NOT NULL,
  
                    N_VCHR VARCHAR(254)    FOR SBCS DATA
  
                   ) CCSID UNICODE;
  
  COMMIT;
  
  INSERT INTO TMP VALUES (1, '','');
  
  INSERT INTO TMP VALUES (2, 'A','A');
  SELECT MIN('32', D_VCHR) FROM TMP;
  
  The result of this query is such that two empty strings are
  returned incorrectly.
  
       +-------------------+
        |                              |
       +-------------------+
    1_|                               |
    2_|                               |
       +-------------------+
  However, the expected result to be returned is empty string and
  '32'.
  
       +-------------------+
        |                              |
       +-------------------+
    1_|                               |
    2_| 32                          |
       +-------------------+
   2_|32                 |
     +-------------------+
  

Problem conclusion

• The code in DB2 which processes MIN/MAX scalar function on
  varying string column is modified.    This will allow a correct
  result to be returned.    Similar code changes are also made for
  fixed length string column.     Please note that PI75870  is the
  retrofit APAR for DB2 11 code changes.
  
  Additional Keywords: SQLMIN  SQLMAX SQLINCORR SQLINCORROUT
  INCORROUT
  

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:

  UI45313

Modules/Macros

• DSNXRGBJ DSNXRCSF DSNXRSFJ DSNXRSOR DSNXRRSQ DSNXRWND DSNXRSFN
  DSNXRSGB DSNXRSJ
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI45313

     UP17/03/21 P F703

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.