Skip to Main Content
  • Questions
  • Converting data types in where clause

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jens.

Asked: September 24, 2019 - 6:26 am UTC

Last updated: September 26, 2019 - 10:21 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom,

My question is regarding when a query is not having the right datatype in the where clause

Example:

-- Create table

CREATE TABLE mytable (
mynumber varchar2(20), primary key(mynumber));


-- Insert some rows

insert into mytable (mynumber)
    select rownum from dual
    connect by rownum <100
;
 
commit;


-- Query with right data type

select * from mytable where mynumber = '10';


-- 1. Explain plan
-- Notice use of index with unique scan
-- 2. Execute query
-- > 10

-- Query with wrong data type

select * from mytable where mynumber = 10;


-- 1. Explain plan
-- Notice to_number and full scan on index
-- 2. Execute query
-- > 10

-- Insert new row with value that cannot be converted to number

insert into mytable (mynumber) values ('xyz');
commit;


-- Query with right data type

select * from mytable where mynumber = '10';


-- 1. Explain plan
-- Notice use of index
-- 2. Execute query
-- > 10

-- Query with wrong data type
select * from mytable where mynumber = 10;


-- 1. Explain plan
-- Notice to_number and full scan on index
-- 2. Execute query
-- > ORA-01722: invalid number

I am able to understand what's going on, but my question is why Oracle chooses to convert all rows (index) to number instead of converting the value in the where clause. I expect there is a perfectly good explanation for that, but I can't seem to figure it out.

BR
Jens

and Chris said...

Think about the following values:

01
1
1.0000

Numerically they're all the same value: one.

But if you compare them as text they're all different!

So when comparing numeric values to strings, the database will to_number the text. This ensures you get all the rows with the same numeric value:

create table t (
  c1 varchar2(10)
);

insert into t values ( '01' );
insert into t values ( '1' );
insert into t values ( '1.000' );

select * from t
where  to_number ( c1 ) = 1;

C1      
01       
1        
1.000    

select * from t
where  c1 = to_char ( 1 );

C1   
1     


The conversion will happen on whichever expression returns character values. Flip the data types around and you'll get all the rows back:

drop table t cascade constraints purge;
create table t (
  c1 number
);

insert into t values ( '01' );
insert into t values ( '1' );
insert into t values ( '1.000' );

select * from t
where  c1 = '1.0';

C1   
    1 
    1 
    1 

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great answer

Jens Bille, September 25, 2019 - 6:30 am UTC

See title

Implicit conversions

Chuck Jolley, September 25, 2019 - 7:36 pm UTC

Implicit conversions should always be considered a bug even if nothing is broken yet.
We had an accidental implicit conversion break when we did a db upgrade and Oracle changed the way it did them.
It took a while to figure out what was wrong.
Chris Saxon
September 26, 2019 - 10:21 am UTC

Indeed.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.