Unexpected result in WHERE clause on AI ID field

Question

I have a table which's name is users in my MySQL database, and I am using this DB with Ruby on Rails application with ORM structure for years. The table has id field and this field is configured as AI (auto-increment), BIGINT.

Example of my users table;

+----+---------+
| id | name    |
+----+---------+
|  1 | John    |
|  2 | Tommy   |
|  3 | ...     |
|  4 | ...     |
|  5 | ...     |
|  6 | ...     |
+----+---------+

The problem I am facing is when I execute the following query I get unexpected rows.

SELECT * FROM users WHERE id = '1AW3F4SEFR';

This query is returning the exact same value with the following query,

 SELECT * FROM users WHERE id = 1;

I do not know why SQL let me use strings in WHERE clause on a data type INT. And as we can see from the example, my DB converts the strings I gave to the integer at position 0. I mean, I search for 1AW3F4SEFR and I expect not to get any result. But SQL statement returns the results for id = 1.

In Oracle SQL, the behavior of this exact same query is completely different. So, I believe there is something different on MySQL. But I am not sure about what causes this.

user_2270762 · Answer

As has been explained in the request comments, MySQL has a weird way of converting strings to numbers. It simply takes as much of a string from the left as is numeric and ignores the rest. If the string doesn't start with a number the conversion defaults to 0.

Examples: '123' => 123, '12.3' => 12.3, '.123' => 0.123, '12A3' => 12, 'A123' => 0, '.1A1.' => 0.1

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=55cd18865fad4738d03bf28082217ca8

That MySQL doesn't raise an error here as other DBMS do, can easily lead to undesired query results that get a long time undetected.

The solution is easy though: Don't let this happen. Don't compare a numeric column with a string. If the ID '1AW3F4SEFR' is entered in some app, raise an error in the app or even prevent this value from being entered. When running the SQL query, make sure to pass a numeric value, so '1AW3F4SEFR' cannot even make it into the DBMS. (Look up how to use prepared statements and pass parameters of different types to the database system in your programming language.)

If for some reason you want to pass a string for the ID instead (I cannot think of any such reason though) and want to make your query fail-safe by not returning any row in case of an ID like '1AW3F4SEFR', check whether the ID string represents an integer value in the query. You can use REGEXP for this.

SELECT * FROM users WHERE id = @id AND @id REGEXP '^[0-9]+$';

Thus you only consider integer ID strings and still enable the DBMS to use an index when looking up the ID.

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=56f8ee902342752933c20b8762f14dbb

Unexpected result in WHERE clause on AI ID field

1 Answers