Using MySQL's Null-Safe Equality Operator With CFQueryParam's Null Option In Lucee CFML 5.3.3.62
The other day, I was in the middle of writing a "typical" conditional MySQL query that had special handling for NULL values when it suddenly occurred to me that I was doing more work than I had to. I don't work with NULL values very often in SQL and ColdFusion; so, I fear that my instincts for them have never been honed properly. That said, in a moment of clarity, it occurred to me that I can use MySQL's Null-Safe equality operator in conjunction with the null attribute of the CFQueryParam tag to simplify certain types of prepared SQL statements in Lucee CFML 5.3.3.62.
To illustrate what I mean, consider the following SQL query using the ColdFusion CFQuery tag:
<!--- Mock out some search values. --->
<cfset filterProjectID = 9 />
<cfset filterParentID = 0 />
<cfquery name="test" datasource="testing">
SELECT
f.*
FROM
asset_folder f
WHERE
f.projectID = <cfqueryparam value="#filterProjectID#" sqltype="integer" />
<!---
Since we can't compare a NULL value using the "=" operator, we have to create
two different logic branches in order to handle the NULL value specially.
--->
<cfif filterParentID>
AND
f.parentID = <cfqueryparam value="#filterParentID#" sqltype="integer" />
<cfelse>
AND
f.parentID IS NULL
</cfif>
;
</cfquery>
For the sake of this discussion, imagine that this query is being run as part of a dynamic filter where the filterParentID is optional and defaults to 0. In this query, the parentID column is either a NULL value or an INT value. And so, if the filter value is 0, I have to use the IS NULL comparison; and, if the filter value is non-zero, I have to use the traditional equality operator.
The reason for this dichotomy is that the following comparison in MySQL will always result in NULL, even if the parentID column is NULL:
f.parentID = NULL
At least in MySQL, NULL never equals anything - not even another NULL value. Which is why I have to conditionally use the IS operator instead of the = operator.
That said, MySQL does have a Null-Safe equality operator: <=>. This operator can safely compare one NULL value to another NULL value. And this is where my epiphany was: I can use the Null-Safe operator to simply the above query:
<!--- Mock out some search values. --->
<cfset filterProjectID = 9 />
<cfset filterParentID = 0 />
<cfquery name="test" datasource="testing">
SELECT
f.*
FROM
asset_folder f
WHERE
f.projectID = <cfqueryparam value="#filterProjectID#" sqltype="integer" />
<!---
With the NULL-Safe equality operator, NULL = NULL results in "1", which means
that the following condition works for both NULL and non-NULL values.
--->
AND
f.parentID <=> <cfqueryparam value="#filterParentID#" sqltype="integer" null="#( ! filterParentID )#" />
;
</cfquery>
As you can see, no more branching logic in my CFQuery tag. I have a single Null-Safe condition for the f.parentID column. And, my CFQueryParam tag will yield a NULL value using the null attribute if the filterParentID is zero. Easy peasy!
Now, not all conditional filter queries work this way. For example, if my filterParentID value defaulted to null instead of 0, then my CFQueryParam tag would have to become a bit more complex:
<!--- Mock out some search values. --->
<cfset filterProjectID = 9 />
<cfset filterParentID = nullValue() />
<cfquery name="test" datasource="testing">
SELECT
f.*
FROM
asset_folder f
WHERE
f.projectID = <cfqueryparam value="#filterProjectID#" sqltype="integer" />
<!---
With the NULL-Safe equality operator, NULL = NULL results in "1", which means
that the following condition works for both NULL and non-NULL values.
--->
AND
f.parentID <=> <cfqueryparam value="#( filterParentID ?: 0 )#" sqltype="integer" null="#isNull( filterParentID )#" />
;
</cfquery>
In this case, I can't just use the filterParentID value or I would get a ColdFusion null-reference error. Instead, I have to use the Elvis Operator to safely consume the filterParentID value in the value attribute. Then, I use the isNull() function to determine whether or not the CFQueryParam tag yields a NULL value. It's not quite as pretty as the query before it; but, it's still doable.
I can't believe that I've been using MySQL for over a decade and this is literally the first time that it has occurred to me that I can use the Null-Safe equality operator within my CFQuery code in ColdFusion. Though, to be fair, I hardly ever use NULL values. And, not all contexts that involve NULL values will be able to leverage this technique. But, for this particular type of query, it works quite nicely.
ASIDE: Technically, I have used the MySQL
<=>operator before, when looking at how to keep prepared statements consistent even with dynamic parameterized queries in ColdFusion. But, that was a proof-of-concept - I've yet to use that type of technique in a production app.
Epilogue / SoapBox On NULL Values In SQL
Almost 15-years ago, I wrote about how little I enjoy using NULL values in SQL. And, in the many years of writing large, SQL-intensive ColdFusion applications since that post, I can report that nothing has changed. I still try to avoid NULL as much as possible, delegating it to Date/Time stamps and soft-delete techniques that leverage uniqueness constraints.
For the most part, I find that NULL values offer little more than academic ceremony; and tend to over-complicate queries (as demonstrated by this post). As such, I still encourage people to avoid NULL values whenever possible; and, instead, leverage values like 0 and "" in lieu of meaningful external references.
Want to use code from this post? Check out the license.
Reader Comments
Just out of interest, can you not just compare the zero:
So you could just do:
Assuming filterParentID is always an integer. If it isn't you could do something like:
I am pretty sure I have done this before in MySQL, but I can't say this with 100% certainty?
I am assuming your parentID column defaults to 0 rather than NULL
Or maybe I have missed something?
The only reason I am saying this, is because at work, we always default INT columns to 0
@Charles,
So, if I wrote the code, then yes, I could compare to Zero as the
parentIDcolumn would be an INT that defaults to0if it was the "root" folder. Unfortunately, theparentIDcolumn defaults toNULL, not0:( As such, attempting to compare it anything other thanNULLwould result in a potentially false negative. And, this is exactly why I try to use0instead ofNULLas a column default wherever humanly possible.Oh I see. Sorry. I missed that column default was NULL.
That's makes perfect sense.
Just out of interest, what is the advantage of setting an INT column default to NULL?
Our company sets all of these column defaults to 0, but I would like to know whether this is correct or not?
@Charles,
In my opinion, there is no advantage. I think people who care about being "academically correct" like to use this technique because they will argue that an "unknown" value is different than a "default" value. But, they can argue that until they are blue in the face - it doesn't give it any practical value.
Now, that's not to say that
NULLis bad - it definitely has it's use-cases, where you do need to differentiate an "unset" value (such as with adeletedAtdate), where aNULLpretty much has to be used.That's very interesting information. I will bear that in mind, in future.
I think I tend to use 0, because I am still not too confident about how to deal with NULL values, in general!
I mean the very definition of NULL is like an existential nightmare!!!
But, this tutorial has been very useful, for the future, in case I have a scenario, where I have to use a NULL column default.
Normally, I would just have cfif statements in the cfquery so this was my first time digging deeper into a nicer solution.
This solution did work for me. Thanks for that.
I just wished <cfqueryparam NULL="true".../> would just null if value is an empty string instead of having to provide the logic on our end. Just my opinion.
@Adrian, I tend to do this to varchar columns:
@Charles,
it's a good solution...I'm just saying it would be nice IMO, if that logic was built-in "under the hood" and activated with NULL="True|Yes" .
@Adrian,
It's hard to make the
cfqueryparamtoo smart as there's so much variety to how the underlying database schema is actually structured. I personally try to stay away fromNULLfields as much as possible and just use "sane defaults" instead (like empty string and zero). This way, we never have to worry about null-comparisons and the queries become much more simple.