DB2 FOR LUW / DB2FORLUW / B10 - DB2

Codegen precomputes predicates in scan lolepops if we deem them 
invariant for the duration of the scan. This is causing problems 
with CASE statements and we may end up performing the THEN 
before the WHEN. 
 
        CASE 
        WHEN TRANSLATE (vc_in, '', '1234567890') THEN CAST 
(vc_in AS INTEGER) 
        ELSE NULL 
 
Above is causing a SQL0420N casting error when the value of 
vc_in was 'T001', we would expect the WHEN to avoid doing the 
cast and avoiding the error. 
 
 
Here is reproduction testcase: 
 
CREATE OR REPLACE FUNCTION METADATA.SAFE_INT (vc_in VARCHAR 
(500)) 
RETURNS INTEGER 
NO EXTERNAL ACTION 
RETURN 
        CASE 
        WHEN TRANSLATE (vc_in, '', '1234567890') = '' THEN CAST 
(vc_in AS INTEGER) 
        ELSE NULL 
END 
@ 
 
CREATE OR REPLACE FUNCTION METADATA.TEST_FUNC1( vc_in 
VARCHAR(4)) 
RETURNS VARCHAR(20) 
   BEGIN ATOMIC 
   DECLARE v_list_status_msg VARCHAR(20) DEFAULT 'START'; 
 
    IF METADATA.SAFE_INT(vc_in) IS NULL THEN 
        SET v_list_status_msg = 'Test Worked'; 
    END IF; 
 
    RETURN v_list_status_msg; 
END 
@ 
 
drop TABLE t1@ 
 
CREATE TABLE t1 
( 
    c1                   VARCHAR(4) NOT NULL 
)@ 
 
INSERT INTO t1 VALUES('T001')@ 
 
SELECT METADATA.TEST_FUNC1(c1) FROM t1@

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to v11.1.3.3 or above                                 * 
****************************************************************

db2set DB2_TCG_DEFAULT_OPTIONS="set precompute_expr off" 
It needs an instance restart to take effect and any static SQL 
needs to 
be rebound to benefit from the workaround.

Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in v11.1.3.3

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

13.09.2017
12.10.2017
12.10.2017