SQL & PL/SQL

No, if there's a value in col2 which cannot be converted to a number you will get a conversion error.
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10765/ext_optimizer.htm#ADDCI4552

"
Predicate Ordering

In the absence of an ORDERED_PREDICATES hint, predicates (except those used for index keys) are evaluated in the order specified by the following rules:

*

Predicates without any user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.
*

Predicates with user-defined functions and type methods which have user-computed costs are evaluated in increasing order of their cost.
*

Predicates with user-defined functions and type methods that have no user-computed cost are evaluated next, in the order specified in the WHERE clause.
*

Predicates not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.
*

Predicates with subqueries are evaluated last in the order specified in the WHERE clause.

"

Yes it will.

create table t
(col1 varchar2(2), col2 varchar2(2));

insert into t (col1,col2) values ('A','1');
insert into t (col1,col2) values ('A','2');
insert into t (col1,col2) values ('B','X');
insert into t (col1,col2) values ('B','Y');

select * from T where col1 = 'A' and to_number(col2) between 1 and 2;

COL1	COL2

A	1
A	2

As mentioned by Tubby The below query will give an error.

select * from T where col1 = 'B' and to_number(col2) between 1 and 2;

Edited by: AJR on Jun 22, 2010 8:39 AM

As you have given the condition for first column the query works. But whe ever it tries to compare a number with character it throws error.

Try using case statements which helps you to solve most problems.

select case when col2 = 1 then col2 when col2 = 2 then col2 end from t where col1 = 'A';

thanks,
Vinodh

Edited by: Vinodh2 on Jun 22, 2010 8:54 AM

No. It is pure luck if it is working. Might work today and will raise an error tomorrow.
The order of the filter criteria does not depend on the order how it is written in the select statement.
Yes there are certain rules as tubby showed. However in real world examples those rules are the very last criteria that are applied.

If somebody puts an index on col2 it could break.
If this query is used inside some larger select, the order might change and different rows might be processed first.
If some other programmer changes the order because it looks better it will break.

ConclusioN: DO not rely on such a statement to work.

Edited by: Sven W. on Jun 22, 2010 6:00 PM

Thanks for all the responses. Let me elaborate:

My first criteria in the where clause (col1='A') is guaranteed to only select those rows that have a numeric value in col2. Given that fact, will the query always work?

My real query has few other criteria in the where clause so my fear is that the oracle optimizer may choose to evaluate the predicates in a different order (other than left to right) and I would get an exception. I would like for the query to work irrespective of the path that the optimizer chooses. I could use the ORDERED_PREDICATES hint. Does anyone have any other suggestions?

Thanks
Aspi

Yes. Write your statement so that you do not depend on the dataconversion to work.

Example

select * from T where col1 = 'A' and to_number(regexp_substr(col2,'([[:digit:]])+')) between 1 and 2;

This would also work with column values like 'X1' (=> translates to number 1) which is not part of the original scenario, but maybe you like that.

Important! Test it with many different values to see what the result is. Also consider non-integer values in different languages. e.g. '1.2' or '1,2' or '1,00,000.00'

Edited by: Sven W. on Jun 22, 2010 6:20 PM

Any suggestions as to why this will not work? This is the real query that I am struggling with. The column "message_rvsequence" would be "col2" from my previous example. Column 'message_subject' would be Col1.

I know for a fact that for those rows where message_subject like 'VALUE%', the returned value of message_rvsequence will always be numeric. I would have expected the inline view to evaluate first, and subsequently using a 'to_number' on the returned value should work. But it does not.

 select * from (
  select message_rvsequence
  from mms_message_info
  where
  realm_id = 30 and
  message_subject like 'VALUE%' and
  message_date between to_date('06/21/2010 09:40:00', 'MM/DD/YYYY HH24:MI:SS') and
  to_date('06/21/2010 12:10:00', 'MM/DD/YYYY HH24:MI:SS')
  ) T
where to_number(T.message_rvsequence) between 1 and 29;

