Insane ColdFusion Query Of Queries Madness (PLEASE HELP!)

Funny: you have an earlier post that seems to answer this (at least indirectly). It seems CF takes a "guess" as to the data type when run through QoQ, which isn't always right. So, if you need to maintain a particular data type, you have to CAST those columns to the desired type:

www.bennadel.com/blog/379-ColdFusion-Query-of-Queries-Unexpected-Data-Type-Conversion.htm

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.

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.

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?

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.

Sorry, my bad. It just sounded so similar to the issue I mentioned.

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,

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 ;-)

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.

Did you try a QofQ on a duplicate of the query?

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.

Don't forget to log a bug at www.adobe.com/go/wish.

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

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 ;-)

Please help to decide this problem, its decision I need too!

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.

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?

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>

@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

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.