Query of Queries Trims Whitespace Automatically
After literally hours of tracking down what was causing a large code base to behave badly, I found an odd and frustrating features when doing a query of queries with <cfquery>. Take the following code as an example of what you would think works:
<cfquery name="q1" datasource="#application.DSN#">
SELECT ' A2010 ' as v1
FROM Seasons
WHERE teamid = <cfqueryparam value="18513" cfsqltype="cf_sql_integer">
</cfquery>
<cfquery name="q2" dbtype="query">
SELECT v1
FROM q1
WHERE LOWER(v1) = '#Lcase(' A2010 ')#'
</cfquery>
The above code will return 10 results for q1 but none for q2 even though they are both matching " 2010 ".
To fix the issue I tried using Changing the query to the following works:
<cfquery name="q1" datasource="#application.DSN#">
SELECT ' A2010 ' as v1
FROM Seasons
WHERE teamid = <cfqueryparam value="18513" cfsqltype="cf_sql_integer">
</cfquery>
<cfquery name="q2" dbtype="query">
SELECT v1
FROM q1
WHERE LOWER(v1) = '#Lcase('A2010')#'
</cfquery>
Notice I removed the space before and after the 2010 in the second query. This will return all 10 results even though it is not an exact match.
ColdFusion is automatically trimming the whitespace of the values returned from q1.
The only solution I can find is to trim the value to match as well. This will provide for an inexact match, but will work for my purpose. This is very concerning because you could have a lack of results and not even know it without inspecting it.
The final code I've come up with for a "sorta-fix-workaround" is:
<cfquery name="q1" datasource="#application.DSN#">
SELECT ' A2010 ' as v1
FROM Seasons
WHERE teamid = <cfqueryparam value="18513" cfsqltype="cf_sql_integer">
</cfquery>
<cfquery name="q2" dbtype="query">
SELECT v1
FROM q1
WHERE LOWER(v1) = '#Lcase(Trim(' A2010 '))#'
</cfquery>
