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:

ColdFusion Query of Queries Unexpected Data Type Conversion

By Ben Nadel on
Tags: ColdFusion

I cam across a really strange ColdFusion query of query error this morning involving some unexpected data type conversion. The query of query seems to be ignoring BOTH the data type passed in as well as the target field's data type and converts automatically to a date. Let me demonstrate.

First, let me build a simple query using ColdFusion's QueryNew() method:

  • <!---
  • Create new query. Set up column data types
  • to both be VARCHAR.
  • --->
  • <cfset qGirl = QueryNew(
  • "name, date",
  • "VARCHAR, VARCHAR"
  • ) />
  •  
  •  
  • <!--- Add rows. --->
  • <cfset QueryAddRow( qGirl, 3 ) />
  •  
  •  
  • <!---
  • Set row/cell data values. Use JavaCast() to make
  • sure ColdFusion doesn't have to guess at data types.
  • --->
  • <cfset qGirl[ "name" ][ 1 ] = JavaCast( "string", "Libby" ) />
  • <cfset qGirl[ "date" ][ 1 ] = JavaCast( "string", "2006-10-01" ) />
  •  
  • <cfset qGirl[ "name" ][ 2 ] = JavaCast( "string", "Sarah" ) />
  • <cfset qGirl[ "date" ][ 2 ] = JavaCast( "string", "2006-09-01" ) />
  •  
  • <cfset qGirl[ "name" ][ 3 ] = JavaCast( "string", "Cindy" ) />
  • <cfset qGirl[ "date" ][ 3 ] = JavaCast( "string", "2006-08-01" ) />

At this point, I have a 3-row query that has two columns, both of which are VARCHAR fields. When I set the data into the cells, I do so using ColdFusion's JavaCast() method. This takes out any guessing that ColdFusion has to do. We are explicitly telling ColdFusion to use STRING values in the underlying Java record set.

Nothing magical yet. But now, let's try to perform a ColdFusion query of queries getting the first girl based on the date column (NOTE: the date column is a VARCHAR field with String data):

  • <!--- Get first girl. --->
  • <cfquery name="qFirstGirl" dbtype="query">
  • SELECT
  • name,
  • [date]
  • FROM
  • qGirl
  • WHERE
  • [date] =
  • <cfqueryparam
  • value="#qGirl.date#"
  • cfsqltype="CF_SQL_VARCHAR"
  • />
  • </cfquery>

Notice that I am not touching any data myself. I am only passing in the data that is already stored in the query (via qGirl.date). This should return one record containing the first girl in the original query, but, in fact, it returns an empty record set.

So what's the problem. Well, I thought maybe it was JavaCast() call. Even though that is a best practice, I thought maybe it was confusing the record set. So I removed. This did not fix the error. Then, I thought maybe it was the CFQueryParam tag - maybe there was something about it that was messing up the data. So I tried removing it and putting in a literal string:

  • <!--- Get first girl. --->
  • <cfquery name="qFirstGirl" dbtype="query">
  • SELECT
  • name,
  • [date]
  • FROM
  • qGirl
  • WHERE
  • [date] = '#qGirl.date#'
  • </cfquery>

This also did NOT fix it. So, what the heck was going on?!? As a last ditch effort, I tried echoing both the original data field and the passed in data to see what was going on:

  • <!--- Echo back data values. --->
  • <cfquery name="qEcho" dbtype="query">
  • SELECT
  • [date],
  • (
  • <cfqueryparam
  • value="#qGirl.date#"
  • cfsqltype="CF_SQL_VARCHAR"
  • />
  • ) AS target_date
  • FROM
  • qGirl
  • </cfquery>

Notice that I am selecting the existing data (in the qGirl.date column) and creating a calculated column based on the CFQueryParam of the first records date value. This gives me the following CFDump:


 
 
 

 
 
 
 
 

Very interesting! It seems the ColdFusion query of query is totally ignoring the data type that the CFQueryParam is requesting. But, while I can't see it visually, it must also be ignoring the [date] data type as well (otherwise we would be getting a match). It seems to automatically convert the value to a Date/Time stamp just because it "looks" like date/time object.

To test this, I tried passing in data that did not look exactly like a date:

  • <!--- Echo back data values. --->
  • <cfquery name="qEcho" dbtype="query">
  • SELECT
  • [date],
  • (
  • <cfqueryparam
  • value="-#qGirl.date#-"
  • cfsqltype="CF_SQL_VARCHAR"
  • />
  • ) AS target_date
  • FROM
  • qGirl
  • </cfquery>

Notice that the value attribute of the CFQueryParam tag now has "-" in it. This gives us the following CFDump:


 
 
 

 
 
 
 
 

Interesting! Why is it doing this? Why is it ignoring my requests and converting data on it own?

