Re: wrong result from select

From: Scott Canaan <srcdco_at_ritvax.rit.edu>
Date: Tue, 12 Feb 2002 06:15:16 -0800
Message-ID: <F001.0040C806.20020212060321@fatcity.com>

It works fine in 8.1.6.0.0, Sun Solaris 2.6:

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 12 09:01:22 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production

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> This is the output on 8.1.7.0.0, Sun Solaris 2.6:

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 12 09:03:48 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option
JServer Release 8.1.7.0.0 - Production

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> "Jesse, Rich" wrote:

> Same result on 8.1.7.2 32bit on HP 11.0. The good news is that the correct
> result is obtained using "...where f1 >= 2000 and f1 <= 2001".
>
> Is anyone using 8.1.7.3 on HP/UX 11 to test this?
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
>
> -----Original Message-----
> [mailto:Alexander.Feinstein_at_mitchell1.com]
> Sent: Friday, February 08, 2002 4:58 PM
> To: Multiple recipients of list ORACLE-L
>
> 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 ?
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.com
>
> 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).

--
Scott Canaan (srcdco_at_rit.edu)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it" -
Tom Lehrer


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Canaan
  INET: srcdco_at_ritvax.rit.edu

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 Tue Feb 12 2002 - 08:15:16 CST