Query of Query bug involving numbers

This post is more than 2 years old.

Thanks for this find comes to me from Victor Yelizarov. He reported to me an odd issue with sorting and query of queries. Consider the following code sample:

<cfset q = queryNew("number","Decimal")>

<cfset queryAddRow(q,5)> <cfset q["number"][1] = 10.1> <cfset q["number"][2] = 0.01> <cfset q["number"][3] = 22.75> <cfset q["number"][4] = 21.05> <cfset q["number"][5] = 8.33>

<cfquery name="q" dbtype="query"> SELECT number FROM q ORDER BY number DESC </cfquery>

<cfdump var="#q#">

We know that ColdFusion looks at the query metadata to figure out how to do sorting. In this case he explicitly set the column type to decimal. That should have been enough. But from the result you can see otherwise...

So obviously this is a bug - how do we get around it? If you JavaCast when setting the values, that works. If you multiply the values by one, that also works. But an easier solution, and one that may be useful to remember anyway, is to cast in your query of query.

<cfquery name="q3" dbtype="query"> SELECT cast(number as decimal) as n FROM q ORDER BY n DESC </cfquery>

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Raymond Camden posted on 8/2/2011 at 1:40 AM

I filed this bug report: http://cfbugs.adobe.com/cfb...

Comment 2 by Andrew Scott posted on 8/2/2011 at 7:01 AM

This is not a bug, and something a lot of people including myself have fallen too. The problem is that unless you add the type of data it is, ColdFusion will assume it is something else.

So what you need to do is something like this.

http://livedocs.adobe.com/c...

And when you add the type of data, the QoQ qill behave in the correct manner.

Comment 3 by Raymond Camden posted on 8/2/2011 at 7:03 AM

Eh? Isn't that what the code does? The very first line?

Comment 4 by Andrew Scott posted on 8/2/2011 at 7:10 AM

Hmm, you are right how did I miss that :-(

Comment 5 by Raymond Camden posted on 8/2/2011 at 7:10 AM

Yeah, I've never done that. Ever. (Um....)

Comment 6 by Andreas Schuldhaus posted on 8/2/2011 at 10:27 AM

It's not the query itself that's to blame for the wrong sort, it's the way you populate the query rows.

In my experience, using querySetCell() takes perfectly care of the data type, while array notation obviously doesn't. For whatever reason.

<cfset q = queryNew("number","decimal")>
<cfset queryAddRow(q, 5)>
<cfset querySetCell(q, "number", 10.1, 1)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 0.01, 2)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 22.75, 3)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 21.05, 4)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 8.33, 5)>

<cfquery name="q" dbtype="query">
SELECT number
FROM q
ORDER BY number
</cfquery>

<cfdump var="#q#">

This should work without the need of casting an tweaking in the select clause.

Comment 7 by Raymond Camden posted on 8/2/2011 at 6:05 PM

Wow - you are right. I don't normally use the format above - that's how he sent me his test code. That must be why I haven't run into this before. Good find there Andreas!

Comment 8 by Doug posted on 8/2/2011 at 6:54 PM

Hey Andreas, how come you commented out the add row lines? (Or why were they there in the first place?)

Comment 9 by Raymond Camden posted on 8/2/2011 at 7:13 PM

You don't have to add the rows since 5 were added initially.

Comment 10 by Andreas Schuldhaus posted on 8/2/2011 at 7:15 PM

@Doug No special meaning, in there. I realized right after pressing the post button, that I had forgotten to delete them. They were there in the first place, because in my scribble I first added line by line to the query and then did some refactoring and switched to adding 5 rows at once.

Comment 11 by Doug posted on 8/2/2011 at 7:24 PM

aha. Thanks.

Yeah, I always thought QuerySet was kind of a nuisance, but you raise a good reason to make sure we are thorough when we're creating our own queries.

Comment 12 by Raymond Camden posted on 8/2/2011 at 7:37 PM

Back in the day Hal Helms (I believe) had a custom tag so you could do stuff like:

<cf_newquery name="q">
id,name,age
1,Raymond,38
2,Jacob,9
</cf_newquery>

Obviously not ideal, but for quick stuff it was simpler.

Comment 13 by Andreas Schuldhaus posted on 8/2/2011 at 8:26 PM

I agree! Hal's cf_querysim was a great and very useful tool. I even used it to import text data, such as e-mail addresses and the like and mainly for quickly populating dropdowns while developing an application. Some (read: more than I would admit :) of these sims survived the development process, have never been replaced by 'real' queries and are still in use.

Comment 14 by Raymond Camden posted on 8/2/2011 at 8:27 PM

I'm doing a presentation at RIAUnleashed - a 3 hour one on extending CF. In my custom tags section I'll mention this.

Comment 15 by Aaron Neff posted on 8/4/2011 at 3:12 AM

This trips me up sometimes, as I prefer fewer chars (array notation vs querySetCell). When used within a list of query cell assignments, querySetCell is an _ok_ workaround:

q.stringCol1[1] = "foo";
querySetCell(q, "numberCol", 10.1, 1);
q.stringCol2[1] = "bar";

However, querySetCell is not so helpful when it's just a single dynamic query cell assignment being looped over:

[begin loop]
q[colName][rowNum] = cellValue;
[end loop]

Also, when all columns are needed, "SELECT *" is shorter than "SELECT col1,col2,col3,CAST(col4 AS castType) AS Col4,..,col19,col20".

Array notation should respect query column datatype. (IMNSHO)

Comment 16 by AXL posted on 8/6/2011 at 8:58 AM

This tip also helped a question that just came to the Japanese CFUG a couple of days ago. The person was trying to insert a huge data into a database, but it seemed that the data was somehow truncated when retrieving. So I tried to replicate the issue by using the following code. It's basically creating a text that contains 50,000 letters and inserting it into a database, and then retrieving it.

<cfset letters = "abcde">
<cfset text = ''>

<cfloop index="i" from="1" to="10000">
<cfset text &= letters>
</cfloop>

Text Length: <cfoutput>#len(text)#</cfoutput><br />

<cfquery name="qInsert" datasource="cfjp" result="result">
INSERT INTO test(content)
VALUES(<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#text#">)
</cfquery>

Queryparam Length: <cfoutput>#len(result.sqlparameters[1])#</cfoutput><br />

<cfquery name="qRead" datasource="cfjp">
SELECT content
FROM test
WHERE id = #result.identitycol#
</cfquery>

Content Length: <cfoutput>#len(qRead.content)#</cfoutput>

Then the output was:

Text Length: 50000
Queryparam Length: 50000
Content Lengh: 32000 (This should be 50000, right?)

So first I thought my SQL server (MS SQL 2008 R2) might be the issue, but I used the data type varchar(max) for the field named "content" to store the data, and according to MSDN(http://msdn.microsoft.com/e..., it can hold up to 2^31-1 bytes.
There shouldn't be a problem for this test and sure enough when I checked the data in the database, exact 50,000 letters I inserted were there.
So what's the heck?

After for a while trying to figure this thing out, I remembered this tip and applied it. Bingo. That fixed the problem. All I needed to do was to change the second cfquery to this.

<cfquery name="qRead" datasource="cfjp">
SELECT CAST(content as varchar(max)) as c
FROM test
WHERE id = #result.identitycol#
</cfquery>

Content Length: <cfoutput>#len(qRead.c)#</cfoutput>

Then the output was:

Text Length: 50000
Queryparam Length: 50000
Content Lengh: 50000 (Fixed it)

Thanks for the tip.