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 )