I work with both MySQL and PostgreSQL DBMS. Given the choice, I'll use PostgreSQL over MySQL 99% of the time. MySQL has its benefits, don't get my wrong, but overall I find PostgreSQL to be the more mature DBMS. One of my greatest annoyances with MySQL is that it does not have a true BOOLEAN field type. Now if you don't spend hours carefully crafting your database schemas until they are the perfect balance of normalized and utilitized (random made up term), you turn your nose up at non-relational DBMS, and if your co-developers don't consider you something of a DBA nazi, this might not seem to matter to you. But it does, I promise! Stick with me.
See, at first glance, MySQL's treatment of BOOLEAN would seem to be irrelevant. If you do a table definition with BOOLEAN fields, MySQL quietly converts them to TINYINT(1) fields.
CREATE TABLE booleanfun (
ima_primarykey INT AUTO_INCREMENT PRIMARY KEY,
ima_varchar VARCHAR(30),
ima_smallint SMALLINT,
ima_tinyint TINYINT,
ima_boolean BOOLEAN,
ima_bit BIT
);
However, it allows you to feed TRUE and FALSE into the field, converting them to 1
and 0 respectively. And it lets you set a BOOLEAN field to NULL.
INSERT INTO booleanfun (ima_boolean)
VALUES(true);
INSERT INTO booleanfun (ima_boolean)
VALUES(false);
INSERT INTO booleanfun (ima_boolean)
VALUES(NULL);
This seems like perfect BOOLEAN behavior. So what's the problem? There are actually two fairly major ones. See, BOOLEAN validly allows for three “values”: TRUE, FALSE, or NULL (NULL being no-response/non-value). Since it is a TINYINT field, one would expect it to allow 1, 0, or NULL. However, MySQL doesn't care what numbers you put in it once it is created (another of my big MySQL pet peeves – lack of enforcement of data types). Observe:
INSERT INTO booleanfun (ima_boolean)
VALUES(2);
INSERT INTO booleanfun (ima_boolean)
VALUES(20);
INSERT INTO booleanfun (ima_boolean)
VALUES('someone named me');
When you run the query, it throws NO error at all. None. It just takes the 2, it takes the 20, and it quietly “fixes” the string to a 0. Yeah, that annoys me. Fortunately, with the last issue, ColdFusion still throws an error and the transaction doesn't happen. However, for the first, even CFQUERYPARAM won't stop those none 0,1 values from coming through.
What This Means for The Developer: you must to be extra vigilant with the validation you perform on your data server side when dealing with TINYINTs pretending to be BOOLEANs. Don't trust this DB to complain for you!
This leads us by lengthy prose to the other issue for developers. In CFQUERYPARAM, ColdFusion does indeed have cf_sql_tinyint as an option. So it would seem to properly recognize that field type. And when doing inserts, it certainly allows you to put in any valid integer within the range. The problem appears when dealing with NULLs and selects.
<cfquery datasource="#application.dsn#">
INSERT INTO booleanfun (ima_varchar, ima_smallint, ima_tinyint, ima_boolean)
VALUES('Null tests', NULL, NULL, NULL);
</cfquery>
That's what we expect to see, right? So let's query it via ColdFusion:
<cfquery name="getResults" datasource="#application.dsn#">
SELECT *
FROM booleanfun
WHERE ima_varchar LIKE 'Null tests'
</cfquery>
See the problem? ColdFusion properly pulled the regular TINYINT, the SMALLINT, and the BIT as nulls. However, for the “Boolean” TINYINT, i.e. TINYINT(1), it quietly converted our NULL to a 0. It only does this in the results, however. if you run a query asking for ima_boolean IS NULL, you get the results you expected.
I think this is one reason many people may not have noticed this problem before. After all, your queries work, right? So when does it become an issue? If you need to do a conditional on that value later in your code.
<cfquery name="getResults" datasource="#application.dsn#">
SELECT ima_primarykey, ima_boolean
FROM booleanfun
WHERE ima_boolean IS NULL
</cfquery>
<cfif getResults.IMA_BOOLEAN>
My BOOLEAN is true!<br />
<cfelseif getResults.IMA_BOOLEAN IS FALSE>
My BOOLEAN is false!<br />
<cfelse>
My BOOLEAN was not answered!<br />
</cfif>
<cfif getResults.IMA_BOOLEAN EQ "">
My BOOLEAN is blank!<br />
<cfelse>
My BOOLEAN is <cfoutput>#getResults.IMA_BOOLEAN#</cfoutput>
</cfif>
You hit the same problem if you query that initial query. The formerly TINYINT field that was NULL is now 0, so you end up with unexpected results.
So what's the solution? I can think of a few options:
Option 1: Change DBMS. Like I noted above, I prefer PostgreSQL, but at work our boss said we will only use MySQL on future development, so we do MySQL. Depending on your shop or project, choosing another DBMS may or may not be an option.
Option 2: Use cast in all of your ColdFusion queries to get the field to perform like the regular TINY INT does, so you get the expected results every time.
<cfquery name="getResults" datasource="#application.dsn#">
SELECT ima_primarykey, ima_boolean, CAST(ima_boolean AS UNSIGNED) AS ima_booleanCasted
FROM booleanfun
WHERE ima_boolean IS NULL
</cfquery>
Option 3: Forgo the use of the field type all together, since TINYINT is MySQL specific, and choose an alternative. Of course, note this thoroughly in your best practices documentation.
We went for choice 3, redoing our schema's to use SMALLINT instead of BOOLEAN OR TINYINT, and adding selective triggers where we want to enforce the 1, 0, or NULL options. We felt this gave us the best balance of getting the desired results and being more DBMS independent, since SMALLINT is a standard field type found in all real DBMS.
Which option would you go with, or did you use a different solution when coming against this problem?