|
Problem IC63007
|
Status: Closed |
SQL0083C ERROR WHEN ISSUING SQL QUERY WITH OUTER JOIN AND INNER JOIN (WITH GROUP BY SUBQUERY) WHICH ACTS LIKE EXISTS CONDITION |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
This problem is introduced with APAR IZ36537 which attempts to
push down GROUP BY subquery to the row producing side to employ
earlier filtering for queries which have a LEFT OUTER JOIN
joined with GROUP BY subquery.
If a query has LEFT OUTER JOIN and INNER JOIN with GROUP BY
subquery, and if the INNER JOIN target's columns are not
used in the query (acts like EXISTS condition) then
query compilation fails with:
"SQL0083C A memory allocation error has occurred"
Queries that can encounter this problem are typically similar to
the following:
CREATE TABLE T1 ( C INT );
CREATE TABLE T2 ( C INT );
CREATE TABLE T3 ( C INT );
CREATE VIEW V AS SELECT * FROM T3 GROUP BY C;
-- Fails with SQL0083C A memory allocation error has occurred.
SELECT T1.C
FROM T1 LEFT OUTER JOIN T2 ON T2.C IS NULL
INNER JOIN V ON V.C = 1
WHERE T1.C > 0;
Following messages appear in db2diag.log:
ZRC=0x820F0004=-2112946172=SQLO_MEM_SIZE "Mem Mgt invalid size"
DIA8563C An invalid memory size was requested.
CALL STACK is similar to the following:
[0] sqlogmblkEx + 0x36C
[1] sqlnr_count_qncs__FP9sqlnq_qunPPiPi + 0xC0
[2]
@58@sqlnr_reorder_OJ_qgm_action__FCP9sqlnq_oprR17sqlnq_qunElemLi
stT2PP13sqlnq_deplistPiCP3loc + 0xC7C
[3]
sqlnr_reorder_IJ_qgm__FCP9sqlnq_qunCP9sqlnq_oprPP13sqlnq_deplist
PiCP3loc + 0x20
[4] sqlnr_qrwprep_phase2__FP3locPi + 0x5C8
[5] sqlnr_exe__FP9sqlnq_qur + 0x968
[6] sqlnr_exe__FP9sqlnq_qur@glue5F0 + 0x70
[7]
sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_compi
leModeT3P14sqlrr_cmpl_enviT7PP9sqlnq_qur + 0x264
[8]
sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_compi
leModeT3P14sqlrr_cmpl_env + 0x24
[9]
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P16sqlra
_cached_varPiPUl + 0x608 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* If a query has LEFT OUTER JOIN and INNER JOIN with GROUP BY *
* *
* subquery, and if the INNER JOIN target's columns are not *
* *
* used in the query (acts like EXISTS condition) then *
* *
* query compilation fails with: *
* *
* "SQL0083C A memory allocation error has occurred" *
* *
* *
* *
* *
* Queries that can encounter this problem are typically *
* similar to *
* the following: *
* *
* *
* *
* CREATE TABLE T1 ( C INT ); *
* *
* CREATE TABLE T2 ( C INT ); *
* *
* CREATE TABLE T3 ( C INT ); *
* *
* CREATE VIEW V AS SELECT * FROM T3 GROUP BY C; *
* *
* -- Fails with SQL0083C A memory allocation error has *
* occurred. *
* SELECT T1.C *
* *
* FROM T1 LEFT OUTER JOIN T2 ON T2.C IS NULL *
* *
* INNER JOIN V ON V.C = 1 *
* *
* WHERE T1.C > 0; *
* *
* *
* *
* *
* *
* Following messages appear in db2diag.log: *
* *
* *
* *
* ZRC=0x820F0004=-2112946172=SQLO_MEM_SIZE "Mem Mgt invalid *
* size" *
* DIA8563C An invalid memory size was requested. *
* *
* *
* *
* *
* *
* CALL STACK is similar to the following: *
* *
* *
* *
* [0] sqlogmblkEx + 0x36C *
* *
* [1] sqlnr_count_qncs__FP9sqlnq_qunPPiPi + 0xC0 *
* *
* [2] *
* @58@sqlnr_reorder_OJ_qgm_action__FCP9sqlnq_oprR17sqlnq_qunElem
* + 0xC7C *
* [3] *
* sqlnr_reorder_IJ_qgm__FCP9sqlnq_qunCP9sqlnq_oprPP13sqlnq_depli
* + 0x20 *
* [4] sqlnr_qrwprep_phase2__FP3locPi + 0x5C8 *
* [5] sqlnr_exe__FP9sqlnq_qur + 0x968 *
* [6] sqlnr_exe__FP9sqlnq_qur@glue5F0 + 0x70 *
* [7] *
* sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_com
* + 0x264 *
* [8] *
* sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_com
* + 0x24 *
* [9] *
* sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P16sql
* + 0x608 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.1.800 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.1 Fix Pack 8 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.09.2009
24.02.2010
24.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.1.800,
9.1.FP8
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.5
|
 |
