Query of Queries -- Whitespace Is Important
Posted by
Brad Wood
Oct 09, 2008 09:25:00 UTC
I've been working on modifying the QueryParamScanner to have a new screen that will allow me to fix the queries and update the files automatically. I finally seem to have everything working and I have the code setup so I can edit the suggested changes to fix anything that the regular expression didn't guess correctly.
Last night I was running a final test and I noticed that I had lost all of the whitespace at the beginning and end of each query.I knew I hadn't set up a trim, and I knew the whitespace was still present when I submitted the form. I stepped through the process and found that when I used query of queries to control the order in which I was replacing my queries, my whitespace disappeared :(
Here's some code to demonstrate the phenomenon:
Value With Tabs and Spaces
Testing HTML
<html>
<head>
<title>White Space Test</title>
</head>
<body>
<div>White Space Test</div>
</body>
</html>
Value With Tabs and Spaces
Testing HTML
<html>
<head>
<title>White Space Test</title>
</head>
<body>
<div>White Space Test</div>
</body>
</html>
As you can see, the ColdFusion Query of Query is "helping" us by trimming all whitespace off the start and end of our strings. I have Googled and can find no reference of anyone else having this problem before. I am getting the same behavior with CF 8.0.1 Ent/Win and CF 7.0.2 Stan/Lin.
Here's some code to demonstrate the phenomenon:
[code]<cfset crlf = Chr(13) & Chr(10)>
<cfset tab = chr(09)>
<cfset testingwithwhitespace = crlf & tab & tab & "Value With Tabs and Spaces" & crlf & tab & tab & crlf & tab & tab & crlf>
<cfset testingwithoutwhitespace = "Value Without Tabs and Spaces">
<cfsavecontent variable="testingHTML">
<html>
<head>
<title>White Space Test</title>
</head>
<body>
<div>White Space Test</div>
</body>
</html>
</cfsavecontent>
<cfset qryTesting = QueryNew("Description,testvalue")>
<cfset rowid = queryAddRow(qryTesting)>
<cfset querySetCell(qryTesting,"Description","Testing String with whitespace",rowid)>
<cfset querySetCell(qryTesting,"testvalue",testingwithwhitespace,rowid)>
<cfset rowid = queryAddRow(qryTesting)>
<cfset querySetCell(qryTesting,"Description","Testing HTML",rowid)>
<cfset querySetCell(qryTesting,"testvalue",testingHTML,rowid)>
<h1 align="center">Before Query of Query</h1>
<cfoutput query="qryTesting">
#qryTesting.description#<hr />
<span style="background:yellow">#HTMLCodeFormat(qryTesting.testvalue)#</span><br /><br />
</cfoutput>
<cfquery dbtype="query" name="qofqAutoTrim">
SELECT Description,testvalue
FROM qryTesting
</cfquery>
<h1 align="center">After Query of Query</h1>
<cfoutput query="qofqAutoTrim">
#qofqAutoTrim.description#<hr />
<span style="background:yellow">#HTMLCodeFormat(qofqAutoTrim.testvalue)#</span><br /><br />
</cfoutput>
[/code]
Here is the output of the test code:
Before Query of Query
Testing String with whitespaceValue With Tabs and Spaces
Testing HTML
<html>
<head>
<title>White Space Test</title>
</head>
<body>
<div>White Space Test</div>
</body>
</html>
After Query of Query
Testing String with whitespaceValue With Tabs and Spaces
Testing HTML
<html>
<head>
<title>White Space Test</title>
</head>
<body>
<div>White Space Test</div>
</body>
</html>
As you can see, the ColdFusion Query of Query is "helping" us by trimming all whitespace off the start and end of our strings. I have Googled and can find no reference of anyone else having this problem before. I am getting the same behavior with CF 8.0.1 Ent/Win and CF 7.0.2 Stan/Lin.
Tags: ColdFusion
Raymond Camden
Interesting. I tried adding the column types in queryNew but it didn't help. Nor did an explicit cast in the q of q.
matt
I guess I understand it a bit. That is the same as in HTML is done to wss. I understand you're confused, but in most languages you initialise strings with some quoting system. Here it is more confusing since this is SET language, where variable is delimited by tags, and while there is no option to do <var>" string "</var> and recieve unquoted ' string ', it produces necessity to explicitely use quotes, what is bit confusing at all, but maybe lower evil than using quotes everytime, or typing some "NoTrim" attribute to declaration tag.
Brad Wood
@Matt: I think you many be confusing the example with the problem. The fact that at one point I used a cfsavecontent tag to capture my text and the fact I was using HTML as the text I captured is really totally arbitrary and is just the example I used.
The basic problem here is white space (tabs, spaces, and line breaks) as part of a string variable (and more specifically at the beginning and end of one) should be considered non-trivial and should be preserved. The query of query functionality in ColdFusion trims off that leading and trailing white space which is an unexpected behavior.
Samuel Williams
Curiously, in my case at least it seems that using SELECT * does preserve whitespace, but specifying column names causes it to be trimmed...
Bryan
I just found this problem myself. I had two very similar queries that would return the exact same results with one column difference. Dont ask me why, I didnt write the code, i was just debugging it. One of the entries had a space at the end of the string. when i tried to use this item from the query to find the item from the other query, I couldnt find it. The query was outputing the variable with the extra space at the end. but when trying to compare it to the other, almost identical query results, it couldnt find the value because the query of queries was ignoring that extra white space.
Joe R
Yep, I've just experienced the exact same problem. Adobe needs to fix this. It's kind of craptacular.
James Moberg
Adobe hasn't fixed this in CF8 yet... and today this was a "good thing". An SQL query returned from a third-party database used CHAR fields for everything and resulted in lots of extra whitespace. Instead of having to manually trim each field in the SQL query or loop through the data, I performed a Q-of-Q and all of the fields were automatically trimmed. I didn't try this on CF9 yet and wonder if it does the same thing.
Brett
They "fixed" this in CF 11. Found out the hard way. Upgraded from 9 to 11 and it bit us pretty hard in production. We were unintentionally relying on this behavior.