Received: (qmail 32102 invoked from network); 9 Sep 2009 14:02:51 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 9 Sep 2009 14:02:44 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 007DBCADDBD; Wed, 9 Sep 2009 15:02:41 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id BbdEG4YWUetk; Wed, 9 Sep 2009 15:02:41 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E02D7CADCB6; Wed, 9 Sep 2009 15:02:03 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 09 Sep 2009 15:01:22 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ABF41CADB66 for ; Wed, 9 Sep 2009 15:01:22 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id z9l8GBBq1GD4 for ; Wed, 9 Sep 2009 15:01:22 -0400 (EDT) Received: from mail-px0-f185.google.com (mail-px0-f185.google.com [209.85.216.185]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 38DD5CACF65 for ; Wed, 9 Sep 2009 15:01:21 -0400 (EDT) Received: by pxi15 with SMTP id 15so341361pxi.25 for ; Wed, 09 Sep 2009 12:01:21 -0700 (PDT) MIME-Version: 1.0 Received: by 10.142.4.39 with SMTP id 39mr40988wfd.128.1252522881402; Wed, 09 Sep 2009 12:01:21 -0700 (PDT) In-Reply-To: <35B30104-23D2-42B9-9CC1-C535BB0CCA59@gmail.com> References: <26fdee6e0909081635h4b1a1562wbf5d51e3d5635461@mail.gmail.com> <26fdee6e0909082219r3dc6efb0lfaf5a4db0f3a3fdb@mail.gmail.com> <35B30104-23D2-42B9-9CC1-C535BB0CCA59@gmail.com> Date: Thu, 10 Sep 2009 03:01:21 +0800 Message-ID: <4602f23c0909091201m3dbe811fkd56e3abe49ede640@mail.gmail.com> Subject: Re: Operator and condition precedence From: Tanel Poder To: martin.a.berger@gmail.com Cc: michaeljmoore@gmail.com, Ric Van Dyke , rjoralist@society.servebeer.com, Oracle L Content-Type: multipart/alternative; boundary=00504502af826b46d7047329b5dd X-archive-position: 20689 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: tanel@poderc.com Precedence: normal Reply-to: tanel@poderc.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --00504502af826b46d7047329b5dd Content-Type: text/plain; charset=ISO-8859-1 Empty string is treated as NULL in Oracle. And you can compare where varchar is bigger than NULL. Once the first predicate evaluated returns false, there is no need to go and check the next predicate in AND condition as the end result would be false anyway. Thus we never really try to apply to_number() to dummy column. Here's your example: SQL> select * from dual where dummy > '' and to_number(dummy) > 0; no rows selected Here's your example with predicates switched around, but Oracle still uses the direct comparison one first and never tries the to_number one: SQL> select * from dual where to_number(dummy) > 0 and dummy > ''; no rows selected But I can force my order of predicates so that to_number(dummy) one would be evaluated first: SQL> select /*+ *ordered_predicates* */ * from dual where to_number(dummy) > 0 and dummy > ''; select /*+ ordered_predicates */ * from dual where to_number(dummy) > 0 and dummy > '' * ERROR at line 1: ORA-01722: invalid number -- Tanel Poder http://blog.tanelpoder.com On Thu, Sep 10, 2009 at 2:33 AM, Martin Berger wrote: > Mike, > does not play it too easy: > > select * from dual where 'xyz'>'' and to_number('abc') > 0 > * > ERROR at line 1: > ORA-01722: invalid number > > > SQL> select * from dual where dummy > '' and to_number(dummy) > 0; > > > no rows selected > > (10.2.0.4 on Sunos10 - 2nodeRAC) > > seems to be something for deeper investigating ;-) > > Martin > > > > Am 09.09.2009 um 07:19 schrieb Michael Moore: > > Ric, > On my system > >> >> >> select * from dual >> where 'xyz'>'' and to_number('abc') > 0; >> > throws a numeric error > > >> >> Mike >> >> >> > > > -- Tanel Poder http://blog.tanelpoder.com --00504502af826b46d7047329b5dd Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Empty string is treated as NULL in Oracle. And you can compare where varcha= r is bigger than NULL.

Once the first predicate evaluated returns fa= lse, there is no need to go and check the next predicate in AND condition a= s the end result would be false anyway. Thus we never really try to apply t= o_number() to dummy column.

Here's your example:

SQL> select * from dual where dummy > '' and to_= number(dummy) > 0;

no rows selected
Here's your example with predicates switched around, but Oracle still u= ses the direct comparison one first and never tries the to_number one:

SQL> select * fr= om dual where to_number(dummy) > 0 and dummy > '';

no rows selected=


But I can force m= y order of predicates so that to_number(dummy) one would be evaluated first= :

SQL> select /*+ ordered_predicates */ = * from dual where to_number(dummy) > 0 and dummy > '';=
select /*+ ordered_pred= icates */ * from dual where to_number(dummy) > 0 and dummy > '= 9;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 *
ERROR at line 1:=
ORA-01722: invalid number


--
Tanel Poder
http://= blog.tanelpoder.com


On Thu, Sep 1= 0, 2009 at 2:33 AM, Martin Berger <martin.a.berger@gmail.com> wrote:
Mike,

does not play it too easy:

select * from dual where 'xy= z'>'' and to_number('abc') > 0
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0*
ERROR at line 1:
ORA-01722: invalid number


SQL> select * from dual where dummy > '&#= 39; and to_number(dummy) > 0; =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0

no rows selected

(10.2.0.4 on = Sunos10 - 2nodeRAC)

seems to be something for deep= er investigating ;-)

Martin



Am 09.09.2009 um 07:19 schrieb Michael Moore:

Ric,
On my system
=A0


select * from dual
where 'xyz'&= gt;'' and to_number('abc') > 0;

throws a numeri= c error
=A0


Mike
=A0
=

<= br>


--
Tanel Poder
http://blog.tanelpoder.com

--00504502af826b46d7047329b5dd-- -- http://www.freelists.org/webpage/oracle-l