Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Sunday, March 15, 2009

Multiplying texts and null effect

Written by Paweł Barut
This time short notice on side effect of implicit type conversion ans short expression evaluation. Let me show this on example - try this statement:
SQL> select 'a'*'b' from dual;
select 'a'*'b' from dual
ERROR at line 1:
ORA-01722: invalid number
As expected we get error. It is due to fact that multiplexing operator (*) is expecting numbers on both sides, an implicit conversion takes place. However this statement:
SQL> select ''*'b' from dual;

does not give error. How is it possible? In oracle empty string is in fact an null. null multiplied by any number gives null, so due to optimization oracle decided to evaluate this expression to null.
As you know also 0 (zero) multiplied by any number gives zero. So let's test it:
SQL> select '0'*'b' from dual;
select '0'*'b' from dual
ERROR at line 1:
ORA-01722: invalid number
In that case Oracle did not optimized evaluation, and decided to make full calculation. I do not know the reason for different behaviour but is repeatable (at least on instances I've access to).
My recommendation: always make sure that you provide data in proper data-type. Make explicit data conversion using to_number, to_date or to_char functions.

Keep reading,

Related Articles on Paweł Barut blog:


Erik van Roon said...

Absolutely true!!
Don't evre (EVER!!!!) trust oracle to do the right thing when it comes to implicit dataconversion.

Example 1:
In a table with a varchar2 column you are only interested in the records where this column has the value '3'.
select *
from mytable
where varcharcolumn = 3;

If any record has a non-numeric value in this column, the query will give you "ORA-01722: invalid number" because oracle converts the column to number rather then the number to the column datatype.

And eve if you are 100% sure that the column only contains numeric values (will it always stay like that???), you are still left with the fact that an index on that column will not be used, since the where clause that is being executed contains a to_number() on that column, rendering the index useless and degrading performance of the query.

Conclusion: implicit conversion is for lazy programmers.


SydOracle said...

"As you know also 0 (zero) multiplied by any number gives zero"
Not quite. Zero multipled by any NON-NULL number gives zero. So the second expression needs to be evaluated in case it returns null.

Paweł Barut said...

@Bell - Thanks for good comments. Implicit conversion should be forbidden ...

@Gary - exactly this is the case. Thanks for emphasizing this



Copyright © Paweł Barut
Printing from DOS to USB Printer