Mysql equality against false or true

user_651174 0
Viewed 50

In the following query:

select count(1) from main_territory where code = true
union all
select count(1) from main_territory where code = false;

-------------------------------------------------------------------
count(1)
0
250

Why does putting 0 or false make the query return all rows?

It doesn't matter what the data is, but here's a sample:

CREATE TABLE z_tmp (x varchar(11));
insert into z_tmp values ('x'); -- note this is not a Boolean
select count(1) from z_tmp where x=true union all select count(1) from z_tmp where x=false;

Note: I'm using mysql5.7.

mysql sql database
1 Answers

In MySQL, true is literally the integer 1 and false is the integer 0. This is non-standard and not similar to some other SQL implementations, but this is the way MySQL works.

mysql> select true, false;
+------+-------+
| true | false |
+------+-------+
|    1 |     0 |
+------+-------+

When you compare an integer to a string, the string is cast to its integer value, which is based on any leading digit characters in the string. If it has no leading digits, the integer value is 0.

So you're comparing 0 = 1 and 0 = 0:

mysql> select 'x' = 1, 'x' = 0;
+---------+---------+
| 'x' = 1 | 'x' = 0 |
+---------+---------+
|       0 |       1 |
+---------+---------+

All the rows where x has no leading digits evaluate as 0, and therefore are equal to false.

Bill_Karwin_208600
Related