Re: New 8.1.6 bug of the day

I get the same results that you do on 8.1.5 using optimizer_mode of choose, however, the following queries, only slightly modified, return the correct results:

(1) Reordering the tables:

SQL> SELECT TABLE1.F1, TABLE1.F2, TABLE2.F1, TABLE2.F2, TABLE2.F3   2 FROM TABLE1, TABLE2, TABLE3
  3 WHERE TABLE1.F1='test1'

  4          AND TABLE1.F1=TABLE2.F3
  5          AND TABLE1.F3=TABLE3.F1;

F1         F2                F1 F2         F3
---------- ---------- --------- ---------- ----------
test1      test2              1 test2      test1

(2) Testing table1.f2 rather than table1.f1:

SQL> SELECT TABLE1.F1, TABLE1.F2, TABLE2.F1, TABLE2.F2, TABLE2.F3   2 FROM TABLE3, TABLE2, TABLE1
  3 WHERE TABLE1.F2='test2'

  4          AND TABLE1.F1=TABLE2.F3
  5          AND TABLE1.F3=TABLE3.F1;

F1         F2                F1 F2         F3
---------- ---------- --------- ---------- ----------
test1      test2              1 test2      test1

I wonder why these modifications should produce in a different result set?

Frank Hubeny

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.
Received on Fri May 12 2000 - 00:00:00 CDT