Hi Tom, ehm, Chris of course,
after migration our production database to 12.2.0.1, I have found out that there is a problem regarding CONNECT BY with a subquery in the START WITH clause.
FYI: LiveSQL Link didn't work when I copied it, here is the correct one:
https://livesql.oracle.com/apex/livesql/s/g8qg94z3f0vxh7o70gi2u5gxk Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
I have a table TW_GROUP containing articles and their product group. Then I have a hierarchical table TW_GROUP_TREE of all the product groups with columns GRP, PARENT and CODE. Product group 0 is the root group. For my testcase I just need 2 levels, in our system we have some more, of course.
CREATE TABLE TW_GROUP (Article NUMBER, Subgrp NUMBER);
INSERT INTO TW_GROUP VALUES (1, 20);
INSERT INTO TW_GROUP VALUES (2, 23);
CREATE TABLE TW_GROUP_TREE (Grp NUMBER, Parent NUMBER, Code NUMBER);
INSERT INTO TW_GROUP_TREE VALUES (20, 0, 6);
INSERT INTO TW_GROUP_TREE VALUES (23, 0, NULL);
INSERT INTO TW_GROUP_TREE VALUES (0, NULL, 99);
The column CODE can contain a value in each hierarchy, but it is not a must, as you can see at product group 23. So I have to use CONNECT BY to look after it. I want to use the first value I can find in the hierarchy starting with the subgroup that is in TW_GROUP.
In the START WITH clause I use a subquery to get the product group of the article.
This works fine for Article 1:
SELECT Article,
(SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
FROM TW_GROUP_TREE
WHERE Code IS NOT NULL
AND RowNum = 1
CONNECT BY PRIOR Parent = Grp
START WITH Grp = (SELECT SubGrp
FROM TW_GROUP
WHERE Article = u1.Article)) AS Code_with_Subquery
FROM (SELECT 1 AS Article FROM DUAL) u1; ARTICLE CODE_WITH_SUBQUERY
---------- ------------------
1 6
1 row selected.
It works fine for Article 2, too.
SELECT Article,
(SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
FROM TW_GROUP_TREE
WHERE Code IS NOT NULL
AND RowNum = 1
CONNECT BY PRIOR Parent = Grp
START WITH Grp = (SELECT SubGrp
FROM TW_GROUP
WHERE Article = u1.Article)) AS Code_with_Subquery
FROM (SELECT 2 AS Article FROM DUAL) u1; ARTICLE CODE_WITH_SUBQUERY
---------- ------------------
2 99
1 row selected.
Here Code 99 is chosen from the root product group, because product group 23 doesn't have a Code.
But it does not work correctly for both articles at the same time:
SELECT Article,
(SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
FROM TW_GROUP_TREE
WHERE Code IS NOT NULL
AND RowNum = 1
CONNECT BY PRIOR Parent = Grp
START WITH Grp = (SELECT SubGrp
FROM TW_GROUP
WHERE Article = u1.Article)) AS Code_with_Subquery
FROM (SELECT 1 Article FROM DUAL UNION ALL SELECT 2 Article FROM DUAL) u1; ARTICLE CODE_WITH_SUBQUERY
---------- ------------------
1 6
2 6
2 rows selected.
The result for both articles here is 6, which is definitely wrong for article 2, as shown above.
When I remove the subquery from the START WITH clause and put it into the query u1, it works correctly.
SELECT Article,
(SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
FROM TW_GROUP_TREE
WHERE Code IS NOT NULL
AND RowNum = 1
CONNECT BY PRIOR Parent = Grp
START WITH Grp = u1.SubGrp) AS Code_without_Subquery
FROM (SELECT 1 AS Article,
(SELECT SubGrp
FROM TW_GROUP
WHERE Article = 1) AS SubGrp
FROM DUAL
UNION ALL
SELECT 2 AS Article,
(SELECT SubGrp
FROM TW_GROUP
WHERE Article = 2) AS SubGrp
FROM DUAL) u1; ARTICLE CODE_WITHOUT_SUBQUERY
---------- ---------------------
1 6
2 99
2 rows selected.Both versions in the same SQL:
SELECT Article,
(SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
FROM TW_GROUP_TREE
WHERE Code IS NOT NULL
AND RowNum = 1
CONNECT BY PRIOR Parent = Grp
START WITH Grp = (SELECT SubGrp
FROM TW_GROUP
WHERE Article = u1.Article)) AS Code_with_Subquery,
(SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
FROM TW_GROUP_TREE
WHERE Code IS NOT NULL
AND RowNum = 1
CONNECT BY PRIOR Parent = Grp
START WITH Grp = u1.SubGrp) AS Code_without_Subquery
FROM (SELECT 1 AS Article,
(SELECT SubGrp
FROM TW_GROUP
WHERE Article = 1) SubGrp
FROM DUAL d
UNION ALL
SELECT 2 AS Article,
(SELECT SubGrp
FROM TW_GROUP
WHERE Article = 2)
FROM DUAL) u1;And that's my result in 12.2.0.1:
ARTICLE CODE_WITH_SUBQUERY CODE_WITHOUT_SUBQUERY
---------- ------------------ ---------------------
1 6 6
2 6 99
2 rows selected.
As you can see in my test case on livesql.oracle.com, the problem doesn't appear in 18.0.0.0 anymore.
Correct link again:
https://livesql.oracle.com/apex/livesql/s/g8qg94z3f0vxh7o70gi2u5gxk And now my questions:
Is this a known bug in 12.2.0.1? Is there maybe a patch that can solve this?
I don't want to check manually all the SQLs in our system that use CONNECT BY, because the results may not be correct.
Best regards, Tobias
There's certainly a bug of some sort. I can reproduce the behaviour on 12.2 and the fix on 18.1.
I'm not able to track down the root cause though. There's a lot going on in your query. You can use a subquery in your start with clause fine (from at least 11.2.0.4):
SELECT t.*,
connect_by_root grp,
level
FROM TW_GROUP_TREE t
CONNECT BY PRIOR Parent = Grp
START WITH Grp in (
SELECT t.SubGrp
FROM TW_GROUP t
);
GRP PARENT CODE CONNECT_BY_ROOTGRP LEVEL
20 0 6 20 1
0 <null> 99 20 2
23 0 <null> 23 1
0 <null> 99 23 2And if you're looking to find the first non-null value for code in the hierarchy, you could do something like:
with tree as (
select t.*,
connect_by_root grp root,
level lvl
from tw_group_tree t
connect by prior parent = grp
start with grp in (
select t.subgrp
from tw_group t
)
)
select t.*,
first_value ( code ) ignore nulls over (
partition by root
order by lvl rows between unbounded preceding and unbounded following
) first_code_val
from tree t;
GRP PARENT CODE ROOT LVL FIRST_CODE_VAL
20 0 6 20 1 6
0 <null> 99 20 2 6
23 0 <null> 23 1 99
0 <null> 99 23 2 99Which again, works from at least 11.2.0.4 (the oldest version I tested). So if you find you get this I'm sure there's a way to rewrite the query to avoid the issue.
If you want more certainty, contact support to see about a patch.