|
Problem IC64027
|
Status: Closed |
JOIN BETWEEN TWO GROUPBY SUBQUERY MAY ABEND DB2 INSTANCE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Query abends DB2 instance with StackTrace in trap file starting
with
pp__17sqlnq_qunqncchild
sqlnq_unify_cstar
sqlnr_gbelim_action
One sample query is
WITH gbsubq1 AS (SELECT c11, c12 FROM t1
GROUP BY c11, c12),
gbsubq2 AS (SELECT DISTINCT c11,
COUNT (*) AS count1,
COUNT(CASE WHEN c12 = 20 THEN 1 ELSE 0
END) AS count2
FROM gbsubq1
GROUP BY c11)
SELECT count1, count2
FROM gbsubq1, gbsubq2
where gbsubq1.c11 = gbsubq2.c11;
No query will get into this problem unless it meets all the
following conditions:
1. Query main block has an equivalent join predicate whose join
keys are from two different group by sub-queries, i.e. gbsubq1
and gbsubq2 in the example.
2. gbsubq2 references gbsubq1. Also, the two join keys are
derived from the common base table column. In this case, that
predicate is "gbsubq1.c11 = gbsubq2.c11", and gbsubq1.c11 and
gbsubq2.c11 are both derived from t1.c11.
3. gbsubq2 SELECT list contains more than one COUNT aggregates.
One ore more COUNT aggregate has CASE expression operand. The
main query block references to at least two gbsubq2's COUNT
aggregates.
4. The SELECT of gbsubq2 has DISTINCT. |
| Problem Summary: |
JOIN BETWEEN TWO GROUPBY SUBQUERY MAY ABEND DB2 INSTANCE |
| Local Fix: |
1. Get rid of "DISTINCT" from gbsubq2 in the sql statement.
2. Replace "COUNT(CASE WHEN c12 = 20 THEN 1 ELSE 0 END)" with
"COUNT(*)" in the sql statement.
The two workaround won't change query result. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
|
| Solution |
First Fixed on DB2 V9.7 Fixpak 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
17.12.2009
17.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
