Re: wrong result from select

I tried the steps on 8.1.7.0.0 on Solaris 2.7, and it worked fine. Trying it on 8.1.7.2.0 provided the erroneous(??) results, but only after the table was analyzed.

Has this been filed as a bug?

Brian

Scott Canaan wrote:

> 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).

--
--------------------------------------
| Brian McGraw     --     Oracle DBA |
| Central Alabama Oracle Users Group |
|------------------------------------|
| mailto:BMcGraw_at_mindspring.com      |
| http://bmcgraw.home.mindspring.com |
--------------------------------------


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Brian McGraw
  INET: brian.mcgraw_at_infinity-insurance.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).