RE: wrong result from select

From: <Alexander.Feinstein_at_mitchell1.com>
Date: Mon, 11 Feb 2002 16:40:01 -0800
Message-ID: <F001.0040C1EA.20020211163847@fatcity.com>

This seems to be a bug in 8.1.7.2...searching our database for known issues. Awaiting Internal Response

TAR# 1975492.995 Alex.

-----Original Message-----
From: Ron Thomas [mailto:rthomas_at_hypercom.com] Sent: Monday, February 11, 2002 4:09 PM
To: Multiple recipients of list ORACLE-L Subject: RE: wrong result from select

OK, so has anyone opened a tar with oracle on this?  I wonder wait their "official" response will be...

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
"Hit any PHB to continue..."

                                                                                                                              

                    Alexander.Feinstein_at_mit                                                                                   

                    chell1.com                     To:     ORACLE-L_at_fatcity.com                                               

                    Sent by:                       cc:                                                                        

                    root_at_fatcity.com               Subject:     RE: wrong result from select                                  

                                                                                                                              

                                                                                                                              

                    02/11/02 04:48 PM                                                                                         

                    Please respond to                                                                                         

                    ORACLE-L                                                                                                  

                                                                                                                              

                                                                                                                              

Thanks Rich,
But it does not work.
One should execute with exactly same order. I you do "where f1 >= 2000 and f1 <= 2001" first, everything works fine (as expected). Here is the last part of the output

SQL> select f1 from tb1 where f1 >= 2000 and f1 <= 2000;         F1

---

      2000

SQL> select f1 from tb1 where f1 >= 2000 and f1 <= 2001;         F1

---

      2000

SQL> select f1 from tb1 where f1 >= 2001 and f1 <= 2000;         F1

---

      2001

SQL> drop table tb1;
Table dropped.
SQL> So far,
works properly on

8.1.6.0.0 NT
8.1.6.0.0 32bit Solaris
9.0.1.2 Linux
9.0.1.2 AIX
9.0.1 Solaris

does not work on

8.1.7.2.0 64bit HP-UX
8.1.7.2.0 32bit HP-UX
8.1.7.3 Solaris

Alex.

-----Original Message-----
Sent: Monday, February 11, 2002 1:40 PM
To: Multiple recipients of list ORACLE-L

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








-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Thomas
  INET: rthomas_at_hypercom.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).

Received on Mon Feb 11 2002 - 18:40:01 CST