I’ve been trying to debug some discrepancies between script output on ColdFusion and Lucee. While debugging I noticed something which looks strange. When doing QoQ I’m not getting exact matched. I was querying for the string “2571” and I got results for “2571” and “02571”. When doing the reverse, searching for “02571” I got the same results.
the code below is using “queryNew” only for the example, when I noticed that I was querying a result form a DB query.
<cfset testQ = queryNew('test', 'CF_SQL_VARCHAR')>
<cfset queryAddRow(testQ)>
<cfset testQ['test'][1] = '2571'>
<cfset queryAddRow(testQ)>
<cfset testQ['test'][2] = '02571'>
<cfset queryAddRow(testQ)>
<cfset testQ['test'][3] = '1356'>
<cfset queryAddRow(testQ)>
<cfset testQ['test'][4] = '1659'>
<cfquery dbtype="query" name="res">
SELECT * from testQ
where test = '2571'
</cfquery>
<cfdump var="#res#">
<cfquery dbtype="query" name="res">
SELECT * from testQ
where test = '02571'
</cfquery>
<cfdump var="#res#">
I tried to search for some option that could be sanitising the query values, but I couldn’t find anything that could cause this behaviour.
Any ideas what could be causing that or if I can avoid it?
OS: using docker image “ortussolutions/commandbox:lucee5” Lucee Version: Lucee 5.3.8.189
A couple things here, I assume you mean “zeros” instead of “spaces”. Also, you said “trailing”, but you only showed “leading” zeros. I don’t think this holds true for trailing spaces.
Believe it or not, this is sort of working as designed, even though I totally agree it is unexpected in a SQL context. The reason is Lucee’s QoQ uses its own internal comparison logic that it uses when comparing any two variables in CMFL code. So consider this example, which dumps out true.
dump( '1234' == '01234' )
Since QoQ uses the same internal Lucee logic, it is also true for QoQ.
Now here’s the irony-- this is the Java code inside Lucee tha does the comparison.
Upstream is a whole bunch of methods that try to determine what types the variables are, and finally once this method gets called, the code has determined that two strings are involved. What’s the first thing it does? Tries to convert them to numbers!!
There isn’t a great answer to this in CFML as there is a lot of code that probably depends on this. Now, what we could do in QoQ is pass some sort of flag that tells the compare logic to not cast values, but I have a feeling that will also break stuff somewhere too.
Great question – because it doesn’t want to? I’m not entirely sure if this was a design decision on purpose for QoQ to have the same matching logic as CFML (many things QoQ does are idiomatic to CFML) or if it was just an oversight.
Yes, I’m sorry, I tested the trailing spaces but forgot to include them in the example. It is happening with trailing spaces too (but based on the java code you provided, I guess it is normal to happen for numeric string value with trailing spaces):
I’m not entirely sure if this was a design decision on purpose for QoQ to have the same matching logic as CFML (many things QoQ does are idiomatic to CFML) or if it was just an oversight.