Re: Operator and condition precedence
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: martin.a.berger@xxxxxxxxx
- Date: Thu, 10 Sep 2009 03:01:21 +0800
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@xxxxxxxxx>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
Other related posts:
