Skip to main content

Search

Welcome to Tibero GTS!

Difference in Result Values Depending on the Presence of START WITH Clause When Using SYS_CONNECT_BY_PATH

Document TypeㅣTroubleshooting 

Category | Migration

Applicable Product Versionㅣ6FS07

Document NumberㅣTMITS002

 

Issue

When using the SYS_CONNECT_BY_PATH function, an issue occurs where the result values are output differently compared to other DBMSs.
 
Below is example data.
CREATE TABLE tab1 (
 id        NUMBER,
 parent_id NUMBER
);
INSERT INTO tab1 VALUES (1, NULL);
INSERT INTO tab1 VALUES (2, 1);
INSERT INTO tab1 VALUES (3, 2);
INSERT INTO tab1 VALUES (4, 2);
INSERT INTO tab1 VALUES (5, 4);
INSERT INTO tab1 VALUES (6, 4);
INSERT INTO tab1 VALUES (7, 1);
INSERT INTO tab1 VALUES (8, 7);
INSERT INTO tab1 VALUES (9, 1);
INSERT INTO tab1 VALUES (10, 9);
INSERT INTO tab1 VALUES (11, 10);
INSERT INTO tab1 VALUES (12, 9);
COMMIT;
 
Full path when START WITH is not used in Tibero
select id, parent_id,                                         
(select 'L' || level || sys_connect_by_path (id, '⇢')
from tab1                                                     
where t1.id = tab1.id and rownum = 1                                                      
connect by prior id = parent_id) as full_path                 
from tab1 t1;      

    ID  PARENT_ID   FULL_PATH           
---------- ---------- -----------------
         1            		L1⇢1
         2          1 		L1⇢2
         3          2 		L1⇢3
         4          2 		L1⇢4
         5          4 		L1⇢5
         6          4 		L1⇢6
         7          1 		L1⇢7
         8          7 		L1⇢8
         9          1 		L1⇢9
        10          9 		L1⇢10
        11         10 		L1⇢11
        12          9 		L1⇢12

12 rows selected.
 
Full path when START WITH is used in Tibero
select id, parent_id,
(select 'L' || level || sys_connect_by_path (id, '⇢')
from tab1
where t1.id = tab1.id and rownum = 1
start with parent_id is null
connect by prior id = parent_id) as full_path
from tab1 t1;    

ID         PARENT_ID   FULL_PATH           


     1            L1⇢1
     2          1 L2⇢1⇢2
     3          2 L3⇢1⇢2⇢3
     4          2 L3⇢1⇢2⇢4
     5          4 L4⇢1⇢2⇢4⇢5
     6          4 L4⇢1⇢2⇢4⇢6
     7          1 L2⇢1⇢7
     8          7 L3⇢1⇢7⇢8
     9          1 L2⇢1⇢9
    10          9 L3⇢1⇢9⇢10
    11         10 L4⇢1⇢9⇢10⇢11
    12          9 L3⇢1⇢9⇢12

12 rows selected.

When the START WITH clause is absent, it is confirmed that the SYS_CONNECT_BY_PATH function does not output the full path correctly.

 

Cause

In the CONNECT BY clause, when using the SYS_CONNECT_BY_PATH function to output the path information from the root to the current row, omitting the START WITH clause causes the full path not to be output correctly.

 

Solutions

In the CONNECT BY clause, if the START WITH clause is omitted, the desired results will not be output correctly. Therefore, the row to be used as the root must be explicitly specified through the START WITH clause for proper operation.
Especially when using the SYS_CONNECT_BY_PATH function, the START WITH clause must be used.
 
 
 

Related to