# Comparing Mismatched Datatypes

Using comparisons between mismatched data types may degrade query performance and may use unsafe type conversions which can yield undesirable query results. SingleStore will display a warning for queries with potentially problematic comparisons between mismatched data types in the `EXPLAIN` and `information_schema` records for the query.

These warnings do not necessarily indicate a problem, and you may have valid reasons for comparing different data types. But these data type mismatches are flagged to help you identify potential problems that you may not be aware of.

When you see these warnings, you should check whether the data types are expected to be different. You may wish to consider changing the data types of the fields or values involved. You may also wish to consider adding explicit type conversion operations, such as the [cast operators](https://docs.singlestore.com/db/v9.1/reference/sql-reference/conditional-functions/cast-or-convert.md) or functions like [STR\_TO\_DATE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/date-and-time-functions/str-to-date.md) which convert between different types.

These warnings can be seen in `EXPLAIN <query>`, `information_schema.plancache.plan_warnings`, `information_schema.mv_queries.plan_warnings`, and the `PlanWarnings` column of `show plancache` in textual form, as well as in `EXPLAIN JSON <query>`, `information_schema.plancache.plan_info`, and `information_schema.mv_queries.plan_info` in JSON form.

## Example

An example of these warnings is the following:

```sql
CREATE TABLE t (id VARCHAR(50), PRIMARY KEY (id));

EXPLAIN SELECT * FROM t WHERE id = 123;

```

```output

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| WARNING: Comparisons between mismatched datatypes which may involve unsafe datatype conversions and/or degrade performance. Consider changing the datatypes, or adding explicit typecasts. See https://docs.singlestore.com/docs/mismatched-datatypes for more information. |
|                                                                                                                                                                                                                                                                        |
| WARNING: Comparison between mismatched datatypes: (`t`.`id` = 123). Types 'varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL' vs 'bigint(20) NOT NULL'.                                                                                                  |
|                                                                                                                                                                                                                                                                        |
| Gather partitions:all alias:remote_0                                                                                                                                                                                                                                   |
| Project [t.id]                                                                                                                                                                                                                                                         |
| Filter [t.id = 123]                                                                                                                                                                                                                                                    |
| TableScan db.t, PRIMARY KEY (id)                                                                                                                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

```

In this example, the query involves the expression `t.i = 123`, where `t.i` is a string field. When a numeric value is compared to a string, the string is converted to a numeric value, e.g. the string `'123'` is converted to the number `123`.

This comparison may be problematic for multiple reasons:

* The mismatched data types may indicate a mistake in how the query is written or how the table is defined. The query may behave differently than intended and yield undesired results. For example, all of the strings `'123'`, `'123.0'`, `'0123'`, and `'123abc'` compare equal to the integer `123`, so while the query may be intended to retrieve a single row with the specified `id`, this equality comparison may yield multiple rows whose `id` values all compare equal to `123`.
* The mismatched data types negatively impact the performance of the query. If the field and constant were either both strings or both integers, the query plan would be able to use the index to efficiently seek to lookup the matching `id`. But because there are many possible string `id`s that match the number `123`, which do not come in any particular order in terms of string lexicographic order, the query cannot seek into the index and instead must scan all the rows of the table.

***

Modified at: March 8, 2024

Source: [/db/v9.1/reference/sql-reference/data-types/comparing-mismatched-datatypes/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/comparing-mismatched-datatypes/)

(An index of the documentation is available at /llms.txt)