About two months ago I blogged about an interesting Query of Query bug. The bug involved a QoQ that changed the sort order of the original data. This week a user sent in another example of a QoQ causing changes to the original query. Consider the following query:
<cfset mydata = queryNew("mydate, rannum")>
<cfloop index="loop1" from="1" to="5">
<cfset newrow = queryaddrow(mydata, 1)>
<cfset temp = querysetcell(mydata, "mydate", #dateformat(now()-loop1,"dd-mmm-yy")#, #loop1#)>
<cfset temp = querysetcell(mydata, "rannum", 55.65, #loop1#)>
</cfloop>
The query contains two columns, mydate and rannum. (I just now noticed the space in his list of columns. ColdFusion appears to auto-trim it. I'd remove that from the code however.) Dumping the query shows:
Now consider this simple QofQ:
<cfquery dbtype="query" name="query4graph">
select mydate, rannum from mydata
</cfquery>
After running this, a new dump of mydata shows:
The dates in the original query have now been converted into full date/time stamps. This comes from the fact that ColdFusion noticed they were dates and treated them as such. I think that makes sense, but it still bugs me that it changes the original query.
You can kind of fix it by forcing a type on the queryNew:
<cfset mydata = queryNew("mydate, rannum","varchar,double")>
Of course, that's kinda sucky as well. I bet if you sort on it you will get inconsistent results. The best fix then would be to duplicate the query before you run the QofQ on it. That way you can go ahead and let ColdFusion convert the column to dates and still get proper sorting.
Oh - and in case you were wondering - the formats of the date in the query were pretty important. He was feeding the query to a chart and needed it to show up correctly.
Archived Comments
I would actually consider that expected behavior more than a bug. As you said, "This comes from the fact that ColdFusion noticed they were dates and treated them as such. I think that makes sense..."
As for the chart formatting, I haven't used cfchart in a long time. Seems like the issue is how to get a date field from a database into cfchart in a specific format.
Did it really change the query though? You applied a date (now()-loop1) to the column, you just formatted it (Using a 'date' method at that, dateformat(). Wouldn't the use of that function technically convert to a date object [if possible] if it weren't already?)
I do expect CF to notice the date-like formats and consider the data as dates. But it should not change the original query. A QofQ should not change the original query in any way. It should just read it - nothing more.
@Cutter: If I output the query and it shows X, and then Y, then to me that is a change. Period. :) Do you not agree?
I would almost say that it is a bug that doing the dump of the original query does not format it into the {ts} date object. It seems CF should recognize the date at that point. Obviously running the QofQ forces the recognition. I just tried a normal cfoutput query and can output the date in other formats (but if left alone it outputs as originally input).
@Matt - Wait - are you serious? If I make a variable with the value "4-8-1973", I don't care if it is a valid date or not, CF should NOT format it as a date unless I specifically run dateFormat on it. That's INSANE MAN! (Sorry to yell, but I'm in awe here and want to start a rumble. ;) It is a good thing CF is typeless, and it is a good thing that CF can _treat_ a string as a date, but CF shouldn't reformat data w/o me asking it to.
QoQ will also "helpfully" convert strings to numbers:
<cfset myQuery = QueryNew("agent,name") />
<cfset QueryAddRow(myQuery, 1) />
<cfset QuerySetCell(myQuery, "agent", "007") />
<cfset QuerySetCell(myQuery, "name", "James Bond") />
<cfset QueryAddRow(myQuery, 1) />
<cfset QuerySetCell(myQuery, "agent", "86") />
<cfset QuerySetCell(myQuery, "name", "Maxwell Smart") />
<cfdump var="#myQuery#">
<cfquery dbtype="query" name="queryAgents">
select agent, name from myQuery
</cfquery>
<cfdump var="#queryAgents#">
@Ray - I see your point and, no, CF shouldn't run willy nilly with variables like that. But at some point we expect the typeless behavior to take over. So who is to say when that should happen? I guess when dumping a query, I expect to see the data in its raw format, such as the {ts} format. That is what I always get from a normal query from a DB.
I guess I would prefer that QofQ translates that to a date object so it could keep the sorting I would expect (year-month-day, not alphabetical). If the QofQ did not do that translation, as you mentioned, a potential Order By would get wacky.
Oh, and no sweat over the yelling. Nothing like a good Friday rumble distraction. :)
@Joel - Sounds consistent. That would be a case where defining the column type in the QueryNew would be even more necessary.
I would definitely consider this a bug as CF should not making any changes to data types. Several years ago we faced a similar situation where CAS Registry Numbers (which can be in this format XX-XX-XX) were converted by ColdFusion to dates if we ran a Query of Query. The database had the field in VarChar2 format.
@Matt: But we aren't disagreeing. I do think QoQ should translate it to a date object. But it should do this on a COPY of the query. Not the original query.
@Matt
Yes, defining the column types fixes the problem.
That feature was introduced several CF versions ago (I forget offhand which). Before that it was a real problem, now it's just something to look out for.
@Ray
I finally see what you're getting at. Didn't read so closely. When I went back and cfdumped my original 'myQuery', it, too, had changed, not just the QoQ version. I agree that this isn't good.
Wow, like Joel it took me a moment to see that it is changing the original query (mydata) and not just your new QofQ (query4graph). I would definetly call that a bug. I could see it changing your QofQ because you did not explicitly set the column type, but man that is definitely an issue.
For what it is worth, my guess is that in order for QofQ to work it has to strip everything down to basic SQL query language and it does that before it renderers the html. Try using DateFormat in the RDS Query Builder...ahhhh, try again. CF is smart enought to know it has to remove the formatting before it can perform the QofQ. While not the desired effect, it does make sense. The "obvious" solution would be the automatic creation (in memory) of temp tables (e.g. mydata_temp) that represent all the data tables/views useds in all the QofQs. I am glad I am not the CF engineer that has to figure out how to make that happen.