|
Problem IC77339
|
Status: Closed |
INCORRECT OUTPUT MIGHT BE RETURNED BY A QUERY WITH PARTITION ELIMINATION INVOLVING MULTIPLE COLUMNS AND NON-CONSTANT KEYS |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
You might encounter incorrect output from a SQL statement that
references a partitioned table with a multi-column range key,
and satisfies all the following conditions:
1. The WHERE clause contains a partition elimination eligible
predicate, p1, with place holder variable, such as COL1=?
2. The WHERE clause contains a correlated subquery or a join,
p2, that is partition elimination eligible.
3. The predicate p1 is applied on a leading column in the range
key, followed by the predicate p2.
For example, consider a table T1 partitioned by range on
(COL1,COL2). If an SQL statement contains a WHERE clause as
SELECT ...
FROM ..., T1, T2, ...
WHERE ... T1.COL1 = ? AND T1.COL2 = T2.X ...
then, you might encounter incorrect output. Likewise, incorrect
output is also possible if the predicates are within a
correlated subquery such as
WHERE T2.Y = (SELECT MAX(T1.Y) FROM T1 WHERE T1.COL1 = ? AND
T1.COL2 = T2.Y)
If the predicates on COL1 and COL2 are switched, you will get
the correct output. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* You might encounter incorrect output from a SQL statement *
* that *
* references a partitioned table with a multi-column range *
* key, *
* and satisfies all the following conditions: *
* *
* 1. The WHERE clause contains a partition elimination *
* eligible *
* predicate, p1, with place holder variable, such as COL1=? *
* 2. The WHERE clause contains a correlated subquery or a *
* join, p2, that is partition elimination eligible. *
* 3. The predicate p1 is applied on a leading column in the *
* range key, followed by the predicate p2. *
* *
* For example, consider a table T1 partitioned by range on *
* (COL1,COL2). If an SQL statement contains a WHERE clause as *
* *
* SELECT ... *
* FROM ..., T1, T2, ... *
* WHERE ... T1.COL1 = ? AND T1.COL2 = T2.X ... *
* *
* then, you might encounter incorrect output. Likewise, *
* incorrect output is also possible if the predicates are *
* within a correlated subquery such as *
* *
* WHERE T2.Y = (SELECT MAX(T1.Y) FROM T1 WHERE T1.COL1 = ? AND *
* T1.COL2 = T2.Y) *
* *
* If the predicates on COL1 and COL2 are switched, you will *
* get the correct output. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 5 *
**************************************************************** |
| Local Fix: |
Replace the place holder variable with a constant or use the
REOPT option. |
| available fix packs: |
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 5 |
| Workaround |
See LOCAL FIX |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.07.2011
24.11.2011
24.11.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.11
|
 |
