Re: New 8.1.6 bug of the day

From: <buckeye714_at_my-deja.com>
Date: 2000/05/15
Message-ID: <8fp6t0$erc$1@nnrp1.deja.com>#1/1

This is a known Oracle Bug and a patch is available. We ran into the problem a couple of months ago. We immediately called Oracle Support and sent them the tables, some data and the query that returned the bad results. They were able to reproduce it in their labs. Turns out it is an old Bug with the Rule Based Optimizer that was once fixed but has been re-introduced in 8.1 Following is a snippit from the TAR we filed:

<Bug:1176886>
  This bug is effectively a duplicate of <Bug:385681> re-introduced in 8.1 If an SMJ and unique index scan and the index scan is known   to return only a single row then a query may either dump or return incorrect results.
  Workaround: Use CBO

As I said, they supplied a patch, but I can no longer find it. Any one with concerns should call Oracle Support and reference the above Bug numbers.

HTH,
Patrick

In article <8fhsnh$rlp$1_at_nnrp1.deja.com>,   dejaisbogus_at_my-deja.com wrote:
>
>
> Why does the following 8.1.6 normal join return bad data?
>
> alter session set optimizer_goal=rule;
>
> DROP TABLE TABLE1;
> CREATE TABLE TABLE1(
> f1 VARCHAR2(10) NOT NULL,
> f2 VARCHAR2(10) NOT NULL,
> f3 VARCHAR2(10),
> CONSTRAINT PK_TABLE1 PRIMARY KEY (f1));
>
> DROP TABLE TABLE2;
> CREATE TABLE TABLE2 (
> f1 NUMBER(10) NOT NULL,
> f2 VARCHAR2(10) NOT NULL,
> f3 VARCHAR2(10),
> CONSTRAINT PK_TABLE2 PRIMARY KEY (f2));
>
> DROP TABLE TABLE3;
> CREATE TABLE TABLE3 (
> f2 VARCHAR2(10) NOT NULL,
> f1 VARCHAR2(10) NOT NULL,
> CONSTRAINT PK_TABLE3 PRIMARY KEY (f2));
>
> INSERT INTO TABLE1 VALUES ('test1','test2','test1');
> INSERT INTO TABLE1 VALUES ('test3','test4','test3');
>
> INSERT INTO TABLE2 VALUES (1,'test2','test1');
> INSERT INTO TABLE2 VALUES (999,'test4','test3');
>
> INSERT INTO TABLE3 VALUES ('test2', 'test1');
> INSERT INTO TABLE3 VALUES ('test4', 'test3');
>
> COMMIT;
>
> SELECT TABLE1.F1, TABLE1.F2, TABLE2.F1, TABLE2.F2, TABLE2.F3
> FROM TABLE3, TABLE2, TABLE1
> WHERE TABLE1.F1='test1'
> AND TABLE1.F1=TABLE2.F3
> AND TABLE1.F3=TABLE3.F1;
>
> F1 F2 F1 F2 F3
> ---------- ---------- ---------- ----------

---

> test1 test2 999 test2 test1
>
> and the 999 is WRONG (should be 1).
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 15 2000 - 00:00:00 CDT