Bug 10202228 wrong result when _allow_level_without_connect_by set to true

Bug
10202228  wrong result when _allow_level_without_connect_by set to
true

 This note gives a brief overview of bug
10202228.
 The content was last updated on: 25-OCT-2011
 Click here for details of each of the sections below.

Affects:

Fixed:

Description

If a select query with,

  1.
level pseudo column

  2. no
connect-by clause

  3.
“_allow_level_without_connect_by” = true

  4.
plan_table output shows missing filter predicate

  5.
Wrong result (more rows than expected)

HOOKS
parameter:_allow_level_without_connect_by LIKELYAFFECTS XAFFECTS_11.2.0.1 XAFFECTS_V11020001
AFFECTS=11.2.0.1 XAFFECTS_11.2.0.2 XAFFECTS_V11020002 AFFECTS=11.2.0.2
XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_CONNECTBY
TAG_WRONGRES CONNECTBY WRONGRES FIXED_11.2.0.3 FIXED_12.1.0.0

References

Bug:10202228 (This link will only work for PUBLISHED
bugs)

Hdr: 10202228 11.2.0.1 RDBMS 11.2.0.1 SQL EXECUTION PRODID-5 PORTID-912
Abstract: QUERY RETURN WRONG RESULT WHEN _ALLOW_LEVEL_WITHOUT_CONNECT_BY SET TO
TRUE
 
*** 10/14/10 05:25 am *** (CHG: RDBMS Ver.-> NULL -> 11.2.0.1)
*** 10/14/10 05:25 am ***
—-
 
PROBLEM:
——–
Query return wrong result when parameter _allow_level_without_connect_by set
to TRUE in 11.2.0.1 ,but works fine with OFE set to 9.2.0 or 10.2.0.1
 
SQL> alter session set “_allow_level_without_connect_by”=true;
SQL> select level,dist_id from dist_main where dist_id=’TH0233542′;
 
     LEVEL DIST_ID
———- ——————–
         0 TH0233542
         0 TH0014199
         0 TH0187012
         0 TH0255131
 
DIAGNOSTIC ANALYSIS:
——————–
When checked the issue with different OFE value
it works fine for 9.2.0 and 10.2.0.1
 
WORKAROUND:
———–
Set OFE to 9.2.0
 
RELATED BUGS:
————-
 
REPRODUCIBILITY:
—————-
Yes, easily reproducible
 
TEST CASE:
———-
SQL> create table dist_main(dist_id varchar2(20));
SQL> insert into dist_main values(‘TH0233542’);
SQL> insert into dist_main values(‘TH0014199’)
SQL> insert into dist_main values(‘TH0187012’)
SQL> insert into dist_main values(‘TH0255131’)
SQL> commit;
SQL> alter session set “_allow_level_without_connect_by”=true;
 
Session altered.
SQL> select level,dist_id from dist_main where dist_id=’TH0233542′;
 
     LEVEL DIST_ID
———- ——————–
         0 TH0233542
         0 TH0014199
         0 TH0187012
         0 TH0255131
 
SQL> alter session set optimizer_features_enable=’9.2.0′;
 
Session altered.
 
SQL> select /*+ optimizer_features_enable(‘9.2.0’) */ level,dist_id from
dist_main where dist_id=’TH0233542′;
 
     LEVEL DIST_ID
———- ——————–
         0 TH0233542
 
STACK TRACE:
————
 
SUPPORTING INFORMATION:
———————–
 
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-
 
DIAL-IN INFORMATION:
——————–
 
IMPACT DATE:
————
 
*** 10/14/10 05:25 am *** (CHG: Sta->16)
*** 10/14/10 05:26 am *** (CHG: Sta->10)
*** 10/14/10 05:34 am *** (CHG: Sta->16)
*** 10/14/10 05:34 am ***
*** 11/02/10 04:04 am ***
*** 11/24/10 03:10 am *** (CHG: Sta->11)
*** 11/24/10 03:10 am ***
*** 11/24/10 03:11 am ***
*** 11/24/10 03:11 am ***
*** 11/24/10 03:11 am ***
*** 11/24/10 09:29 pm ***
*** 11/24/10 09:29 pm ***
*** 11/25/10 01:35 am ***
*** 11/25/10 01:35 am ***
*** 11/29/10 12:21 am ***
RELEASE NOTES:
]] select  query  with  level and no  connect-by clause
gives  wrong
]] results, when _allow_level_without_connect_by is set to true.
 
 
 
 
REDISCOVERY INFORMATION:
 If a select query with,
  1. level pseudo column
  2. no connect-by clause
  3. “_allow_level_without_connect_by” = true
  4. plan_table output shows missing filter predicate
gives wrong result, then probably we are encountering this bug.
WORKAROUND:
None
*** 12/06/10 09:26 pm ***
*** 12/06/10 09:26 pm ***
*** 12/06/10 09:26 pm *** (CHG: Sta->80)
*** 12/06/10 09:46 pm *** (ADD: Impact/Symptom->WRONG RESULTS )