MySQL Select where Value = 0

Question

---

TL;DR

Why SELECT * FROM title_included = 0 returns one line ?

mysql> SELECT * FROM person_task WHERE title_included = 0;
+----+----------+---------+----------+---------------------+----------+------------+-----------+----------+-----------------------+----------------+-------------+----------+---------+--------------+-----------------+-------------------+---------------+---------------------+---------+---------+-------------------+-------------+---------------------+---------------------+
| id | location | keyword | industry | years_of_experience | language | first_name | last_name | zip_code | title_included        | title_excluded | title_scope | function | company | company_size | seniority_level | tenure_at_company | total_results | last_result_scraped | elapsed | is_done | last_page_scraped | total_pages | creation_time       | scraping_time       |
+----+----------+---------+----------+---------------------+----------+------------+-----------+----------+-----------------------+----------------+-------------+----------+---------+--------------+-----------------+-------------------+---------------+---------------------+---------+---------+-------------------+-------------+---------------------+---------------------+
|  1 | 0        | 0       | 0        | 0                   | 0        | 0          | 0         | 0        | Global IT procurement | 0              | CURRENT     | 0        | 0       | C            | 0               | 0                 |            12 |                  12 | 86.5676 |       0 |                 1 |           1 | 2019-11-02 16:08:51 | 2019-11-02 15:12:34 |
+----+----------+---------+----------+---------------------+----------+------------+-----------+----------+-----------------------+----------------+-------------+----------+---------+--------------+-----------------+-------------------+---------------+---------------------+---------+---------+-------------------+-------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM person_task WHERE title_included = "0";
Empty set (0.00 sec)

---

0

SELECT * FROM person_task WHERE title_included = 0

This first query returns one line.

---

"0"

SELECT * FROM person_task WHERE title_included = "0"

This second query returns no line.

---

Table

Here is my person_task table structure. title_included specifically here is MEDIUMTEXT.

mysql> DESCRIBE person_task;
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| location            | mediumtext   | YES  |     | NULL    |                |
| keyword             | mediumtext   | YES  |     | NULL    |                |
| industry            | mediumtext   | YES  |     | NULL    |                |
| years_of_experience | mediumtext   | YES  |     | NULL    |                |
| language            | mediumtext   | YES  |     | NULL    |                |
| first_name          | mediumtext   | YES  |     | NULL    |                |
| last_name           | mediumtext   | YES  |     | NULL    |                |
| zip_code            | mediumtext   | YES  |     | NULL    |                |
| title_included      | mediumtext   | YES  |     | NULL    |                |
| title_excluded      | mediumtext   | YES  |     | NULL    |                |
| title_scope         | mediumtext   | YES  |     | NULL    |                |
| function            | mediumtext   | YES  |     | NULL    |                |
| company             | mediumtext   | YES  |     | NULL    |                |
| company_size        | mediumtext   | YES  |     | NULL    |                |
| seniority_level     | mediumtext   | YES  |     | NULL    |                |
| tenure_at_company   | mediumtext   | YES  |     | NULL    |                |
| total_results       | mediumint(9) | YES  |     | NULL    |                |
| last_result_scraped | mediumint(9) | YES  |     | NULL    |                |
| elapsed             | float        | YES  |     | NULL    |                |
| is_done             | tinyint(4)   | YES  |     | NULL    |                |
| last_page_scraped   | tinyint(4)   | YES  |     | NULL    |                |
| total_pages         | mediumint(9) | YES  |     | NULL    |                |
| creation_time       | datetime     | YES  |     | NULL    |                |
| scraping_time       | datetime     | YES  |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+
25 rows in set (0.00 sec)

Answer 1

WHERE title_included = 0

You are comparing a string to an integer. When doing this, MySQL converts the string to a numeric datatype. Unless your string starts with a number, the result is 0. Hence all records match.

WHERE title_included = "0" (note: should be '0')

Here you are doing string comparison. Things work as you would expect.

Bottom line: know your datatypes; pass the proper datatype in the queries to avoid implicit conversion. If you want to compare a string, compare it with a string.

---

This is explained in the manual:

In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes places as a comparison of floating-point numbers.

Here is an example of what happens under the hood when you do string to integer comparison:

select cast('a' as float)

| cast('a' as float) |
| -----------------: |
|                  0 |

select cast('1a' as float)

| cast('1a' as float) |
| ------------------: |
|                   1 |

Demo on DB Fiddlde

Answer 2

I believe it is because 0 and "0" are not the same, that is 0 === "0" is false, 0 is an integer and "0" is a string

Answer 3

For literal value mysql use single quote

SELECT * FROM person_task WHERE title_included = '0';