Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with: James Spoonmore
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with: James Spoonmore@grapestack )

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

By Ben Nadel on
Tags: ColdFusion

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.



Reader 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 ;)

Reply to this Comment

@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

Reply to this Comment

@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

Reply to this Comment

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

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.