Re: Operator and condition precedence

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Wed, 9 Sep 2009 21:17:21 +0200
Message-Id: <8A1B26AC-95F8-415B-B961-19AF27630B66_at_gmail.com>

thank you Tanel for this additiional info: '' <-> NULL (and it's shourcut)

so one more test:

select * from dual where 'xyz'>' ' and to_number('abc') > 0

                                                  *

ERROR at line 1:
ORA-01722: invalid number

select * from dual where to_number('abc') > 0 and 'xyz'>' '

                                    *

ERROR at line 1:
ORA-01722: invalid number

select * from dual where dummy > ' ' and to_number(dummy) > 0

                                          *

ERROR at line 1:
ORA-01722: invalid number

select * from dual where to_number(dummy) > 0 and dummy>' '

                          *

ERROR at line 1:
ORA-01722: invalid number

So with '<space>' instead of '' (equals NULL), all these 4 statements fails, without any difference in order of if it's based on a constant or column.

funny to play :-) (no, I will not check 10053 trace, it's to late for me today)
  Martin

Am 09.09.2009 um 21:01 schrieb Tanel Poder:

> 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:
> 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

Received on Wed Sep 09 2009 - 14:17:21 CDT