From Alexander.Feinstein@mitchell1.com Thu, 14 Feb 2002 16:07:21 -0800
From: Alexander.Feinstein@mitchell1.com
Date: Thu, 14 Feb 2002 16:07:21 -0800
Subject: RE: wrong result from select
Message-ID: <F001.00410625.20020214144139@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: RE: wrong result from select





Thanks everyone.
Bug# 2225065 Assigned to Development.
Alex.


-----Original Message-----
From: Alexander Feinstein 
Sent: Friday, February 08, 2002 2:58 PM
To: Multiple recipients of list ORACLE-L
Subject: wrong result from select



Hi, 
Can anybody try this on 9i? 
set echo on 
alter session set optimizer_mode=choose; 
alter session set cursor_sharing=force; 
create table tb1 (f1 number(4)); 
insert into tb1 values (1999); 
insert into tb1 values (2000); 
insert into tb1 values (2001); 
insert into tb1 values (2002); 
commit; 
select * from tb1; 
analyze table tb1 compute statistics; 
select f1 from tb1 where f1 between 2000 and 2000; 
select f1 from tb1 where f1 between 2000 and 2001; 
select f1 from tb1 where f1 between 2001 and 2000; 
drop table tb1; 
Here is what I got on 8.1.7.2.1 64bit on HP-UX 11.0: 
SQL> select * from tb1; 
        F1 
---------- 
      1999 
      2000 
      2001 
      2002 
SQL> select f1 from tb1 where f1 between 2000 and 2000; 
        F1 
---------- 
      2000 
SQL> select f1 from tb1 where f1 between 2000 and 2001; 
        F1 
---------- 
      2000 
SQL> select f1 from tb1 where f1 between 2001 and 2000; 
        F1 
---------- 
      2001 
TIA 
Alex. 
PS. What about cursor_sharing=similar ?