To get around this, I tried to cast the passed in CFQueryParam value to a VARCHAR. But that DIDN'T WORK. What is going on! Finally, I got the whole thing to work if I CAST both values to date:

  • <!--- Get first girl. --->
  • <cfquery name="qFirstGirl" dbtype="query">
  • SELECT
  • name,
  • [date]
  • FROM
  • qGirl
  • WHERE
  • CAST( [date] AS DATE ) =
  • CAST(
  • <cfqueryparam
  • value="#qGirl.date#"
  • cfsqltype="CF_SQL_VARCHAR"
  • />
  • AS DATE
  • )
  • </cfquery>

This finally gives us the desired output:


 
 
 

 
 
 
 
 

This seems like a really whacky behavior.




Reader Comments

QofQ has some nice uses and it makes certain things easier, but it is also probably the source of more unexpected behavior and frustration than any other feature.

Weird. But none of this surprises me of QoQ, really: it really doesn't take much effort to confuse it.

Have you raised it as a bug?

Good investigation, btw.

--
Adam

Bug has been reported.

Yeah, ColdFusion query of queries does have some strange things in it, but gosh darnit, it's one of the most bad-ass features of the ColdFusion MX family.

If it worked properly, you'd be right. However it's so flaky that it's really only useful for doing the most basic things. It feels like it's only a beta (which is sad, given it's been around since... when did CF5 come out... 2001?).

So I agree than the words "bad" and "ass" (although I'd say "arse") are suitable to describe it. Just not quite the way you meant ;-)

--
Adam

PS: much prefer your "De-spam" approach to all that "captcha" nonsense that's the current trend.

This may sound really trite... but did you try changing the query equivalency?

from

[date] = '#qGirl.date#'

to

[date] like #qGirl.date#

?

It's just a thought, because date types are not like other data types. They are weird objects, so it's possible that using the = operator doesn't work with them.

This is just what I needed! I retrieved a query and modified it using QueryAddColumn:
<cfset ArraySet(MyArray,1,themembers.recordcount,JavaCast("boolean",true))>
<cfset QueryAddColumn(themembers,"Signer","bit",MyArray)>

Notice that I used JavaCast AND set the column type in QueryAddColumn. That wasn't enough. To pull data out I had to do:

<cfquery dbtype="QUERY" name="thequery">
Select * from this.members
where cast(signer as bit) = cast(<cfqueryparam value="true" cfsqltype="cf_sql_bit"> as bit)
</cfquery>

Otherwise it kept telling my column didn't exist.

@Steve,

It's probably because you were using SELECT *. If you took out the * and wrote in the column names, it probably would work fine without the casting. It's just caching the query structure (my guess).

I just stumbled upon this entry after a google search. I was having a single digit integer (which was stored on the database as a varchar) being called a double in a QoQ.

I tried pblecha's suggestion to use a like and unfortunately that did not work. However, I then tried to CAST as VARCHAR and that did work.

Here is the original query:

SELECT frmtm, totm
FROM ProfileStruct.DOWControls
WHERE dow = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DayValue#">
AND seq = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="2">;

And I changed it to:

SELECT frmtm, totm
FROM ProfileStruct.DOWControls
WHERE CAST(dow AS VARCHAR) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DayValue#">
AND CAST(seq AS VARCHAR) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="2">;

I was actually having problems with both conditions of the predicate and now they work fine with CAST. For the record, all the fields are stored as VARCHAR2 on an Oracle database. dow is a 3 character representation of the weekday (MON, TUE, WED, ...) and seq is either 1 or 2.

It just shows that there is still a bunch of flakiness with CF7's QoQ.

@Frank,

Yeah, I would like going forward for QoQ to enforce more appropriate data types. The problem with that, however, is that it is currently quite flexible (you can put *anything* into a query). Forcing more data constraints might break a lot of code. We'll see. At least this error only pops up from time to time.

I wish there was a way to change the column data types on the fly. I've been playing around with jQuery AJAX and CFWDDX and I've noticed that when I pass a query object as a WDDX packet and re-construct it using CFWDDX the data types can get all messed up -- specifically when dealing with dates. They get even more messed up if the first row of a query contains a NULL value.

@David,

Yeah, that would be cool. That's why I like converting query data to array/struct data, so I know how it's all going back and I can do any massaging that needs to be done.

I had the exact same problem with a regular query on a varchar data type. I used the CAST() function and it solved the problem, but it only errored when certain strings were passed in. E.G. 'mfrscan' worked fine, but 'mfrbillback' threw sql conversion error. Very strange?

@Charlie,

Yeah, that is strange - especially with values that are not too different. Query of queries is awesome, but can definitely be frustrating from time to time.

@Charlie

Query of Queries is like a 3 year old with ADHD. You should never leave it alone to make any decisions for you. I highly recommend using a combination of JavaCast (when building your QofQ manually) and Cast() in order to ensure the data matches your expectations.

@David,

Yeah, JavaCast() is awesome; but, I've even had *some* issues with that. Sometimes, ColdFusion just wants to ignore everything about your column and cast data!!

I noticed (with CF8) similar QofQ date bizareness, trying to test for date X being before or on now().

If I have a query "foo" and it cfdumps and the "dateX" col looks and smells like a date field, I'd try "SELECT * from foo where cast([dateX] as date) <= cast(<cfqueryparam cfsqltype="CF_SQL_DATE" value="#now()#"/> as date)" with no luck. But what worked? Doing the cast as integer. "SELECT * from foo where cast([dateX] as integer) <= cast(<cfqueryparam cfsqltype="CF_SQL_DATE" value="#now()#"/> as integer)" got me the correct results

Yeah, CF QofQ is very cool, but in a maddening unpredictable way. Argh.

@Alan,

That's frustrating when not even a CAST() call will fix your problem (or at least, not with the more appropriate data type :)).

Ben, I'll pass along another QofQ datatype disaster I went through a couple days ago. An app that uses QofQ started throwing "cannot conver value '' to number" errors for no apparent reason. The QofQ had been CASTed to death, and I could not come to a solution, but did come up with a strange but successful jury-rig.

I seralized the original query with CFWDDX, then deseralized it back to a query again. After that, the subsequent QofQ worked fine. Go figure.

Anyhow, I'm not trying to hijack this thread or anything, but doing a google of "cf query of query" brings this page up so it's become a good resource for QofQ victims. Thanks for your excellent blog.

@Alan,

Please, never feel bad about contributing to the conversation; every time someone brings up a new point, it's an opportunity for us to learn something new.

The converting "" to numeric, maybe that happens if the value is NULL? Although I think QoQ should handle NULLs properly... although who knows, right??

I display usage quarterly for various products in tabular format. I displays products in rows and monthly usage in column. When I query my sql database, I may get 1-3 records based on the entry. I create a coldfusion query which has 0 as usage for all month for a given product. I union both the query and sum up usage grouping date so I will get a record for every month irrespective for data in database. My problem starts while merging data as date format are different in cf query ans sql query. Data do not group due to date difference (sql 2010-10-01 00:00:00.0 do not match with {ts '2010-10-01 00:00:00'}). I cast sql date while merging data and bingo... it works.. thanks for the solution given it this article.

select start, cast(usagedate as Date) as usagedate from sql.qrymaxrow
union all select start, usagedate from cfm.rowscounter

Hmmmm. My problem seems to fit into this maybe. Stranger things have happened. But this is NOT a QoQ. I am querying an Oracle 10g dtabase with an IN clause which is a query. So maybe it is having the same effect as a QoQ. Basically there is an ID number that can be either digits or a combo of letters and digits. So I do a query that retrieves a list of IDs. I may have B22,699,C22 type of return.

Now I want to get more info for those 3 and so I run the query

Select info
from infotable
where id IN (select ids from idtable)

I get things like "B22 is an invalid identifier"

It shows that the IDs are being put in the query without quotes around them. So I ran that query seperately and tried casting the ID as text but still no go. I tried manually putting single quotes around each one but when I build the string for the query it then puts another set of quotes in.

Any ideas?

@Don,

Hmm, not sure about that one. I don't have any experience with Oracle, so this may very well be Oracle-specific. I have definitely use the IN with a sub-query inside of it with success. Are the ID in the top query and the IDS in the sub-query the same data type?

<cfquery dbtype="query" name="qryCashReceipt">
select cashreceiptid, category + ' - ' as Dspstr1,
'#qryCashReceipt.category# - #DollarFormat(qryCashReceipt.amount)# (#dateformat(qryCashReceipt.createdate,"d/m/yy")#)' as Dspstr2
from qryCashReceipt
</cfquery>

this also work

Ditto Grant's thanks, from the bottom of MY QofQ recordset, which was too full because the date filter on my where clause didn't work until I CAST both sides of the comparison!

It's good to see this is still broke on the latest version of CF, some 6.5 years after it was first raised.

What was the ticket number for this, Ben?

--
Adam

This was really helpful. I had an issue where I wanted to have something like:

  • WHERE date1 <= <cfqueryparam value="#d1#" cfsqltype="CF_SQL_TIMESTAMP" />

But it wasn't working, and casting to a string wouldn't work because it's a date comparison, so I used:

  • WHERE CAST(date1 AS DATE) <= CAST(<cfqueryparam value="#d1#" cfsqltype="CF_SQL_TIMESTAMP" /> AS DATE)

And it worked.

Well, it's now 2014, and I too had this same issue when trying to use a date in my WHERE statement of my QoQ.

I was plugging in all my data and pulling my hair out. It's strange that I am doing this in other QoQ on the same page and it works fine.

#bennadel4president