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

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

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;