|
Problem IC75678
|
Status: Closed |
GROUP BY CLAUSE ON COLUMNS FROM ONE SOURCE INSIDE A SUBSELECT MAY NOT BE PUSHED DOWN IF THE SUBSELECT IS USED IN AN OUTER JOIN |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A GROUP BY clause specified on columns from a single source may
not be fully optimized under certain conditions, which are:
- The GROUP BY clause is used in a subselect
- The subselect is used in an OUTER JOIN
- The GROUP BY clause only groups columns from a single source
- At least one of the columns in the GROUP BY clause defines
uniqueness
- The source of the columns is used in a join
- All columns referenced in the output list of the subselect are
simple, i.e., are not used in any expressions |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* A GROUP BY clause specified on columns from a single source *
* may *
* not be fully optimized under certain conditions, which are: *
* *
* - The GROUP BY clause is used in a subselect *
* *
* - The subselect is used in an OUTER JOIN *
* *
* - The GROUP BY clause only groups columns from a single *
* source *
* - At least one of the columns in the GROUP BY clause defines *
* uniqueness *
* *
* - The source of the columns is used in a join *
* *
* - All columns referenced in the output list of the subselect *
* are *
* simple, i.e., are not used in any expressions *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 5 or later. *
**************************************************************** |
| Local Fix: |
The issue can be worked around by include one or more columns in
the output list of the subselect in a no-op (no-operation)
expression.
For example, an integer column (c1) used in the output list of a
subselect could be re-written as (c1+0). |
| available fix packs: |
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
|
| Solution |
First fixed in DB2 Version 9.7 Fix Pack 5. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 07.04.2011
07.12.2011
07.12.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.7.0.5
|
 |
