tl:dr; - use javacast("null", "") or get a ColdFusion null some other way and you'll be fine

I've been on a bit of a refactoring & testing kick lately thanks to a few recent episodes of the Working Code podcast (specifically episodes 22 & 23). I recently set out to refactor a common function in the CMS platform we have built at work and realized that I need to mock a query that I can use in my test suites. We're using TestBox, so I have MockBox available, but querySim doesn't handle making a column null. So, what's a guy to do? Go to Slack and ask, naturally!

I got a reply from an outspoken chap from Europe whose name you've probably come across if you've spent any amount of time in the CFML world. He was kind enough to hit me with this snippet (the last 2 lines are mine):

records = queryNew(
    "id,mi,en",
    "integer,varchar,varchar",
    [
        [1, null(), "one"],
        [2, "rua", null()],
        [null(), "toru", "three"],
        [4, "wha", "four"]
    ]
);
writeDump(records)

function null(){
    return;
}

writeOutput("value: [#records.en[2]#] isNull: [#isNull(records.en[2])#]");
writeOutput("<br/>");
writeOutput("isNull(null()): " & isNull(null()));

This example shows that returning an empty result from the null() function – a proper ColdFusion null as evidenced by the last line of output – and giving that to queryNew doesn't come back out as a null if you try to grab it off the query.

OK, so does the fact that it's not a true null make any bit of difference for what I'm trying to do? As a reminder, I'm trying to make a query that I can feed to a function that runs a Query of Queries (QoQ – suppress your groans, please) and that QoQ has a WHERE column IS NULL check in it. So long as I can pacify the QoQ, I'll be off to the next problem. So, let's check...

I'll add the following code to the snippet above and see what the result is.

<cfquery dbtype="query" name="filtered">
    SELECT *
    FROM records
    WHERE mi IS NULL
</cfquery>

<cfdump var="#filtered#" label="filtered results" />

Well, it... works? My definition of "works" here is incredibly narrow and only accounts for the one situation with which I'm concerned. I don't like the idea of having to add a null() function to a bunch of our tests, so I did a quick check and confirmed that using javacast("null", "") instead of null() when creating the array for the data used by queryNew functions in the same way.

Another observation I've made is that if you're lucky enough to have the column you want to be null as the last column, you can pass fewer data points than columns and it works itself out. Here's a separate but similar example showing some records where one individual hasn't logged in and thus has no value for the lastLogin column.

records = queryNew(
    "id,name,lastLogin",
    "integer,varchar,timestamp",
    [
        [1, "David", "2021-06-09 08:15:36"],
        [2, "Jennifer", "2021-05-24 16:15:42"],
        [3, "Elizabeth", "2019-12-04 19:49:14"],
        [4, "Ronald"]
    ]
);

In this example, the lastLogin value for "Ronald" would be null enough for QoQ to use WHERE lastLogin IS NULL and find the record.

If you'd like to play around with this, here's a link to the code on trycf.com.