|
Problem IC68077
|
Status: Closed |
SQL0901N WHEN INNER JOIN IS USED IN AN UPDATE OR SELECT QUERY |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
A customer may encounter SQL0901N running an UPDATE or SELECT
query.
This happens if the query uses INNER JOIN instead of comma
separated join and if one of the following is true:
1. The INNER JOIN is referencing the same table multiple times.
2. There is a subquery joining on a unique column
The message displayed to the user can be "DOID1 is missing" or
"OID imbalance".
Example 1:
***
select
1
from
t1
join t3
on (t1.c1= t3.c1)
join t4
on (t3.c2= t4.c2)
join t4 as t5
on (t3.c2 = t5.c2)
join t4 as t6
on (t1.c2 = t6.c2)
join t4 as t7
on (t1.c1 = t7.c1)
where
t1.c1 <> 1 ;
can be rewritten as:
select
1
from
t1, t3, t4, t4 as t5, t4 as t6, t4 as t7
where
(t1.c1= t3.c1) AND
(t3.c2= t4.c2) AND
(t3.c2 = t5.c2) AND
(t1.c2 = t6.c2) AND
(t1.c1 = t7.c1) AND
t1.c1 <> 1 ;
****
Example 2:
****
INSERT INTO T1
SELECT
v1.c1, v1.c2
FROM
v1
INNER JOIN t2
ON t1.c1=v1.c1
INNER JOIN t3
ON t3.c1 = t2.c1
WHERE v1.c4 IN
(SELECT c4 FROM
TABLE(FOO(123,'A')) AS table_function);
can be rewritten as:
INSERT INTO T1
SELECT
v1.c1, v1.c2
FROM
(SELECT c4 FROM
TABLE(FOO(123,'A')) AS table_function) as SQ
INNER JOIN v1
ON SQ.c4 = v1.c4
INNER JOIN t2
ON t1.c1=v1.c1
INNER JOIN t3
ON t3.c1 = t2.c1;
If the C4 from the table function is NOT unique, this rewritten
query is not equivalent to the original query and we CANNOT use
this work-around.
**** |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users on V9.1 FixPack 9 and below *
****************************************************************
* PROBLEM DESCRIPTION: *
* A customer may encounter SQL0901N running an UPDATE or *
* SELECT *
* query. *
* *
* *
* *
* This happens if the query uses INNER JOIN instead of comma *
* *
* separated join and if one of the following is true: *
* *
* 1. The INNER JOIN is referencing the same table multiple *
* times. *
* 2. There is a subquery joining on a unique column *
* *
* *
* *
* The message displayed to the user can be "DOID1 is missing" *
* or *
* "OID imbalance". *
* *
* *
* *
* Example 1: *
* *
* *
* *
* *
* *
* *** *
* *
* select *
* *
* 1 *
* *
* *
* *
* from *
* *
* t1 *
* *
* join t3 *
* *
* on (t1.c1= t3.c1) *
* *
* join t4 *
* *
* on (t3.c2= t4.c2) *
* *
* join t4 as t5 *
* *
* on (t3.c2 = t5.c2) *
* *
* join t4 as t6 *
* *
* on (t1.c2 = t6.c2) *
* *
* join t4 as t7 *
* *
* on (t1.c1 = t7.c1) *
* *
* *
* *
* where *
* *
* t1.c1 <> 1 ; *
* *
* *
* *
* *
* *
* can be rewritten as: *
* *
* *
* *
* select *
* *
* 1 *
* *
* *
* *
* from *
* *
* t1, t3, t4, t4 as t5, t4 as t6, t4 as t7 *
* *
* where *
* *
* (t1.c1= t3.c1) AND *
* *
* (t3.c2= t4.c2) AND *
* *
* (t3.c2 = t5.c2) AND *
* *
* (t1.c2 = t6.c2) AND *
* *
* (t1.c1 = t7.c1) AND *
* *
* *
* *
* t1.c1 <> 1 ; *
* *
* *
* *
* **** *
* *
* *
* *
* Example 2: *
* *
* *
* *
* **** *
* *
* INSERT INTO T1 *
* *
* SELECT *
* *
* v1.c1, v1.c2 *
* *
* FROM *
* *
* v1 *
* *
* INNER JOIN t2 *
* *
* ON t1.c1=v1.c1 *
* *
* INNER JOIN t3 *
* *
* ON t3.c1 = t2.c1 *
* *
* WHERE v1.c4 IN *
* *
* (SELECT c4 FROM *
* *
* *
* *
* TABLE(FOO(123,'A')) AS table_function); *
* *
* *
* *
* can be rewritten as: *
* *
* *
* *
* INSERT INTO T1 *
* *
* SELECT *
* *
* v1.c1, v1.c2 *
* *
* FROM *
* *
* (SELECT c4 FROM *
* *
* TABLE(FOO(123,'A')) AS table_function) as SQ *
* *
* INNER JOIN v1 *
* *
* ON SQ.c4 = v1.c4 *
* *
* INNER JOIN t2 *
* *
* ON t1.c1=v1.c1 *
* *
* INNER JOIN t3 *
* *
* ON t3.c1 = t2.c1; *
* *
* *
* *
* If the C4 from the table function is NOT unique, this *
* rewritten *
* query is not equivalent to the original query and we CANNOT *
* use *
* this work-around. *
* *
* *
* *
* **** *
****************************************************************
* RECOMMENDATION: *
* Upgrade to V9.1 FixPack 10 *
**************************************************************** |
| Local Fix: |
Replace the inner join with a comma separated join to fix the
issue |
| available fix packs: |
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 |
First Fixed in V9.1 FixPack 10 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.04.2010
16.06.2011
16.06.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.1.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.10
|
 |
