Shulou(Shulou.com)06/01 Report--

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:

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.1Platforms affected

Generic (all / most platforms affected)

Fixed:

This issue is fixed in

12.1 (Future Release) 11.2.0.3Symptoms:

Related To:

Wrong ResultsConnect By / Hierarchical Queries_allow_level_without_connect_byDescription

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

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 PUBLISHED bugs)

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 TO TRUE

* * 05:25 on 10-14-10 am * * (CHG: RDBMS Ver.- > NULL-> 11.2.0.1)

* * 05:25 on 10-14-10 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:

-

* * 05:25 on 10-14-10 am * * (CHG: Sta- > 16)

* * 05:26 on 10-14-10 am * * (CHG: Sta- > 10)

* * 05:34 on 10-14-10 am * * (CHG: Sta- > 16)

* * 05:34 on 10-14-10 am * *

* * 04:04 on 11-02-10 am * *

* * 03:10 on 11-24-10 am * * (CHG: Sta- > 11)

* * 03:10 on 11-24-10 am * *

* * 03:11 on 11-24-10 am * *

* * 03:11 on 11-24-10 am * *

* * 03:11 on 11-24-10 am * *

* * 09:29 on 11-24-10 pm * *

* * 09:29 on 11-24-10 pm * *

* * 01:35 on 11-25-10 am * *

* * 01:35 on 11-25-10 am * *

* * 12:21 on 11-29-10 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

* * 09:26 on 12-06-10 pm * *

* * 09:26 on 12-06-10 pm * *

* * 09:26 on 12-06-10 pm * * (CHG: Sta- > 80)

* * 09:46 on 12-06-10 pm * * (ADD: Impact/Symptom- > WRONG RESULTS)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.