SQL’s Implicit Type Conversion

MySQL/MariaDB

Both MySQL and MariaDB should have the exact same behaviour here, though I didn’t fully check. I find the MariaDB documentation easier to read than the equivalent MySQL page, but the MySQL one is a bit more exhaustive.

I checked some of the behaviour with MariaDB 10.4.

A conversion inside a simple SELECT will convert and return true (1):

chainz@localhost [1]> select 22 = '22';
+-----------+
| 22 = '22' |
+-----------+
|         1 |
+-----------+
1 row in set (0.000 sec)

This is also the case when querying against a column:

chainz@localhost [4]> create table t(a int);
Query OK, 0 rows affected (0.045 sec)

chainz@localhost [5]> insert into t(a) values (22);
Query OK, 1 row affected (0.002 sec)

chainz@localhost [6]> select a from t where a = '22';
+------+
| a    |
+------+
|   22 |
+------+
1 row in set (0.001 sec)

However if not all characters are used in the conversion, MySQL does issue a warning:

chainz@localhost [2]> select 22 = '22a';
+------------+
| 22 = '22a' |
+------------+
|          1 |
+------------+
1 row in set, 1 warning (0.000 sec)

chainz@localhost [3]> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '22a' |
+---------+------+-----------------------------------------+
1 row in set (0.001 sec)

Converting warnings to errors could possibly have prevented Esau’s bug. However I haven’t ever seen an application configured to do this.

PostgreSQL

PostgreSQL’s documentation on type conversion is quite academic. The examples use less common operators, such as the factorial operator, but some conversion still happens with =.

Testing using PostgreSQL 11.7, getting ‘t’ for ‘true’:

postgres=# select '22' = 22;
 ?column?
----------
 t
(1 row)

And while querying:

postgres=# create table t(a int);
CREATE TABLE
postgres=# insert into t(a) values (22);
INSERT 0 1
postgres=# select a from t where a = '22';
 a
----
 22
(1 row)

PostgreSQL is nice though and returns an error if the string doesn’t fully parse:

postgres=# select '22a' = 22;
ERROR:  invalid input syntax for integer: "22a"
LINE 1: select '22a' = 22;

So at least Esau’s bug couldn’t happen on PostgreSQL. PostgreSQL does allow spaces though - and maybe other characters - cases where MySQL/MariaDB would still issue its warning:

postgres=# select '22 ' = 22;
 ?column?
----------
 t
(1 row)

SQLite

SQLite’s data type model is the loosest, with a column’s data type (“affinity”) not restricting the actual types stored in rows. This allows much more flexibility than you’d probably think.

I’m testing with SQLite 3.24.0. Implicit conversion doesn’t actually happen on a plain SELECT, returning 0 for false:

sqlite> select 22 = '22';
0

However using a table allows the column affinity to change the value:

sqlite> create table t(a int);
sqlite> insert into t(a) values (22);
sqlite> select a from t where a = '22';
22

SQLite just won’t find matches for converting a string with trailing letters in:

sqlite> select a from t where a = '22a';
sqlite>

So the bug also couldn’t happen here.

However it will trim spaces - and maybe other characters:

sqlite> select a from t where a = '22 ';
22

SQLite has no warning or error for this conversion as far as I can tell. I guess that makes sense since a string could be stored in an integer column.

Fin

I guess the main takeaway is that we need to be careful with the types our applications send to databases. If you’re using MySQL it might make sense to convert its warnings into errors.

Thanks to Esau for the talk and reviewing this post,

—Adam