Re: New Bug of the week ?

From: <dejaisbogus_at_my-deja.com>
Date: 2000/05/12
Message-ID: <8fhs48$r01$1@nnrp1.deja.com>#1/1

In article <956332972.15617.0.nnrp-11.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> How's this for a fun little bug.
>

How about this bug in 8.1.6 on a normal join (does anyone know why the normal join returns 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 INCORRECT (should return 1).

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