Document TypeㅣTroubleshooting
CategoryㅣTuning
Applicable Product Versionㅣ7FS02PS
Document NumberㅣTTUTS006
Issue
After upgrading from Tibero6 to Tibero7.2.3, performing a hierarchical query like the one below results in an Internal Error [Code: OPT_D2OF2J] with condition '!list_empty(paths) || !list_empty(&or_exp_paths)'.
Query Executed
SELECT
CLASS_ID||'^'||NODE_TYPE||'^'||DECODE(LEAF,1,'true','false')||'^'||ENTITY_TYPE AS ID
,CLASS_ID AS NODE_ID
,CLASS_NM AS TEXT
,NVL(UP_CLASS_ID,'N/A') AS UP_NODE_ID
,DECODE(CONNECT_BY_ISLEAF,1,'true','false') AS LEAF
,TO_CHAR(LEVEL) AS NODE_LEVEL
,TO_CHAR(DISPLAY_NO) AS DISPLAY_NO
FROM (
SELECT
CLASS_ID
,CLASS_NM
,UP_CLASS_ID
,DISPLAY_NO
,USE_YN
,NODE_TYPE
,LEAF
,ENTITY_TYPE
FROM(
SELECT
CLASS_ID
,CLASS_NM
,UP_CLASS_ID
,DISPLAY_NO
,USE_YN
,'C' NODE_TYPE
,CONNECT_BY_ISLEAF AS LEAF
,'' ENTITY_TYPE
FROM
(
SELECT
CLASS_ID AS CLASS_ID
,CLASS_NM
,UP_CLASS_ID
,DISPLAY_NO
,USE_YN
,UPD_USER_ID
,CLASS_TYPE
FROM
AM_CLASS A
WHERE
USE_YN = 'Y'
UNION ALL
SELECT
CLASS_ID||'-0' AS CLASS_ID
,CLASS_NM||'(Logical)' AS CLASS_NM
,UP_CLASS_ID,DISPLAY_NO+0.1 AS DISPLYAY_NO
,USE_YN
,UPD_USER_ID
,CLASS_TYPE
FROM
AM_CLASS
WHERE
USE_YN = 'Y'
AND LOGICAL_YN='Y'
) A
START WITH CLASS_ID = '0'
CONNECT BY PRIOR CLASS_ID = UP_CLASS_ID
ORDER SIBLINGS BY TO_NUMBER(DISPLAY_NO)
)UNION ALL(
SELECT
ENTITY_ID
,ENTITY_NM
,UP_ENTITY_ID
,SORT_ORDER
,USE_YN
,'E'
,0
,ENTITY_TYPE
FROM
AM_ENTITY)
)
START WITH CLASS_ID = '0'
CONNECT BY PRIOR CLASS_ID = UP_CLASS_ID
ORDER SIBLINGS BY TO_NUMBER(DISPLAY_NO)
/
SQL
out file
================================ [1557] 2025/04/24 11:32:53.556 Internal Error [Code:OPT_D2OF2J] with condition '!list_empty(paths) || !list_empty(&or_exp_paths)' (0 args) (build_path.c:153:build_path) (pid=55424, sessid=1557, tid=1557, os_thr_id=57173) [1557] client : ip [10.238.3.110] process [JDBC Thin Client] logon time : [2025/04/24 10:55:49] [1557] prev sql : " SELECT DECODE(MOD_YN,'N','R',AUTH_TYPE) AS AUTH_TYPE FROM( SELECT DISTINCT AUTH_TYPE ,( SELECT MOD_YN FROM AM_ENTITY WHERE ENTITY_ID = A.ENTITY_ID) AS MOD_YN FROM AM_ATTR_USER_GRP_MAPPING A WHERE ENTITY_ID = ? AND USER_GRP_ID IN (SELECT AUTH_ID FROM SYS_USERGRP_TO_AUTH WHERE USER_GRP_ID IN (SELECT USER_GRP_ID FROM SYS_USERGRP_TO_USER WHERE USER_ID = ?)) ) " [1557] current sql : "SELECT /*+ no_outline */ NAME, ID FROM SYS.SQLOBJ$ SO WHERE SO.SIGNATURE = :b0 AND SO.CATEGORY = :b1 AND SO.OBJ_TYPE = 1 AND SO.FLAGS = 1;" [220] *** 2025/04/24 11:32:53.556 *** [220] callstack dump from tbsvr_AGNT for [WTHR, 1557, 57173/55424] [220] SEE DUMP tbsvr.callstack.55424 (lwpid 57173) BY PSTACK ================================
Cause
Due to a side effect of patch 315625a, the logic was modified so that columns used in the siblings order by clause are included in the newly added order list for processing.
However, when performing the optimization rule to check whether a column is unused in the query, the check logic for this list was omitted, causing the column to be mistakenly considered unnecessary and resulting in an Internal Error.
In this query, the USE_YN column was not used in the final SELECT statement, so it was treated as an unnecessary column, which caused the Internal Error during the optimization process.
Solutions
Since the Internal Error occurred because the USE_YN column was not used in the final SELECT statement, you can work around this by modifying the query to include that column in the final SELECT statement.
Also, you can work around the issue by adding conditions such as not null at the end of the CONNECT BY clause that do not affect the query results, or by configuring the final SELECT statement to output USE_YN.
NoteApply the patch through technical support provided by Tmax Tibero.
Example of Modifying CONNECT BY Clause
START WITH CLASS_ID = '0'
CONNECT BY PRIOR CLASS_ID = UP_CLASS_ID
ORDER SIBLINGS BY TO_NUMBER(DISPLAY_NO)
/
=>
START WITH CLASS_ID = '0'
CONNECT BY PRIOR CLASS_ID = UP_CLASS_ID
--and CLASS_NM is not null
and USE_YN is not null
--and NODE_TYPE is not null
--and LEAF is not null
--and ENTITY_TYPE is not null
ORDER SIBLINGS BY TO_NUMBER(DISPLAY_NO)Example of Modifying SELECT Target
SELECT
CLASS_ID||'^'||NODE_TYPE||'^'||DECODE(LEAF,1,'true','false')||'^'||ENTITY_TYPE AS ID
,CLASS_ID AS NODE_ID
,CLASS_NM AS TEXT
,NVL(UP_CLASS_ID,'N/A') AS UP_NODE_ID
,DECODE(CONNECT_BY_ISLEAF,1,'true','false') AS LEAF
,TO_CHAR(LEVEL) AS NODE_LEVEL
,TO_CHAR(DISPLAY_NO)
=>
SELECT
CLASS_ID||'^'||NODE_TYPE||'^'||DECODE(LEAF,1,'true','false')||'^'||ENTITY_TYPE AS ID
,CLASS_ID AS NODE_ID
,CLASS_NM AS TEXT
,NVL(UP_CLASS_ID,'N/A') AS UP_NODE_ID
,DECODE(CONNECT_BY_ISLEAF,1,'true','false') AS LEAF
,TO_CHAR(LEVEL) AS NODE_LEVEL
,TO_CHAR(DISPLAY_NO) AS DISPLAY_NO