Bug10202228wrong result when _allow_level_without_connect_by set totrue

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

Affects:

Product (Component)

Oracle Server (Rdbms)

Range of versions believed to be affected

Versions >= 11.2.0.1 but BELOW 12.1

Versions confirmed as being affected

11.2.0.211.2.0.1

Platforms affected

Generic (all / most platforms affected)

Fixed:

This issue is fixed in

12.1 (Future Release)11.2.0.3

Symptoms:

Related To:

Wrong ResultsConnect By / Hierarchical Queries_allow_level_without_connect_by

Description

If a select query with,

1.level pseudo column

2. noconnect-by clause

3."_allow_level_without_connect_by" = true

4.plan_table output shows missing filter predicate

5.Wrong result (more rows than expected)

HOOKSparameter:_allow_level_without_connect_by LIKELYAFFECTS XAFFECTS_11.2.0.1 XAFFECTS_V11020001AFFECTS=11.2.0.1 XAFFECTS_11.2.0.2 XAFFECTS_V11020002 AFFECTS=11.2.0.2XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_CONNECTBYTAG_WRONGRES CONNECTBY WRONGRES FIXED_11.2.0.3 FIXED_12.1.0.0

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:10202228 (This link will only work for PUBLISHEDbugs)

Bug 10202228 : QUERY RETURN WRONG RESULT WHEN _ALLOW_LEVEL_WITHOUT_CONNECT_BY SET TO TRUE

To Bottom

Bug Attributes

…

Type

B - Defect

Fixed in Product Version

12.1

Severity

2 - Severe Loss of Service

Product Version

11.2.0.1

Status

80 - Development to QA/Fix Delivered Internal

Platform

912 - Microsoft Windows (32-bit)

Created

14-Oct-2010

Platform Version

2003

Updated

15-Mar-2013

Base Bug

N/A

Database Version

11.2.0.1

Affects Platforms

Generic

Product Source

Oracle

Related Products

…

Line

Oracle Database Products

Family

Oracle Database

Area

Oracle Database

Product

5 - Oracle Database - Enterprise Edition

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 TOTRUE

*** 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 clausegives 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 )