MySQL SELECT where boolean field is NULL or false

Question

OK I must be missing something really simple here. I just want to return all records from a table where the user_id matches (easy!) and the 'paid' field is either NULL or 0. My 'paid' field is a TinyInt (1).

My CakePHP model code is:

$workingRecord = $this->find('first',array(
    'conditions'=>array(
        'Subscription.user_id'=>$userId,
        array('not' => array('Subscription.paid' => true)) // Not True, to catch both false or NULL values
    )
));

The SQL generated by CakePHP is like this:

SELECT `Subscription`.`id`, `Subscription`.`paid` FROM `subscriptions` AS `Subscription` WHERE `Subscription`.`user_id` = 3 AND NOT (`Subscription`.`paid` = '1') LIMIT 1

Common sense would say this should work. The problem is that that SQL will return rows containing 0 in the 'paid' column, but will never return NULLs.

Is there any way to return zero's and NULLs in the one stroke, without using 'or'?

Thanks in advance.

score 5 · Answer 1 · answered Jul 08 '15 at 12:45

5

When you left join a table with a boolean field, the result set may contain a NULL value even though the joined table's schema does not permit it.

So as a general case I would suggest:

where `Subscription`.`paid` = 0 or `Subscription`.`paid` IS NULL

answered Jul 08 '15 at 12:45

Steven Spungin

27,002
5
88
78

1

I like to use `WHERE NOT coalesce(Subscription.paid, FALSE)` or `coalesce(Subscription.paid, FALSE) = FALSE` to avoid repetition, transmuting NULL to FALSE, so it's only a single boolean comparison – theY4Kman Jul 28 '21 at 01:52

Dave · Accepted Answer · 2012-11-12T07:34:38.323

3

If your field is TINYINT(1), you should not have any NULL values. If you do, run a quick query to replace any NULLs with the default, and set the database field to have a default of 1 or 0.

Then, your code should be:

$workingRecord = $this->find('first',array(
    'conditions'=>array(
        'Subscription.user_id'=>$userId,
        'Subscription.paid' => 0
    )
));

Reason why it's not working like you'd think:

According to MySQL's documentation on 'NOT':

Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is nonzero, and NOT NULL returns NULL.

So - in your case, you're saying "NOT 1", which MySQL translates to " = 0".

edited Nov 12 '12 at 07:34

answered Nov 12 '12 at 07:24

Dave

28,833
23
113
183

So is that a hard and fast rule, that you should never allow null in a boolean MySQL column? – joshua.paling Nov 12 '12 at 08:26
1

@joshua.paling - Personally, yes - I always make sure the database is set up correctly - if it's meant to have a boolean value (true or false), then I always make sure it can only have 2 values, not 3. Not sure if that's a general rule or not. – Dave Nov 12 '12 at 14:46

MySQL SELECT where boolean field is NULL or false

2 Answers2