Bug 10202228 wrong result when _allow_level_without_connect_by set to true

Bug
10202228wrong result when _allow_level_without_connect_by set t免费云主机域名o
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

Platforms
affected

Generic
(all / most platforms affected)

Fixed:

This
issue is fixed in

Symptoms:

Related
To:

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

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

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

相关推荐: Redis的应用场景都有哪些

Redis的应用场景都有哪些？这个问题可能是我们日常学习或工作经常见到的。希望通过这个问题能让你收获颇深。下面是小编给大家带来的参考内容，让我们一起来看看吧！Redis 有 8 大应用场景！之前讲过Redis的介绍，及使用Redis带来的优势，这章整理了一下R…