Without seeing an explain plan, I'm guessing that the optimizer is un-nesting your inline view.

Try adding ROWNUM to the select list of your inline view.

Adding the rownum worked.

Explain plan before adding rownum:

  
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    75 |  2762   (1)| 00:00:39 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| MMS_MESSAGE_INFO    |     1 |    75 |  2762   (1)| 00:00:39 |
|*  2 |   INDEX RANGE SCAN          | MMS_MESSAGE_INFO_N2 | 12845 |       |    18   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MESSAGE_SUBJECT" LIKE 'VALUE%' AND TO_NUMBER("MESSAGE_RVSEQUENCE")>=1 AND
              TO_NUMBER("MESSAGE_RVSEQUENCE")<=29 AND "REALM_ID"=30)
   2 - access("MESSAGE_DATE">=TO_DATE(' 2010-06-21 09:40:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "MESSAGE_DATE"<=TO_DATE(' 2010-06-21 12:10:00', 'syyyy-mm-dd hh24:mi:ss'))

Explain Plan after adding rownum:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |   140 |  2762   (1)| 00:00:39 |
|*  1 |  VIEW                         |                     |     1 |   140 |  2762   (1)| 00:00:39 |
|   2 |   COUNT                       |                     |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MMS_MESSAGE_INFO    |     1 |    75 |  2762   (1)| 00:00:39 |
|*  4 |     INDEX RANGE SCAN          | MMS_MESSAGE_INFO_N2 | 12845 |       |    18   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("T"."MESSAGE_RVSEQUENCE")<=29 AND
              TO_NUMBER("T"."MESSAGE_RVSEQUENCE")>=1)
   3 - filter("MESSAGE_SUBJECT" LIKE 'VALUE%' AND "REALM_ID"=30)
   4 - access("MESSAGE_DATE">=TO_DATE(' 2010-06-21 09:40:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "MESSAGE_DATE"<=TO_DATE(' 2010-06-21 12:10:00', 'syyyy-mm-dd hh24:mi:ss'))

Can you help explain why did it work, and would this always work?

Thanks

The magic 8 ball says: 'Unlikely'

If you're looking for someone to guarantee that the optimizer will work in the future as it currently does then you might be out of luck.

Your best bet is write strong code that doesn't rely on current execution paths in order to avoid errors.

MScallion is correct. Relying on the physical layout of your sql statement to avoid exceptions is going to cause problems eventually.

In addition, it sounds like you have a design issue in your table. Your message_rvsequence column seems to have multiple meanings based on the contents of your message_subject column.

Hehe case expression uses short-circuit evaluation B-)
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/expressions004.htm

The same thread of OTN-Japan ;-)
http://www.oracle.co.jp/forum/thread.jspa?threadID=35007126

with t(Col1,Col2) as(
select 'A','1' from dual union
select 'A','2' from dual union
select 'B','X' from dual union
select 'B','Y' from dual)
select *
  from T
 where case when lnnvl(col1 = 'A')
            then 0
            when to_number(col2) between 1 and 2
            then 1
            else 0 end = 1;

C  C
-  -
A  1
A  2

Oh adding rownum is mentioned SQLCookBook :-)
http://oreilly.com/catalog/9780596009762/index.html

How about this. Set up a function is_number that returns 1 if the parameter is a number else 0

create or replace function is_number(i_numstring varchar2)
  return number is
  v_number  number;
begin
  v_number := to_number(i_numstring);
  return 1;
exception
  when others then
    return 0;
end;

Now use that in your query:

select * from ( 
  select message_rvsequence 
  from mms_message_info 
  where 
  realm_id = 30 and 
  message_subject like 'VALUE%' and 
  message_date between to_date('06/21/2010 09:40:00', 'MM/DD/YYYY HH24:MI:SS') and 
  to_date('06/21/2010 12:10:00', 'MM/DD/YYYY HH24:MI:SS')
  ) T 
where case when is_number(T.message_rvsequence) = 1
           then to_number(T.message_rvsequence) 
           else 30
      end between 1 and 29;