RE: wrong result from select

From: <Alexander.Feinstein_at_mitchell1.com>
Date: Thu, 14 Feb 2002 16:07:21 -0800
Message-ID: <F001.00410625.20020214144139@fatcity.com>

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 ? Received on Thu Feb 14 2002 - 18:07:21 CST