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 )