|
Problem IC96922
|
Status: Closed |
USER-DEFINED FUNCTION WITH INDEX EXTENSION EXPLOITATION MIGHT RETURN INCORRECT RESULTS IF INDEX IS NOT PRESENT |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
The following is an example of a UDF which might return
incorrect results:
CREATE FUNCTION PSearch (a pair, b pair)
RETURNS Integer
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
PREDICATES
(
WHEN = 1
SEARCH BY EXACT INDEX EXTENSION PairExt
WHEN KEY (a) USE RetrieveKey (b)
WHEN KEY (b) USE RetrieveKey (a)
)
RETURN (0)
The following is a query using the UDF as a predicate:
SELECT * FROM mytable AS a
WHERE PSearch(a.column, 1) = 1
If the index does not exist, the SEARCH BY expression is not
processed and the function PSearch returns the value 0 which
results in no rows being returned. Due to the error in function
processing, all table rows might be returned. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to 10.5.0.3 *
**************************************************************** |
| Local Fix: |
Ensure that the index exists if the function is intended to be
used with a table that has a defined index extension. |
| available fix packs: |
DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
Problem Fixed In 10.5.0.3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 15.10.2013
28.02.2014
07.04.2015 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.3
|
 |
| 10.5.0.3
|
|
