My application developers approached me with an issue which is very unique to me. They were complaining about a query which was failing with ORA-01722 “invalid number” after an upgrade to 11.1.0.7 from 10.2.0.4. The syntax of the query is like:
select max(a) from t1 where c1<>'abc' and c2=12345 and c3='Y' and c4='xyz';
This query worked fine in 10204 and was also working fine in another, upgraded, 11.1.0.7 database.
All the columns i.e C1,C2,C3 & C4 are varchar 2(20) .
I ran this query with single quotes around column C2 as:
select max(a) from t1 where c1<>'abc' and c2='12345' and c3='Y' and c4='xyz';
and it worked fine but without single quotes it failed again with same error.
I checked the explain plan of the query and it was doing a “Full Table Scan” on Table T1. Then I opened another 11.1.0.7 database where the same query is working fine and found that there is an index on columns C1,C2,C3 & C4 and the table T1 was getting accessed by Index-Range scan.
Now coming back to the failing 11.1.0.7 database, index on column C4 was missing. After creating index on column C4 the query started to work fine at failing instance.
I am not sure how the absence of an index can cause this issue? Why VARCHAR2 cannot recognize a value without quotes when doing a Full Table Scan?
Your comments are always welcome. Please let us know your views on this.
Whenever the former SQL finds a row in t1 with a non-numeric value of c2, it will try to convert it to a numeric value and raise the error.
When it works, it does so by luck. By that I mean the query plan only scans a subset of the rows in the table, and none of those rows happen to have a non-numeric value in that column
May be couple of reasons. When it do index range scan it just get rowid and get row from that location so conversion might not required OR It’s doing conversion you can watch through extra information from plan. After predicates section with advanced option in dbms_xplan.
Since c2 is a varchar2 column, it could have a non-numeric value. Execution plan based on the Index for column c4 might have filtered all these non-numeric values of column c2, hence it could execute without the error. Error in this scenario, seem to be depend on the data in column C2 and the execution plan. Better way to avoid this error should be using c2 = or an explicit conversion as c2 = To_char(numeric variable/column).
Thanks all for your comments on this.
@Gary : Yes, it is converting as “to_number(value_here)”. It may happen for subset of rows, but still it comes out be very uncertain thing.
@Taral: It is doing a conversion internally and failing on that only.
@Nataraja: Yes, maybe it is filtering the results.
But the question is that, is it an oracle bug ? why the behavior is not consistent?
I did one more test on this,
1. created a new table T2 from T1 using CTAS.
2. ran the same failing query in failing instance on table T2 (T2 is having no indexes).
3. It ran fine without any error !!!!
Now, this has created more confusion on this behavior.
I know the cause:
1. SQL transformation is taken, something like:
TO_NUMBER(C2)=xxx
2. Predicate order plays a big role on this issue.
Only the rows satisfied first predicate/filter will pass to next predicate/filter. So the rows which can’t be converted to number will fire 1722. Those rows may not be passed to to_number(c2) predicate in some cases. That’s why it happened intermittently.
In Original query with index which is failing try to give full hint and see are you able to reproduce same error.
@SYNC: But I think Oracle should fix this type of behavior as it is making the query unpredictable.
@Taral: With Full hint, it is not failing.
So, If it’s not failing with full hint. That means may be conversion from index is not right or index itself may be corrupted.
1. Try to compare plan/predicates for bot with index and with full hint. I believe conversion would take place in both case. But to make sure
2.
Rebuild index and then Try. We have faced many times bugs in 10.2.0.3 regarding merger/or direct path load might corrupt index.