A tale of strip-typecasting in MySQL

A tale of strip-typecasting in MySQL

Subscribe to my newsletter and never miss my upcoming articles

Listen to this article

As much as we think we know MySQL, it never fails to surprise us with unusual and weird, yet well-documented, scenarios.

I was perplexed when I learnt about one such thing about MySQL today where it strip-typecasts (as I like to say it) strings to integers. For example, let’s first have a well-defined table schema and insert some dummy data into it.

Assuming the schema like the one shown below -

+---------+----------+--------+-------+-----------+----------------+
| Field   | Type     | Null   | Key   | Default   | Extra          |
|---------+----------+--------+-------+-----------+----------------|
| id      | int(11)  | NO     | PRI   | <null>    | auto_increment |
| name    | text     | YES    |       | <null>    |                |
| city    | text     | YES    |       | <null>    |                |
| state   | text     | YES    |       | <null>    |                |
| country | char(36) | YES    |       | India     |                |
+---------+----------+--------+-------+-----------+----------------+

We insert dummy data in the table after which it looks something like -

+-------+---------+-----------+-------------+-----------+
| id    | name    | city      | state       | country   |
|-------+---------+-----------+-------------+-----------|
| 12345 | Harshit | Bengaluru | Karnataka   | India     |
| 34213 | Rahul   | New Delhi | New Delhi   | India     |
| 34256 | Rohan   | Dehradun  | Uttarakhand | India     |
| 45897 | Satwik  | Mumbai    | Maharashtra | India     |
| 98364 | Aayush  | Pune      | Maharashtra | India     |
+-------+---------+-----------+-------------+-----------+

Now, what according to you will a query like **SELECT * from table_name where id = 12345;** return? Well yes, you’re right. It will return -

+-------+---------+-----------+-----------+-----------+
| id    | name    | city      | state     | country   |
|-------+---------+-----------+-----------+-----------|
| 12345 | Harshit | Bengaluru | Karnataka | India     |
+-------+---------+-----------+-----------+-----------+

Let’s tweak that query a bit to something like **SELECT * from table_name where id = '45897siujwehrirc';** and can you now predict the output? And no, as much as you think that MySQL will throw an error or return an empty set, in fact it doesn’t. Here’s the output that you’d get -

+-------+--------+--------+-------------+-----------+
| id    | name   | city   | state       | country   |
|-------+--------+--------+-------------+-----------|
| 45897 | Satwik | Mumbai | Maharashtra | India     |
+-------+--------+--------+-------------+-----------+

So the question is why this? And the answer is quite a cliche one though -

This is how MySQL works!

What MySQL does is get all the numbers from the start of the string '**45897siujwehrirc**’ and strip off any characters following it before typecasting it to integer and querying the table. So, the resultant query which actually gets fired to the table is -

SELECT * from table_name where id = 45897;

whose expected result matches our result.

Coming to the part where I tell you that MySQL has documented this clearly. MySQL :: MySQL 5.7 Reference Manual :: 12.2 Type Conversion in Expression Evaluation When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some…dev.mysql.com

Weirdly counterintuitive way of casting '45897siujwehrirc' to 45897. Ain't it?

 
Share this

Impressum

Disclaimer

This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organisations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.

Any views or opinions are not intended to malign any religion, ethnic group, club, organisation, company, or individual.

The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.

Downloadable Files and Images

Any downloadable file, including but not limited to pdfs, docs, jpegs, pngs, is provided at the user’s own risk. The owner will not be liable for any losses, injuries, or damages resulting from a corrupted or damaged file.

Comments

Comments are welcome. However, the blog owner reserves the right to delete any comments submitted to this blog without notice due to:

  • Comments deemed to be spam or questionable spam.

  • Comments including profanity.

  • Comments containing language or concepts that could be deemed offensive.

  • Comments containing hate speech, credible threats, or direct attacks on an individual or group.

The blog owner is not responsible for the content in comments.

This blog disclaimer is subject to change at anytime.

Proudly part of