Document Type | Issue Resolution
Category | Administration
Applicable Product Versions | Tibero 7.2.3, Tibero 7.2.4
Document Number | TADTS078
Issue
When running the same query on the same table and data environment on both the production server and the development server, the result values differ.
Reproduction Scenario and Script
Table Script
CREATE TABLE COM_CD_test ( GROUP_CD CHAR(5 BYTE) NOT NULL, COM_CD VARCHAR(10 BYTE) NOT NULL, CD_NM VARCHAR(100 BYTE) NULL, EXPLN_CN VARCHAR(1000 BYTE) NULL, UP_CD VARCHAR(10 BYTE) NULL, SORT_NO NUMBER(3) NULL, USE_YN CHAR(1 BYTE) DEFAULT 'Y' NOT NULL, REG_DT TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL, RGTR_PK VARCHAR(50 BYTE) NOT NULL, MDFCN_DT TIMESTAMP(6) NULL, MDFR_PK VARCHAR(50 BYTE) NULL );
Executed Query
SELECT GROUP_CD, COM_CD, CD_NM, EXPLN_CN , UP_CD, SORT_NO, USE_YN, LEVEL FROM (SELECT GROUP_CD, COM_CD, CD_NM, EXPLN_CN , UP_CD, SORT_NO, USE_YN , CASE WHEN CHK_CNT > 0 THEN UP_CD ELSE NULL END AS COPY_UP_CD FROM (SELECT GROUP_CD, COM_CD, CD_NM, EXPLN_CN , UP_CD, SORT_NO, USE_YN , (SELECT COUNT(*) AS CNT FROM COM_CD_test CCMB WHERE CCMA.GROUP_CD = CCMB.GROUP_CD AND EXISTS (SELECT 1 FROM COM_CD_test CCMC WHERE CCMB.GROUP_CD = CCMC.GROUP_CD AND CCMB.COM_CD = CCMC.UP_CD) ) AS CHK_CNT FROM COM_CD_test CCMA ) A WHERE GROUP_CD = 'LS001' ) A START WITH COPY_UP_CD IS NULL CONNECT BY PRIOR GROUP_CD || '^' || COM_CD = GROUP_CD || '^' || COPY_UP_CD ORDER SIBLINGS BY GROUP_CD, UP_CD, SORT_NO;
Results
Production Server
Development Server
It is confirmed that the data values between the production and development servers are clearly different.
Execution Plan on Production Server
Execution Plan on Development Server
Upon reviewing the execution plans, the plans themselves are identical, but there is a difference in the cost values.
Cause
The difference in results between the development and production environments is due to the IMS#343668 patch.
To align with Oracle's specifications, the precedence of the PRIOR operator has been adjusted, and in version 7.2.4, the PRIOR operator precedence is applied higher, causing the following difference:
- 7.2.3: CONNECT BY PRIOR GROUP_CD || '^' || COM_CD = GROUP_CD || '^' || COPY_UP_CD - 7.2.4: CONNECT BY PRIOR GROUP_CD || '^' || COM_CD = GROUP_CD || '^' || COPY_UP_CD
In other words, the specifications applied differ by version, resulting in different outcomes.
Solutions
If you want the same results as the older version (7.2.3), you can work around this in 7.2.4 by enclosing the expression in parentheses as shown below, so that the results match those of 7.2.3.
CONNECT BY PRIOR (GROUP_CD || '^' || COM_CD) = GROUP_CD || '^' || COPY_UP_CD ORDER SIBLINGS BY GROUP_CD, UP_CD, SORT_NO;