Problems with booleans

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Problems with booleans

Post by rhecker »

I have a column set to the tinyint or bit type which must allow NULL values because the insert/updates may not contain an explicit value for the column. This means that when I query the column, I can't simply do something like "where columnname != '1' beause it will ignore the Null values, so I have to do: ...where columnname is null OR columnname ='0'

This can't be the right way to do it.
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Problems with booleans

Post by mecha_godzilla »

Hi,

Your query is actually testing two different conditions

1. Is the value NULL?
2. If it isn't NULL, does the value equal 1?

MySQL won't evaluate NULLs numerically so it doesn't even try - at least, that's my understanding of it - but you could do something like this instead:

Code: Select all

SELECT columnname
FROM tablename
WHERE columnname IS NOT TRUE
In a boolean test, both NULL and 0 evaluate to FALSE according to MySQL's rules, so this should hopefully work for you.

HTH,

Mecha Godzilla
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Problems with booleans

Post by requinix »

Are you allowing NULL because that's actually a legitimate use for the column? The column may contain true, false, or neither? Because if your reason is that the column won't always be specified during an INSERT then you can simply set a default value for the column and leave it as a pure boolean field.
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Problems with booleans

Post by rhecker »

M.G., you are right, using IS NOT TRUE instead of IS FALSE does return correctly.

Requinix, ever since I switched to PDO, inserts/updates that don't contain values in a column that does not allow NULL fails. I have set the defaults to '0', but if the column allows NULL, they can still be NULL, and not '0'.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Problems with booleans

Post by requinix »

rhecker wrote:Requinix, ever since I switched to PDO, inserts/updates that don't contain values in a column that does not allow NULL fails. I have set the defaults to '0', but if the column allows NULL, they can still be NULL, and not '0'.
Unless you specify otherwise, a NOT NULL column will (generally) still have NULL as the default. Since that's invalid it forces you to specify a value. If you

Code: Select all

field TINYINT(1) NOT NULL DEFAULT 0
that should allow you to omit it from your INSERTs while not allowing NULL values.
Post Reply