PM84232: SQLPL COMPARISONS OF CHAR AND VARCHAR TO DATE AND TIMESTAMP DO NOT PRODUCE PROPER RESULTS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Customer wrote a Native SQL procedure with the following compare
  IF EXPDTE<=CURRENT_DATE THEN
     SET MSGTXT=MSGTXT||CHAR(EXPDTE)||' IS <= CURRENT_DATE '||
     CHAR(CURRENT_DATE);
  ELSE
     SET MSGTXT=MSGTXT||CHAR(EXPDTE)||' IS > CURRENT_DATE '||
     CHAR(CURRENT_DATE);
  END IF;
  When EXPDTE is equal to CURRENT DATE, the ELSE leg is taken.
  

Local fix

• External SQL procedures do not exhibit this issue.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS users of       *
  *                 native SQL procedure and non-inline SQL      *
  *                 scalar function.                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: INCORRECT OUTPUT may occur when an      *
  *                      SQL variable or SQL parameter of the    *
  *                      character string data type is           *
  *                      referenced in the datetime comparison   *
  *                      inside a native SQL procedure or        *
  *                      non-inline SQL scalar function.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  INCORRECT OUTPUT may occur when an SQL variable or
  SQL parameter of character string data type is referenced
  in datetime comparison in basic predicate, BETWEEN predicate,
  IN predicate, or search-condition in any SQL control statements
  such as IF statement inside a native SQL procedure or
  non-inline SQL scalar function.  Depending on how predicate is
  written in the query, it could return wrong rows, too few rows,
  or too many rows.
  
  Here are detailed conditions for each type of predicate:
  
  1. Basic predicate, BETWEEN predicate,
     search-condition in SQL control statements
  
     SQL variable or SQL parameter of character string data type
     is compared with the expression of datetime data type and the
     expression is not a column, SQL variable or SQL parameter.
  
  2. IN predicate
  
     SQL variable or SQL parameter of character string data type
     is compared with a datetime column in the select list of a
     fullselect as right hand side of the predicate.
  
  
  EXAMPLE
  =======
  CREATE TABLE T1 (TSCOL TIMESTAMP, DATECOL DATE)!
  
  INSERT INTO T1 VALUES ('2009-10-10-01.02.02.123456',
                         '2011-10-10')!
  
  CREATE PROCEDURE MYPROC()
  LANGUAGE SQL
  BEGIN
  
    DECLARE V1 VARCHAR(10);
    DECLARE V2 INTEGER;
    DECLARE V3 VARCHAR(10);
  
    SET V1 = CURRENT_DATE;
  
    SELECT 1 INTO V2
    FROM SYSIBM.SYSDUMMY1
    WHERE V1 = CURRENT_DATE;
  
    SET V1 = '2010-10-10';
    SET V3 = '2011-10-10';
  
    SELECT 1 INTO V2
    FROM T1
    WHERE V1 BETWEEN DATE(TSCOL) AND V3;
  
    SET V1 = '2011-10-10';
  
    SELECT 1 INTO V2
    FROM SYSIBM.SYSDUMMY1
    WHERE V1 IN (SELECT DATECOL FROM T1);
  
  END!
  

Problem conclusion

• DB2 is updated to evaluate datetime comparison correctly in
  SQL native procedure and non-inline SQL scalar function.
  
  Additional Keywords: SQLNATIVESQLPL INCORROUT SQLINCORR
                       DB2INCORR/K SQLINCORROUT
                       SQLIN SQLBETWEEN SQLEQUAL SQLLT SQLGT
                       SQLDATE SQLTIME SQLTIMESTAMP
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK93882 UK93883

Modules/Macros

• DSNXODTV
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK93882

     UP13/05/05 P F305

• R910 PSY UK93883

     UP13/05/05 P F305

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.