Hi,

The following test case is supplied in the metalink in bug no. 2871341, the base bug for this bug is 2679062.

REPRODUCIBILITY:
----------------
Reproduces constantly with simple test case below:
.
TEST CASE:
----------
DROP TABLE A1
/
CREATE TABLE A1 (X1 VARCHAR2(10), X2 VARCHAR2(10))
/
REM *********** Create the second table: ***************
DROP TABLE A2
/
CREATE TABLE A2 (X1 VARCHAR2(10), X2 VARCHAR2(10))
/
INSERT INTO A1 VALUES ('1','2');
INSERT INTO A2 VALUES ('3','4');
COMMIT;
CREATE INDEX A1_X1 ON A1(X1 );
CREATE INDEX A1_X2 ON A1(X2);
CREATE INDEX A2_X1 ON A2(X1);
CREATE INDEX A2_X2 ON A2(X2);
CREATE OR REPLACE VIEW A_ALL AS SELECT * FROM A1 UNION ALL SELECT * FROM A2;
ANALYZE TABLE A1 COMPUTE STATISTICS;
ANALYZE TABLE A2 COMPUTE STATISTICS;
SELECT * FROM A_ALL;
SELECT * FROM A_ALL WHERE (X1='1' AND X2='2') ;
SELECT * FROM A_ALL WHERE ((X1='1' AND X2='2') OR (X1='3' AND X2='4'));

The 2nd query returns answer while the second is not !




The following is published in the 9.2.0.4 fixed bug list:
" 9204 - 2679062 - Wrong results possible from multi-column INLIST "


I have installed 9.2.0.4 patch set on a win 2000 machine Oracle and saw that the above case is actually solved but our application which has a very similar case doesn't.

After investigating I found the following test case that fails, it reproduces only when you have index on all columns (covering index):

drop table t1_1;
drop table t1_2;

create table t1_1(c1 number, c2 number, c3 number);
create table t1_2(c1 number, c2 number, c3 number);
create index t1_1_ix on t1_1(c1, c2, c3);
create index t1_2_ix on t1_2(c1, c2, c3);
create or replace view t1 as select * from t1_1 union all select * from t1_2;

insert into t1_1 values(1, 2, 100);
insert into t1_2 values(1, 2, 200);
commit;

analyze table t1_1 compute statistics;
analyze table t1_2 compute statistics;



prompt
prompt #######################################
prompt try 1 - works fine
prompt #######################################
prompt

select * from t1
where
(c1=1)
and
( (c2=2) and (c3=100)
);



prompt
prompt #######################################
prompt try 2 - works fine
prompt #######################################
prompt

select * from t1
where
(c1=1)
and
(
(c2=2) and (c3=200)
);


prompt
prompt #######################################
prompt try 3 - try 1 OR try 2 does not work !
prompt #######################################
prompt

select * from t1
where
(c1=1)
and
( ( (c2=2) and (c3=100) )
or
( (c2=2) and (c3=200) )
);










opened a TAR and wanted to share with you.




Tal Olier (otal@mercury.co.il).