A SQL statement with a recursive view may yield incorrect
results. The SQL statement uses UNION ALL joining the table onto
a set of columns that forms a unique key. The joined table must
be the table used in the recursive view.
For example:
CREATE TABLE RELATION(ID INTEGER, PARENTID INTEGER, FLAG
CHAR(1));
-- This unique index is required to produce the incorrect
results output
CREATE UNIQUE INDEX UK1 ON RELATION(ID);
CREATE VIEW HIRARCHY(ID, PARENTID, DEPTH)
AS
WITH TREE
(ID, PARENTID, DEPTH) AS
(SELECT ID, PARENTID, 1 AS DEPTH FROM RELATION ROOT
UNION ALL
SELECT CHILD.ID, TREE.PARENTID, DEPTH+1 FROM TREE, RELATION
CHILD WHERE TREE.ID = CHILD.PARENTID)
SELECT * FROM TREE
;
INSERT INTO RELATION VALUES (1,2,'Y'),(2,3,'N');
SELECT H.ID, H.PARENTID, DEPTH
FROM HIRARCHY H,RELATION R
WHERE H.ID = R.ID
AND R.FLAG = 'Y';
The correct result is 2 rows.
ID PARENTID DEPTH
----------- ----------- -----------
1 2 1
1 3 2
DB2 version 9.5 Fix Pack 6 returns only the first row in this
example which is incorrect.
This problem is first introduced in DB2 version 9.5 Fix Pack 6. |
If you do not have the registry variable DB2COMPOPT set,
Perform the following action:
db2set DB2COMPOPT=,65536
Please note the comma preceding the number
If you already have the registry variable set to DB2COMPOPT=x,y
where x and y are numbers that may be present together or by
themselves
then perform the following with appropriate values replacing x
and z
db2set DB2COMPOPT=x,z
where z=65536 + y
and then restart the DB2 instance. |
