Why doesn't the mysql if function convert nulls?

I have a mysql database where some table uses the entity attribute value anti-pattern. I need to query that table and pull in attributes. I'd like to have them as booleans, i.e., where the left join gives me null (for a non-existent) attribute I want to get false, and if the attribute exists, I want to get true (or 0 and 1).

select 
    -- cap.type as over_21  -- ok: some null, some "over_21"
    -- coalesce(cap.type, false) as over_21    -- ok: some 0, some "over_21"
    -- (case when type is not null then 1 else 0 end) as over_21   -- ok: some 0, some 1
    if (cap.type, true, false) as over_21   -- wrong: all 0
    -- if (cap.type is null, false, true) as over_21  -- ok again
from customer c
left join capabilities cap
on c.id = cap.user_id
and cap.type = 'over_21'

The first commented out line works in so far that it gives me null or the attribute type, here "over_21". (The table doesn't have a value field I could use; if the record exists, that means the attribute is set.)

coalesce and case above work as expected, but when I use the if, the over_21 column shows only 0s.

The IF() function documentation says:

If expr1 is TRUE (expr1 <> 0 and expr1 IS NOT NULL), IF() returns expr2. Otherwise, it returns expr3.

Given that my expr1 is sometimes null, I thought this would turn them nicely into 0s and non-nulls into 1s (or false and true). It does work with the explicit is null check.

Why doesn't if convert my null value to boolean?

Solution

NULL is not a boolean false, nor an integer 0.

If it were false, then NOT (NULL) would be true, right? But it isn't:

mysql> select not null;
+----------+
| not null |
+----------+
|     NULL |
+----------+

Think of NULL as "unknown."

If I don't tell you my middle name, and someone asks you is Bill's middle name Dave? You would say "I don't know."

If they ask you is Bill's middle name not Dave? You can only say "I don't know" again.

Testing your query:

CREATE TABLE customer (
  id INT AUTO_INCREMENT primary key
);

INSERT INTO customer VALUES (1), (2), (3);

CREATE TABLE capabilities (
  user_id INT NOT NULL,
  type VARCHAR(10)
);

INSERT INTO capabilities VALUES
(1, 'over_21'),
(2, 0),
(3, NULL);

select
    c.id, cap.type, cast(cap.type as unsigned),
    if (cap.type, true, false) as over_21   
from customer c
left join capabilities cap
on c.id = cap.user_id
and cap.type = 'over_21';

Result:

+----+---------+----------------------------+---------+
| id | type    | cast(cap.type as unsigned) | over_21 |
+----+---------+----------------------------+---------+
|  1 | over_21 |                          0 |       0 |
|  2 | NULL    |                       NULL |       0 |
|  3 | NULL    |                       NULL |       0 |
+----+---------+----------------------------+---------+

So why was the result 0 for user_id 1?

Because in a boolean context, a string is not implicitly true. In MySQL, booleans and integers are the same, so this is really an integer context.

A string evaluated in an integer context is implicitly converted to a number, by reading any initial digit characters and ignoring the rest. If there are no initial digit characters, then the number value of a string is 0. And 0 is false to MySQL.

mysql> warnings;
Show warnings enabled.

mysql> select 'over_21' + 0;
+---------------+
| 'over_21' + 0 |
+---------------+
|             0 |
+---------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'over_21'