|
Problem IC68077
|
Status: Geschlossen |
SQL0901N WHEN INNER JOIN IS USED IN AN UPDATE OR SELECT QUERY |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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 |
| verfügbare FixPacks: |
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
|
| Lösung |
First Fixed in V9.1 FixPack 10 |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 20.04.2010
16.06.2011
16.06.2011 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.1.FP10
|
| Problem behoben lt. FixList in der Version |
| 9.1.0.10
|
 |
