RE: wrong result from select

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 08 Feb 2002 16:02:57 -0800
Message-ID: <F001.0040AF17.20020208152835@fatcity.com>

it
works correctly on 9i but gave me the same erroneous results in 8.1.7.3 Solaris.

  <FONT face=Tahoma
  size=2>-----Original Message-----From:   Alexander.Feinstein_at_mitchell1.com
  [mailto:Alexander.Feinstein_at_mitchell1.com]Sent: Friday, February   08, 2002 5:58 PMTo: Multiple recipients of list   ORACLE-LSubject: 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:02:57 CST