RE: wrong result from select

From: Enrique Orbegozo <eorbegozo_at_mef.gob.pe>
Date: Fri, 08 Feb 2002 16:19:58 -0800
Message-ID: <F001.0040AF35.20020208153322@fatcity.com>

Oracle 9.0.1.2
AIX 4.3 64bit, it's OK with "force" and with "similar"
<FONT color=#0000ff

size=2> 
SQL> select *
from tb1;
 
<FONT color=#0000ff

size=2>       
F1----------     
1999      2000     
2001      2002
<FONT color=#0000ff

size=2> 
SQL> select f1
from tb1 where f1 between 2000 and 2000;  
<FONT color=#0000ff

size=2>       
F1----------      2000
<FONT color=#0000ff

size=2> 
SQL> select f1
from tb1 where f1 between 2000 and 2001;  
<FONT color=#0000ff

size=2>       
F1----------     
2000      2001
<FONT color=#0000ff

size=2> 
SQL> select f1
from tb1 where f1 between 2001 and 2000;  
no rows
selected
<FONT color=#0000ff

size=2> 
<FONT color=#0000ff

size=2> 
Enrique
Orbegozo
Database
Administrator
Ministerio de
Economia y Finanzas
Lima -
Peru
<FONT color=#0000ff

size=2> 

  <FONT face=Tahoma
  size=2>-----Mensaje original-----De: root_at_fatcity.com   [mailto:root_at_fatcity.com]En nombre de   Alexander.Feinstein_at_mitchell1.comEnviado el: Viernes, 08 de   Febrero de 2002 05:58 p.m.Para: Multiple recipients of list   ORACLE-LAsunto: 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; <FONT
  size=2>        F1 <FONT
  size=2>----------      
  1999       2000
        2001 <FONT
  size=2>      2002 SQL>
  select f1 from tb1 where f1 between 2000 and 2000; <FONT   size=2>        F1 <FONT
  size=2>----------      
  2000 SQL> select f1 from tb1 where f1 between 2000   and 2001;        
  F1 ---------- <FONT
  size=2>      2000 SQL>
  select f1 from tb1 where f1 between 2001 and 2000; <FONT   size=2>        F1 <FONT
  size=2>----------      
  2001
  TIA
  Alex. PS. What about
  cursor_sharing=similar ? Received on Fri Feb 08 2002 - 18:19:58 CST