Oracle Invalid number ORA-01722

I stumbled upon a very brain-f**k error on Oracle 10g on these days.

Context: the following query [sql]SELECT * FROM (
SELECT TO_NUMBER(CUSTOMER_ID) AS SNDG FROM BAD_CODES_TABLE WHERE
AND I_LIKE=UPPER('STATIC_CONDITION') AND CUSTOMER_ID NOT LIKE '%P%' ) S WHERE TO_NUMBER(S.SNDG) >2000[/sql] could trigger a Invalid number if CUSTOMER_ID column contains invalid numbers.

Why?

Well…if you ask to "explain plan", you will get something like

• a table full scan
  • Filter Predicates AND
    • I_LIKE=UPPER('STATIC_CONDITION')
    • TO_NUMBER(S.SNDG) >2000
    • CUSTOMER_ID NOT LIKE '%P%'

Oracle need to do a full scan on the table, and because of that, it decide to reorder the WHERE conditions…

The outer coditions (TO_NUMBER(S.SNDG) >2000) get executed before the condition which will exclude not-number values.

This plan will bring the code which will exclude the bad rows AFTER the TO_NUMBER comparasion!

Googling on Internet, this problem spot from Oracle 10g while Oracle9 is unaffected.

The AskTom reply on this strange "invalid number error" is "you cannot relay on where conditions orders", but in this case there is a subquery, so I think is a big issue.

If you query is a lot more complex then the decipted above (=the execution plan must follow the sub expression order in a more tight way) the problem tend to disappear, but the risk is always behind the door.