Re: wrong result from select

From: Joe Testa <teci_at_the-testas.net>
Date: Fri, 08 Feb 2002 15:44:41 -0800
Message-ID: <F001.0040AEFB.20020208152322@fatcity.com>

Here is my result set:

9.0.1.2 on linux:

SQL> set echo on
SQL> alter session set optimizer_mode=choose;

Session altered.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> create table tb1 (f1 number(4));

Table created.

SQL> insert into tb1 values (1999);

1 row created.

SQL> insert into tb1 values (2000);

1 row created.

SQL> insert into tb1 values (2001);

1 row created.

SQL> insert into tb1 values (2002);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tb1;

         F1

---

       1999
       2000
       2001
       2002

SQL> analyze table tb1 compute statistics;

Table analyzed.

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

SQL> drop table tb1;

Table dropped.

SQL> Joe

Alexander.Feinstein_at_mitchell1.com wrote:

> 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 ?
>

-- 
Joe Testa, Oracle DBA
Nothing new to put here, hmmmmmm






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  INET: teci_at_the-testas.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Feb 08 2002 - 17:44:41 CST