Why doesn't this to_date work, when the results have been filtered to match my date format (Oracle SQL)

Question

I have a table 'A' with one column (VARCHAR2). The table contains a row containing the text '01/01/2021' and another row with the text 'A'.

When I try to filter out 'A' and then to_date the remaining value, I get 'ORA-01858: a non-numeric character was found where a numeric was expected'. I've tried this in 2 ways.

select *
from tbl
where col <> 'A'
and to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');

select *
from (  select * 
        from tbl 
        where col <> 'A')
where to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');

I can understand why the first might not work, but in the second example, the to_date should ONLY ever see filtered data (i.e. '01/01/2020').

When I delete the value of 'A', the statement runs and I get my result back so it seems conclusive that the reason it isn't running is because it's trying to to_date the value of 'A', even though that should have been filtered out by then.

I have been able to replicate this using actual Oracle tables but unfortunately when I try and reproduce the tables using WITH AS, the query works and no error is encountered - another mystery!

Why doesn't this query work? The order of operation seems to be satisfied (and it works if I use WITH AS).

Answer 1

Oracle (and other databases) are under no obligation to evaluate the predicate applied to an inline view before evaluating the outer predicate. Frequently, in fact, from a performance optimization standpoint, you want the optimizer to push a selective predicate from an outer query into a view, inline view, or subquery. In this case, whether the query throws an error will depend on the query plan the optimizer chooses and which predicate it actually evaluates first.

As a quick hack, you can change the inline view to prevent predicates from being pushed. In this case, the presence of a rownum stops the optimizer from pushing the predicate. You could also use hints like no_push_pred to try to force the optimizer to use the plan you want

select *
from (  select t.*, rownum rn
        from tbl t
        where col <> 'A')
where to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');

The issue with either of these quick hacks, though, is that some future version of the optimizer might have more options than you are aware of today so you may have problems in the future.

A better option is to rewrite the query such that you don't care what order the predicates are evaluated. In this case (depending on Oracle version), that's pretty easy since to_date allows you to specify a value when there is a conversion error

select *
from tbl
where col <> 'A'
and to_Date(col default null on conversion error,'DD/MM/YYYY') = 
      to_date('01/01/2020','DD/MM/YYYY');

If you're on an earlier version of Oracle or to_date is just an example of the actual problem, you can create a custom function that does the same thing.

create function safe_to_date( p_str in varchar2, p_fmt in varchar2 )
  return date
is
begin
  return to_date( p_str, p_fmt );
exception
  when value_error
  then
    return null;
end safe_to_date;
select *
from tbl
where col <> 'A'
and safe_to_date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');