Re: Operator and condition precedence

  • From: Martin Berger <martin.a.berger@xxxxxxxxx>
  • To: Tanel Poder <tanel@xxxxxxxxxx>, Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 9 Sep 2009 21:17:21 +0200

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

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2009