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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM84232

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-03-06

  • Closed date

    2013-04-26

  • Last modified date

    2013-06-04

  • 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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM84232

Modified date:

2013-06-04

Translate my page

Machine Translation

Content navigation