(not so) massive problem with Query of Query in ColdFusion 9.0.1

Sorry for this... I've had a closer look at all the code and calmed down a bit and realized actually that the core CacheBox framework service had already been rewritten (years ago) to store content in a structure. So this problem turned out to be isolated to the agent CFC and only if the service wasn't installed. So updating the code for the agent to work the same way as the service seems to have resolved the issue.

I'm going to try not to be so jumpy from now on. ;P

----------

OH CRAP! THIS IS A GIANT PROBLEM!

ColdFusion 9.0.1 changed the behavior of Query of Queries and appears to have eliminated the ability to select from ANY query containing non-simple values... like we did just fine in ColdFusion 8.

Here's some code...

<cfset test = QueryNew("") />
<cfset QueryAddColumn(test,"columna",['a','b','c']) />
<cfset QueryAddColumn(test,"columnb",[1,2,3]) />
<cfdump var="#test#" />

<cfquery name="test2" dbtype="query">
   select * from test
   where columna = <cfqueryparam value="b" cfsqltype="cf_sql_varchar">
</cfquery>
<cfdump var="#test2#" />

This works fine on both CF8 and CF9.

Now we make one small tweak and add a complex value in columnb and it continues to work on CF8, but fails completely on CF9 with the message "complex objects cannot be converted to simple values".

<cfset myStruct = {a=1, b=2} />

<cfset test = QueryNew("") />
<cfset QueryAddColumn(test,"columna",['a','b','c']) />
<cfset QueryAddColumn(test,"columnb",[1,myStruct,3]) />
<cfdump var="#test#" />

<cfquery name="test2" dbtype="query">
   select * from test
   where columna = <cfqueryparam value="b" cfsqltype="cf_sql_varchar">
</cfquery>
<cfdump var="#test2#" />

Building the query isn't the problem -- that works fine in both cases -- there's nothing wrong with HAVING complex values in queries... and in CF8 as long as you didn't try to perform comparison operations against them, you could use a query-of-query to select those rows.

But on CF9, the presence of any non-simple value (it seems to be anywhere) in a query-of-query causes it to fail completely... even if I'm not selecting the rows where the structure is located, it still fails. And WHY WOULD IT? What's special about selecting a value in columnA that FORCES it to check the entire columnB column to make sure that every value is simple?! Doesn't that even run contrary to the logic that the query-of-query shouldn't constraint columnB unless it needs to?

This is HUGE because the entire CacheBox framework completely DEPENDS on query-of-query functionality that we had in CF8 and appear to have lost in CF9!

Damnit! I have no idea how to move forward from here...

If you've got a non-updated copy of CF9 (before the 9.0.1 update) it would be nice if you could run this code and see if it works there. I'd like to know if this happened in 9 or 9.0.1. Thanks.

Comments
Adam Tuttle's Gravatar I'll consider this post incomplete until you amend it with a link to the bug you've filed. :P s://bugbase.adobe.com/
# Posted By Adam Tuttle | 4/23/12 9:55 AM
Ike's Gravatar @Adam Thanks for the reminder! ;) It turns out I was a bit knee-jerk in posting this comment... it seems that the core CacheBox framework had already been rewritten (several years ago now) to store the content in a struct (for faster retrieval on reads) and use the query only for flat values that allows it to speed up statistics reporting and evictions (where a QoQ and query loop is faster than looping over the structure). The end result is that it was only failing in the agent CFC when the cachebox framework / service wasn't installed. So after updating the agent CFC to match that technique, it appears to be working properly.

I suppose I could still file a bug report... although I don't personally need it.

But all day I've been thinking about posting another blog apologizing for being so jumpy the past few days. ;P
# Posted By Ike | 4/23/12 1:56 PM
Adam Tuttle's Gravatar No sweat man. Everyone jerks a knee now and then.
# Posted By Adam Tuttle | 4/23/12 2:09 PM
Ike's Gravatar Thanks, Adam! :)
# Posted By Ike | 4/23/12 8:02 PM
Clive's Gravatar File a bug.
# Posted By Clive | 12/12/12 10:45 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.5.006. | Protected by Akismet | Blog with WordPress