Re: Operator and condition precedence
Date: Thu, 10 Sep 2009 03:01:21 +0800
Message-ID: <4602f23c0909091201m3dbe811fkd56e3abe49ede640_at_mail.gmail.com>
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 <martin.a.berger_at_gmail.com>wrote:Received on Wed Sep 09 2009 - 14:01:21 CDT
> 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 -- http://www.freelists.org/webpage/oracle-l