|
Problem IC64772
|
Status: Closed |
Query that uses OLAP specification OVER clause with 0 FOLLOWING might return wrong results. |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
A query that uses the OLAP specification "OVER" clause and also
contains "0 FOLLOWING" might give wrong results.
The following is an example query that can give wrong results:
SELECT C1, C2, COUNT(C2) OVER (ORDER BY C1 ROWS BETWEEN 0
FOLLOWING AND 1 FOLLOWING) from t1
In the case of wrong results, for the above query the 3rd column
that is output (i.e. count(c2)) might show an incorrect number
for the count.
To workaround you can rewrite your query to not use "0
FOLLOWING".
"CURRENT ROW" or "0 PRECEDING" can be used instead. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* OLAP SQL with 0 FOLLOWING *
****************************************************************
* PROBLEM DESCRIPTION: *
* A query that uses the OLAP specification "OVER" clause and *
* also *
* contains "0 FOLLOWING" might give wrong results. *
* *
* *
* *
* The following is an example query that can give wrong *
* results: *
* *
* *
* SELECT C1, C2, COUNT(C2) OVER (ORDER BY C1 ROWS BETWEEN 0 *
* *
* FOLLOWING AND 1 FOLLOWING) from t1 *
* *
* *
* *
* In the case of wrong results, for the above query the 3rd *
* column *
* that is output (i.e. count(c2)) might show an incorrect *
* number *
* for the count. *
* *
* To workaround you can rewrite your query to not use "0 *
* *
* FOLLOWING". *
* *
* "CURRENT ROW" or "0 PRECEDING" can be used instead. *
****************************************************************
* RECOMMENDATION: *
* rewrite the query to not use 0 FOLLOWING or install this *
* APAR *
**************************************************************** |
| Local Fix: |
rewrite the query to not use 0 FOLLOWING or install this APAR |
| available fix packs: |
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
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 |
A query that uses the OLAP specification "OVER" clause and
also
contains "0 FOLLOWING" might give wrong results.
The following is an example query that can give wrong results:
SELECT C1, C2, COUNT(C2) OVER (ORDER BY C1 ROWS BETWEEN 0
FOLLOWING AND 1 FOLLOWING) from t1
In the case of wrong results, for the above query the 3rd
column
that is output (i.e. count(c2)) might show an incorrect number
for the count.
To workaround you can rewrite your query to not use "0
FOLLOWING".
"CURRENT ROW" or "0 PRECEDING" can be used instead. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC64826 IC65715
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 23.11.2009
14.04.2010
14.04.2010 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.9
|
 |
