RE: wrong result from select

[Enrique Orbegozo]

Title: wrong result from select

Oracle 9.0.1.2 AIX 4.3 64bit, it's OK with "force" and with "similar"

 

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
      2001

 

SQL> select f1 from tb1 where f1 between 2001 and 2000;

 

no rows selected

 

 

Enrique Orbegozo

Database Administrator

Ministerio de Economia y Finanzas

Lima - Peru

 

-----Mensaje original-----
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de [EMAIL PROTECTED]
Enviado el: Viernes, 08 de Febrero de 2002 05:58 p.m.
Para: Multiple recipients of list ORACLE-L
Asunto: 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 ?