# Implicit Data Type Conversion Case Study Background


![left](./student-guide/diagram/01-12-1.png)

+ statement execution plans:

------------------------------------------------------------
When we perform operations on different data type in a database (for example, where clause filters a=1 or join a.id=b.id), the database implicitly converts the relevant type for operational compatibility. Let's introduce the problems caused by implicit type conversion through examples.

Specific case

We have a table like this:

```
CREATE TABLE t3 (
 id int(11) NOT NULL AUTO_INCREMENT,
 k varchar(10) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (id) ,
 KEY k_2 (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1629882;
```

Where column k is of type varchar, and k has a normal index of k_2, let's take a look at the following “normal” SQL and execution plan:

(omitted)

We saw that the k column was indexed, but here we went with the TableFullScan operator and did a full table scan! What's the problem here? The problem is in the where clause k=1 of the equivalence query, where the data k on both sides of the equality operator (=) is a varchar, and 1 is a numeric type, the type is inconsistent. Then we look at the operator info information of explain again, and the optimizer casts the sbtest.k3.k column Function conversion, the conversion is a numeric type double, and a function operation is used on the index field. Naturally, the index k_2 on the k field cannot be used, and then the execution plan goes through the full table scan.