DB2 FOR LUW / DB2FORLUW / 970 - DB2

A select query is expected to return 0 rows when its predicate 
is evaluated as false at runtime, however it may return some 
rows when all following conditions are satisfied 
1. Query has scalar UDF (User Defined Function) in its select 
clause 
2. Query has such an equality predicate whose operand is 
expression of literal, host variable or special register. This 
predicate is not entirely composed of constant values. It should 
reference to at least one host variable or special register. 
 
Here is the simple test case : 
 
create function dummy () deterministic no external action 
returns varchar(30) return ( values 'Hello'); 
 
create table demo(col1 int); 
 
insert into demo values 1; 
 
-- get the HH of the current hour 
db2 "SELECT TO_CHAR(CURRENT_TIMESTAMP, 'HH24') FROM 
sysibm.sysdummy1" 
 
-- Pick the HH of the current hour, it will return 1 row of 
'Hello' 
select dummy() from demo where to_char(CURRENT TIMESTAMP,'HH24') 
= '09'; 
 
-- This should return 0 row but it returns a row of NULL! 
select dummy() from demo where to_char(CURRENT TIMESTAMP,'HH24') 
= '-1';

A select query is expected to return 0 rows when its predicate 
is evaluated as false at runtime, however it may return some 
rows when all following conditions are satisfied 
1. Query has scalar UDF (User Defined Function) in its select 
clause 
2. Query has such an equality predicate whose operand is 
expression of literal, host variable or special register. This 
predicate is not entirely composed of constant values. It should 
reference to at least one host variable or special register. 
 
Here is the simple test case : 
 
create function dummy () deterministic no external action 
returns varchar(30) return ( values 'Hello'); 
 
create table demo(col1 int); 
 
insert into demo values 1; 
 
-- get the HH of the current hour 
db2 "SELECT TO_CHAR(CURRENT_TIMESTAMP, 'HH24') FROM 
sysibm.sysdummy1" 
 
-- Pick the HH of the current hour, it will return 1 row of 
'Hello' 
select dummy() from demo where to_char(CURRENT TIMESTAMP,'HH24') 
= '09'; 
 
-- This should return 0 row but it returns a row of NULL! 
select dummy() from demo where to_char(CURRENT TIMESTAMP,'HH24') 
= '-1';

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 9.7 Fixpack 5.

not known / see Local fix

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

02.06.2011
12.12.2011
12.12.2011

9.7.FP5