Comparison of Implicit Type Conversion in MSSQL, Oracle, MySQL, and CUBRID
Implicit type conversion, also known as coercion means that even if a user performs operations on two values of different types, the database system will internally cast one of the values to the type of the other one to perform the operation. For a table of rules which illustrates what data types a particular data type may be converted to can be found in Implicit Type Conversion Rules.
In this article we will compare four database systems on how they perform implicit type conversion and whether their implementation affects the performance. They are MSSQL, Oracle, MySQL, and CUBRID 8.4.0.
Overview
The test has been performed over the "event" table which is a part of a demodb database which is created by default when you install CUBRID.
CREATE TABLE "event"(
"code" int,
"sports" char(50),
"name" char(50),
"gender" char(1),
"players" int,
CONSTRAINT pk_event_code PRIMARY KEY("code")
);
The test assumes two cases:
- When the PK is of INT type;
- When the PK is of CHAR type.
MSSQL
First, we create an identical table in MSSQL and perform the test.
INTEGER PRIMARY KEY
Let's see how MSSQL performs if a table PK is of INT type which is a general case.
CREATE TABLE "event"(
"code" int,
"sports" char(50),
"name" char(50),
"gender" char(1),
"players" int,
CONSTRAINT pk_event_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from event where code=20034
MSSQL successfully executes this query using index scan (indicated as Clustered Index Seek).
CHAR in WHERE condition
Now we will execute the same query but will pass CHAR value to code in the WHERE clause.
select * from event where code='20034'
MSSQL again successfully executes this query using index scan (indicated as Clustered Index Seek). In this case implicit type conversion takes place.
CHAR PRIMARY KEY
Let's see how MSSQL performs if a table PK is of CHAR type.
CREATE TABLE "abcd" (
"code" char(3),
"name" char(40) NOT NULL,
"continent" char(10),
"capital" char(30),
CONSTRAINT pk_nation_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from abcd where code=20034
MSSQL successfully executes this query using index scan (indicated as Clustered Index Seek). In this case implicit type conversion takes place.
CHAR in WHERE condition
Now we will execute the same query but will pass CHAR value to code in the WHERE clause.
select * from abcd where code='20034'
MSSQL again successfully executes this query using index scan (indicated as Clustered Index Seek). Implicit type conversion does not take place.
In other words, in both cases MSSQL implicitly converts the types, then performs index scanning.
Oracle
We will create an identical table for Oracle and perform the test.
INTEGER PRIMARY KEY
In this most common case the PK column is of INT type.
CREATE TABLE "event"(
"code" int,
"sports" char(50),
"name" char(50),
"gender" char(1),
"players" int,
CONSTRAINT pk_event_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from event where code=20034
In this case Oracle will use index scan, too (indicated as INDEX UNIQUE SCAN).
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 83 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| AAA | 1 | 83 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C003834 | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
CHAR in WHERE condition
Now we will pass a CHAR value to code in the WHERE clause.
select * from event where code='20034'
In this case, too, Oracle will use index scan (indicated as INDEX UNIQUE SCAN) for which implicit type conversion takes place.
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 83 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| AAA | 1 | 83 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C003834 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------
CHAR PRIMARY KEY
Let's see how MSSQL performs if a table PK is of CHAR type.
CREATE TABLE "abcd" (
"code" char(3),
"name" char(40) NOT NULL,
"continent" char(10),
"capital" char(30),
CONSTRAINT pk_nation_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from abcd where code=20034
In this case implicit type conversion takes place. But as opposed to MSSQL, in this case Oracle will use full scan (indicated as TABLE ACCESS FULL) instead of index scan. According to Oracle manual, if condition is expressed using character type while the actual column type is numerical, then index scan is used. However, if condition is expressed using integer type while the actual column type is character, then full scan is used.
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Plan hash value: 271814976 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| ABCD | 1 | 19 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------
CHAR in WHERE condition
Now we will execute the same query but will pass CHAR value to code in the WHERE clause.
select * from abcd where code='20034'
Oracle executes this query using index scan (indicated as INDEX UNIQUE SCAN). Implicit type conversion does not take place.
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| ABCD | 1 | 19 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C003835 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------
As a result we can confirm that Oracle does implicitly type conversion, however does not perform index scanning in both cases. If PK is defined as a character and integer is used to scan the records, Oracle will perform full table scan.
MySQL
For MySQL also we will create an identical table and perform the test.
INTEGER PRIMARY KEY
Let's see how MySQL performs if a table PK is of INT type which is a general case.
CREATE TABLE "event"(
"code" int,
"sports" char(50),
"name" char(50),
"gender" char(1),
"players" int,
CONSTRAINT pk_event_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from event where code=20034
MySQL successfully executes this query using index scan (indicated as PRIMARY).
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | event | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
CHAR in WHERE condition
Now we will execute the same query but will pass CHAR value to code in the WHERE clause.
select * from event where code='20034'
MySQL again successfully executes this query using index scan (indicated as PRIMARY). In this case implicit type conversion takes place.
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | event | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
CHAR PRIMARY KEY
Let's see how MySQL performs if a table PK is of CHAR type.
CREATE TABLE "abcd" (
"code" char(3),
"name" char(40) NOT NULL,
"continent" char(10),
"capital" char(30),
CONSTRAINT pk_nation_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from abcd where code=20034
Just like Oracle, MySQL performs implicit type conversion but runs the query through full table scan. Looking at the below query plan, we can confirm that if condition is expressed using integer type while the actual column type is character, then full scan is used.
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | abcd | ALL | PRIMARY | NULL | NULL | NULL | 324 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
CHAR in WHERE condition
Now we will execute the same query but will pass CHAR value to code in the WHERE clause.
select * from abcd where code='20034'
MySQL again successfully executes this query using index scan (indicated as PRIMARY). Implicit type conversion does not take place.
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | abcd | const | PRIMARY | PRIMARY | 15 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
As a result we can confirm that MySQL, like all these DBMSs, does implicitly type conversion, however, like Oracle, does not perform index scanning in both cases. If PK is defined as a character and integer is used to scan the records, Oracle will perform full table scan.
CUBRID
Now let's see at how CUBRID performs implicit type conversion and if index scan is used in all cases.
INTEGER PRIMARY KEY
Here is a table where PK is of INT type (general case).
CREATE TABLE "event"(
"code" int,
"sports" char(50),
"name" char(50),
"gender" char(1),
"players" int,
CONSTRAINT pk_event_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from event where code=20034
CUBRID successfully executes this query using index scan (pk_event_code primary key column is used).
iscan
class: event node[0]
index: pk_event_code term[0]
cost: fixed 0(0.0/0.0) var 0(0.0/0.0) card 1
CHAR in WHERE condition
Now we will execute the same query but will pass CHAR value to code in the WHERE clause.
select * from event where code='20034'
CUBRID again successfully executes this query using index scan (pk_event_code primary key column is used). Also implicit type conversion takes place.
iscan
class: event node[0]
index: pk_event_code term[0]
cost: fixed 0(0.0/0.0) var 0(0.0/0.0) card 1
CHAR PRIMARY KEY
Let's see how MSSQL performs if a table PK is of CHAR type.
CREATE TABLE "abcd" (
"code" char(3),
"name" char(40) NOT NULL,
"continent" char(10),
"capital" char(30),
CONSTRAINT pk_nation_code PRIMARY KEY("code")
);
INTEGER in WHERE condition
We will execute the following query with an INT value used in the WHERE clause.
select * from abcd where code=20034
In this case, CUBRID performs more like MSSQL, rather than Oracle or MySQL. By executing the above SQL we can confirm that CUBRID performs index scan (pk_abcd_code primary key column is used) even if the condition clause contains mismatching data types. It will automatically convert to a data type used by the primary key column.
iscan
class: abcd node[0]
index: pk_abcd_code term[0]
cost: fixed 0(0.0/0.0) var 0(0.0/0.0) card 1
CHAR in WHERE condition
Now we will execute the same query but will pass CHAR value to code in the WHERE clause.
select * from abcd where code='20034'
CUBRID again successfully executes this query using index scan (pk_abcd_code primary key column is used). Implicit type conversion does not take place.
iscan
class: abcd node[0]
index: pk_abcd_code term[0]
cost: fixed 0(0.0/0.0) var 0(0.0/0.0) card 1
In other words, like MSSQL, in both cases CUBRID implicitly converts the types, and performs index scanning.
Conclusion
Let's see in a concise table how these databases compare with each other based on the above test results. The check mark below means that that DBMS uses index scan rather than full table scan.
| Oracle | MSSQL | MySQL | CUBRID | |
| If a column, defined as an INTEGER, is scanned using a CHARACTER value. | 
|
|
|
|
| If a column, defined as a CHARACTER, is scanned using a INTEGER value. | 
|
|
|
|