Skip to main content
Ben Nadel at Dan Wilson's 2011 (North Carolina) with: Nick James Wilson
Ben Nadel at Dan Wilson's 2011 (North Carolina) with: Nick James Wilson

CFQueryParam "Fails Silently" With Empty Lists And IN Clauses In Lucee 5.2.9.40

By
Published in Comments (6)

About a year ago, we converted our production app from Adobe ColdFusion to Lucee CFML. The conversion itself took several months (depending on how hard you squint); and, since the conversion, we've occasionally stumbled over a few inconsistencies between Adobe ColdFusion and Lucee CFML. Yesterday, I fixed an inconsistency in the way that the cfqueryparam tag was handling empty lists when used with an IN() clause in MySQL. Furthermore, even within Lucee CFML, it appears that an empty list is handled differently between the cfquery tag an the queryExecute() function.

To understand what I mean, let's look at a simplified demo query. In the following CFML and SQL code, I'm attempting to use an empty list of IDs in a NOT IN() clause:

<cffunction name="test">

	<cfquery name="local.results">
		SELECT
			p.id,
			p.name
		FROM
			project p
		WHERE
			p.id = <cfqueryparam value="3" sqltype="integer" />
		AND
			-- Trying to use an empty collection with IN() clause.
			p.id NOT IN ( <cfqueryparam value="" sqltype="integer" list="true" /> )
		;
	</cfquery>

	<cfreturn results />

</cffunction>

<cfdump var="#test()#" />

As you can see, the above cfquery is attempting to include a condition that looks like this:

p.id NOT IN( ? )

... where the given query parameter is an empty list.

Now, personally, I believe the SQL statement itself is buggy. Meaning, that the WHERE condition should have been omitted, by the developer, with a <cfif> block if the value was empty. And, in fact, that's how I fixed the bug:

<cffunction name="test">

	<cfquery name="local.results">
		SELECT
			p.id,
			p.name
		FROM
			project p
		WHERE
			p.id = <cfqueryparam value="3" sqltype="integer" />

		<!--- Only include condition if we have a value-list (which we don't). --->
		<cfif "".len()>
			
			AND
				-- Trying to use an empty collection with IN() clause.
				p.id NOT IN ( <cfqueryparam value="" sqltype="integer" list="true" /> )

		</cfif>
		;
	</cfquery>

	<cfreturn results />

</cffunction>

<cfdump var="#test()#" />

But, the curious thing is that the original query doesn't throw an error - it just returns zero records:

CFQueryParam with empty list returns zero records in Lucee CFML 5.2.9.40.

I am guessing (although I haven't tested this) that Adobe ColdFusion essentially nullifies this condition, returning the intended record set as if the IN() clause wasn't executed.

What's even stranger about this whole thing is that the queryExecute() function does throw an error when attempting to use an empty IN() condition:

<cfscript>

	results = queryExecute(
		sql = "
			SELECT
				p.id,
				p.name
			FROM
				project p
			WHERE
				p.id = :id
			AND
				p.id NOT IN ( :notIDs )
			;
		",
		params = {
			id: {
				value: 3,
				type: "integer"
			},
			notIDs: {
				value: "", // Trying to use an empty collection with IN() clause.
				type: "integer",
				list: true
			}
		}
	);

	dump( results );

</cfscript>

As you can see, we're trying to use the same NOT IN() clause. Only this time, instead of returning an empty recordset, Lucee CFML throws an error:

queryExecute() with empty list throws an error in Lucee CFML 5.2.9.40.

Though, to be fair, it looks to be a CFML error, not a SQL error.

After some Googling, it appears that this inconsistency is a known issue and is slated to be fixed in future releases of Lucee CFML:

ASIDE: Changing this would be a "breaking change" to Lucee CFML code. So, it will be curious to see if this is handled as a "major" bump, with regard to semantic versioning; or, if this is a "minor" bump for a bug fix. Semantic versioning is fun :D

Anyway, just documenting my Lucee CFML journey. I better go through the code and sanity-check all the existing list="true" query parameters and make sure there's aren't any other subtle failures that none of the users have complained about yet.

Want to use code from this post? Check out the license.

Reader Comments

9 Comments

At the very least we should throw a better error message, but we will review the compatibility issue and try to find the best way to resolve this.

And yes, Semantic Versioning is both Fun and Important ;)

15,811 Comments

@Igal,

To be fair, there are already tickets open, so don't feel undo pressure simply because I ran into an issue. I only write this stuff to document my experience.

That said, I am surprised that this works in either Adobe ColdFusion or Lucee CFML (albeit with different outcomes). My gut tells me that this should have thrown a syntax error for SQL... essentially trying to pass nothing into an IN() clause.

But, in one of the tickets, Micha talks about this being a MySQL Driver concern and not a Lucee concern. It's a bit of a blackbox to me, so.....

Unless you were specifically talking about the queryExecute() :D Then, yeah, maybe just a better error message :P

9 Comments

@Ben,

No pressure at all. We prioritize and work on tickets on a regular basis. Some of the factors we take into account include:

  • How many users are affected by this issue?
  • How difficult is it to solve?

Sometimes it's a matter of bringing a specific ticket to our attention.

I will definitely check Micha's comments first.

Igal

9 Comments

I've looked into this issue today. The discrepancy between Lucee and ACF is that Lucee passes NULL for the empty list's value while ACF passes an empty string.

In SQL NULL never matches anything, not even other NULLs, so both IN (NULL) and NOT IN (NULL) return no results, while in ACF NOT IN ('') returns all of the results that are not an empty string.

The discrepancy between cfquery and queryExecute() in Lucee is due to the fact that Lucee handles them differently (obviously). Since passing NULL for the empty list always yields no results, I want to fail fast the error as soon as the issue is detected. The right place to improve the error message would affect both constructs, meaning that it will break the currently (not) working cfquery with an error.

We will have to therefore discuss this further before deciding on how to best handle it.

Please feel free to join the discussions in tickets
https://luceeserver.atlassian.net/browse/LDEV-2343
https://luceeserver.atlassian.net/browse/LDEV-1671
https://luceeserver.atlassian.net/browse/LDEV-2298

15,811 Comments

@Igal,

Excellent follow-up. I don't know how strongly I feel about the discrepancy. Frankly, I feel like using an IN() clause with no arguments is a bug on behalf of the developer (since you can't write such a clause in raw SQL). That said, I'll take a look at the tickets. Thanks!

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel