Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Elliott Sprehn and Chris Phillips and Nathan Strutz and Anant Pradhan and Dave DeVol and Byron Raines

Insane ColdFusion Query Of Queries Madness (PLEASE HELP!)

By Ben Nadel on
Tags: ColdFusion

This is driving me bonkers and I cannot for the life of me figure out what is going on. Ok, quick background: I am working on an application that uses an APPLICATION-cached query titled "NoteTable". This just has some data fields to hold some simple message data. Everything goes fine until the data types of the ColdFusion query randomly change! This is totally screwing me over as I use the query Meta Data (new to CFMX 7) to convert the query to XML and back to a query.

After some debugging, I have narrowed it down to this chunk of code:

  • <cfmail
  • to="ben@xxxxxxxxxxxxxx.com"
  • from="ben2@xxxxxxxxxxxxxx.com"
  • subject="BEFORE - #REQUEST.Attributes.action#"
  • type="HTML">
  •  
  • <cfdump var="#GetMetaData( APPLICATION.NoteTable )#" />
  •  
  • </cfmail>
  •  
  •  
  • <!--- Store the results query directly into the result object. --->
  • <cfquery name="REQUEST.Response.Data" dbtype="query">
  • SELECT
  • id,
  • page_key,
  • description,
  • name,
  • email,
  • date_posted,
  • date_completed,
  • parent_id
  • FROM
  • APPLICATION.NoteTable
  • WHERE
  • id = <cfqueryparam
  • value="#REQUEST.Attributes.id#"
  • cfsqltype="CF_SQL_VARCHAR"
  • />
  • </cfquery>
  •  
  •  
  • <cfmail
  • to="ben@xxxxxxxxxxxxxx.com"
  • from="ben2@xxxxxxxxxxxxxx.com"
  • subject="AFTER - #REQUEST.Attributes.action#"
  • type="HTML">
  •  
  • <cfdump var="#GetMetaData( APPLICATION.NoteTable )#" />
  •  
  • </cfmail>

As you can see from the above code, I am CFMailing myself the query meta data of the APPLICATION.NoteTable query. Then I perform a ColdFusion query of queries on the table. Then I CFMail myself the meta data once more. Notice that I am at NO point updating the APPLICATION.NoteTable query in any way. I am just querying off of it.

Here are the two dumps I get in the mail:


 
 
 

 
ColdFusion Query of Query Madness (BEFORE)  
 
 
 

... and then the one after:


 
 
 

 
ColdFusion Query of Query Madness (AFTER)  
 
 
 

Suddenly, the data types changes. I am pulling my hair out over this. I am the only one using this system right now. I know that it is not some crazy thread situation where a different call is changing the database at the exactly same time this is going on. NOT possible.

What is the ColdFusion query of queries doing here? Someone please help me to understand!



Reader Comments

I agree with the post above. In my experience, it there is multiple rows in the QofQ, it takes the first row and "assumes" that this is the data type for the columns. As Aaron has stated, defining the column types will (at least should) take care of this situation.

Reply to this Comment

@Aaron, CJ,

That all would make sense if I was actually modifying the query from which I am pulling the Meta Data. However, I am not modifying the query at all. I don't care at all about the query returned from the query of queries... my concern is that the "from" table (APPLICATION.NoteTable) seems to be altered in some way while I have not made any modifications to it.

Bananas!

Reply to this Comment

I see what you are saying. Does the column type need defined in the cached Query? Just throwing something out there, if that is not it, I have no idea. That is really weird. Even if they were looking at the first row to get the metadatatype, they would both be looking at the same row. That is very interesting. Good Luck.

Reply to this Comment

woah. Didn't notice at first that you were dumping NotesTable the second time. That is crazy

Is the NotesTable query variable being created in code or from a live query? Does this happen with other queries as well?

Reply to this Comment

When I have a few minutes I am gonna grab the data types using Java to see if they come up differently.

Reply to this Comment

@Daniel,

The NoteTable query is being constructed manually. But I am passing in all the data types explicitly in the QueryNew( cols, types ) call.

Reply to this Comment

I ran into this issue a couple of years ago. The QoQ will assign the entire column the data type that is in the first row of that column. So, in you full query, both dates are type double. But in the in the QoQ, the first row values in the recordset may have one date value as empty or null, so it assigns the entire QoQ recordset column as null.

I ended up having to add in a dummy row that would always end up first no matter how it was ordered, and then ignore the dummy row in the code. It was an ugly hack but it worked.

Reply to this Comment

@Ben,

Be careful... BOTH CFMail are giving me the meta data from the parent table... the I am not utilizing the query of query in any way (nor does it affect the parent table). The data type should not change (since the first row of the parent query never changes).

Reply to this Comment

No worries. I LOVE query of queries... but 50% of the time, when I try to do anything complicated, they seem to act all crazy-bananas :)

Reply to this Comment

I would have to guess that the issue comes of passing the query by reference. That is, the Application scope is passing only a pointer to the NoteTable in memory. Clearly, running the QoQ on that structure is impacting it in such a way that using the Application.NoteTable after the fact is showing the change becuase the QoQ took action on that cached structure, in addition to creating a new structure (Request.Response.Data).

You may be able to force the correct datatypes ahead of time, with the queryNew(cols, types). Try this (might work, not sure):

<!--- Store the results query directly into the result object. --->
<cfset Request.Respeonse.Data = queryNew(
"id, page_key, description, name, email, date_posted, date_completed, parent_id",
"varchar, varchar, varchar, varchar, varchar, date, date, varchar"
)>
<cfquery name="REQUEST.Response.Data" dbtype="query">
SELECT
id,

Reply to this Comment

Ben -

Sorry, I didn't read and comprehend. *sheepish grin*

restart brainstorming: I'm assuming the code snippet you posted is *all* that's running, and there's nothing else that happens between the first and second CFMail other than the QoQ.

1) Can you take that out of the equation completely to verify that you get the same dump results *without* the QoQ in between?

2) next, try adding the QoQ code back in with only

SELECT * FROM APPLICATION.NoteTable

to rule out possible issue with the WHERE clause...

3) I'm also curious (although it doesn't really make sense) if you remove the Application scope from the equation entirely...?

It might just be that you have uncovered an obscure bug... but I'm on the edge of my seat to find out ;-)

Reply to this Comment

Jason may be on to something. What happens if you take your original app scoped query and do this:

<cfset copy1 = duplicate(the query)>
<cfset copy2 = duplicate(the query)>

Work with the copies and see if you still get the problem.

Reply to this Comment

@Aaron,

When I remove the query in between, the data type remains unchanged. So at least this definitely narrows it down to the query of query.

However, when I put the query back in, with SELECT * FROM APPLICATION.NoteTable... reverts back to messing up.

As far as removing the APPLICATION variable, I cannot do that (as ColdFusion does not search the APP scope automatically). I can however, try using a shorthand:

<cfset qTest = APPLICATION.NoteTable />

... then query off of that.

Unfortunately, NONE of those work (except actually removing the query of queries). Great debugging suggestions, though!

Reply to this Comment

QoQ definitely have its quirks. In one of my past project, we were doing QoQ on a result that will have CAS numbers (which can optionally be in the format XX-XX-XXXX). Now ColdFusion in its wisdom was converting the entire column to type Date if the first row in CAS number column is in date format. We end up writing a function that manually loops through the recordset and apply our filter.

Reply to this Comment

@Ray,

Just tried it and the original table, APPLICATION.NoteTable, remains unchanged. I also get the meta data from the duplicate query as well (just to see) and that meta data was modified. So, there's just something about the query of queries that messes with the column data types.

Now, I am gonna grab the data type using Java:

http://bennadel.com/index.cfm?dax=blog:308.view

... see if that data changes as well (I seem to remember the order of columns be stored differently, so maybe the entire data type representation is also stored differently).

ps. If you look at that entry, you showed me how to get the data types using GetMetaData() :) ... small world.

Reply to this Comment

@Qasim,

I ran into that issue not so long ago. Query of query is like a woman... can't live with it... can't live without it ;)

Reply to this Comment

@Ray,

I just tried getting the data types from the result set using Java. Those data types are altered as well. Looks like there is no getting around it.

For this particular application, I would rather not go Duplicate() as I am not sure how big the queries will get. I guess I will have to have an explicit list of data types, rather than using the Meta Data to figure out what they should be.

Thanks every one!

Reply to this Comment

Ben,

Just gotta ask ... if you do go ahead and list the types, does that actually solve the problem? I understand that you hoping to avoid having to specify, and I understand your desire to avoid Duplicate() for a structure that may grow pretty large. So the questions still remains, however, will the queryNew() with the type list even solve the problem? I was serious above when I only suggested it *might* work LOL.

Keep us posted!

Jason

Reply to this Comment

In summary, I think I'm correct in saying that the QoQ changes the data types for the columns in it?

Would it stand to reason, then, that CASTing them to the desired data types *within* the QoQ might be a work around for this bug?

(I wish I had CF 7 installed on this box, so I didn't have to keep guessing ;-)

Reply to this Comment

Are you using a Microsoft SQL database table to get you data? What if you (maybe you already do) would use Clustered Indexes on your table. The Query should pull the results back in the same order everytime. If the first row is always the same, then the result should be the same. Again this is just a thought. If the same row is returned everytime then I would think that the results woudl be consistance.

Reply to this Comment

This may be a little off topic but, does anyone think 85k for a mid level developer in NYC is the going rate? For a 35 hour work week?

Reply to this Comment

@Aaron,

Casting is not really going to help out here. I already perform a JavaCast() when I set the query values. But I am not concerned with any data returned in the query. I am only concerned with the meta data about the query.

Reply to this Comment

possible workaround:

<cfapplication name="qryTest">

<!--- Store the results query directly into the result object. --->
<!--- <cfset qryOriginalData = queryNew(
"id, page_key, description, name, email, date_posted, date_completed, parent_id",
"varchar, varchar, varchar, varchar, varchar, date, date, varchar"
)> --->

<!--- set up columns, but don't define data types; let CF guess --->
<cfset Application.qryOriginalData = queryNew(
"id, page_key, description, name, email, date_posted, date_completed, parent_id"
)>

<!--- Add rows. --->
<cfset QueryAddRow( Application.qryOriginalData, 3 ) />

<!---
Set row/cell data values.
--->
<cfset Application.qryOriginalData[ "id" ][ 1 ] = "111" />
<cfset Application.qryOriginalData[ "page_key" ][ 1 ] = "pagekey1" />
<cfset Application.qryOriginalData[ "description" ][ 1 ] = "description1" />
<cfset Application.qryOriginalData[ "name" ][ 1 ] = "name1" />
<cfset Application.qryOriginalData[ "email" ][ 1 ] = "email1" />
<cfset Application.qryOriginalData[ "date_posted" ][ 1 ] = "2007-01-11" />
<cfset Application.qryOriginalData[ "date_completed" ][ 1 ] = "2007-02-11" />
<cfset Application.qryOriginalData[ "parent_id" ][ 1 ] = "parent_id1" />

<cfset Application.qryOriginalData[ "id" ][ 2 ] = "222" />
<cfset Application.qryOriginalData[ "page_key" ][ 2 ] = "pagekey2" />
<cfset Application.qryOriginalData[ "description" ][ 2 ] = "description2" />
<cfset Application.qryOriginalData[ "name" ][ 2 ] = "name2" />
<cfset Application.qryOriginalData[ "email" ][ 2 ] = "email2" />
<cfset Application.qryOriginalData[ "date_posted" ][ 2 ] = "2007-01-12" />
<cfset Application.qryOriginalData[ "date_completed" ][ 2 ] = "2007-02-12" />
<cfset Application.qryOriginalData[ "parent_id" ][ 2 ] = "parent_id2" />

<cfset Application.qryOriginalData[ "id" ][ 3 ] = "333" />
<cfset Application.qryOriginalData[ "page_key" ][ 3 ] = "pagekey3" />
<cfset Application.qryOriginalData[ "description" ][ 3 ] = "description3" />
<cfset Application.qryOriginalData[ "name" ][ 3 ] = "name3" />
<cfset Application.qryOriginalData[ "email" ][ 3 ] = "email3" />
<cfset Application.qryOriginalData[ "date_posted" ][ 3 ] = "2007-01-13" />
<cfset Application.qryOriginalData[ "date_completed" ][ 3 ] = "2007-02-13" />
<cfset Application.qryOriginalData[ "parent_id" ][ 3 ] = "parent_id3" />

<!--- not necessary, but let's just see how the data looks --->
<cfdump var="#Application.qryOriginalData#" label="Application.qryOriginalData">
<br><br>
<!--- notice how CF doesn't know the data types at this point --->
<cfdump var="#GetMetaData( Application.qryOriginalData )#" label="BEFORE: GetMetaData( Application.qryOriginalData )" />
<br><br>

<!--- here for some reason, simply by running this QoQ, the Application.qryOriginalData query's meta data
properties change; this must be a bug! BUT as a workaround, knowing that whatever we CAST the data types
to here actually affects the original query object, we simply take advantage of this knowledge --->
<cfquery name="qryTemp" dbtype="query">
SELECT
CAST ( id AS VARCHAR ) AS id,
page_key,
description,
name,
email,
CAST ( date_posted AS DOUBLE ) AS date_posted,
date_completed,
parent_id
FROM
Application.qryOriginalData
</cfquery>

<!--- now notice that the meta data for the original query has changed... (it has data types) --->
<cfdump var="#GetMetaData( Application.qryOriginalData )#" label="AFTER qryTemp: GetMetaData( Application.qryOriginalData )" />
<br><br>
<!--- ...but since we explicitly set the data types during the QoQ (using CAST), the meta data can be whatever we want --->
<cfdump var="#GetMetaData( qryTemp )#" label="GetMetaData( qryTemp )" />
<br><br>

<!--- now reset it so that data types of qryOriginalData are back to what we want --->
<cflock scope="Application" type="exclusive" timeout="5">
<cfset Application.qryOriginalData = qryTemp>
</cflock>

<!--- whippee! with some extra work, we retain the meta data --->
<cfdump var="#GetMetaData( Application.qryOriginalData )#" label="RESET: GetMetaData( Application.qryOriginalData )" />
<br><br>

<!--- now run whatever queries you want against the temp query, *without* affecting the qryOriginalData --->
<cfquery name="REQUEST.Response.Data" dbtype="query">
SELECT
id,
page_key,
description,
name,
email,
date_posted,
date_completed,
parent_id
FROM
qryTemp
WHERE
id = <cfqueryparam value="111" cfsqltype="CF_SQL_VARCHAR" />
</cfquery>

<!--- obviously it shouldn't change the meta data again here, and it DOESN'T - woo hoo! --->
<cfdump var="#GetMetaData( Application.qryOriginalData )#" label="AFTER REQUEST.Response.Data: GetMetaData( Application.qryOriginalData )" />
<br><br>

Reply to this Comment

@Aaron,

Fascinating that CAST() works within QoQ ... very good to know, and an excellent path around this data-typing issue. Nice model for the workaround.

Jason

Reply to this Comment

I have encountered a somewhat related problem where my Q of Q was summing and grouping a decimal column, which would occasionally present itself in scientific format.

After spending the better part of the day on it, the best solution I could come up with was to iterate over the original results, copying each row into a new resultset, setting the decimal values as follows:

  • QuerySetCell(qryDuplicate, 'MyDecimalColumn', PrecisionEvaluate("#qryOriginal.MyDecimalColumn# + 0.00"))

Interestingly I had to add 0.00 otherwise it didnt work.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.