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 the jQuery Conference 2010 (Boston, MA) with:

ColdFusion Query of Query Oddity With Table-Prefixed Columns And ORDER BY

By Ben Nadel on
Tags: ColdFusion

NOTE: I was asked to remove someone's name from this blog entry. I am waaaaay too lazy to remake these images (just as I am too lazy to make up names). If you would like to see the original post please contact me and if I have some spare time, I can remake.

I am running into this really weird ColdFusion query of queries "bug" when I prefix columns with the table name; one of the columns is getting selected twice for no apparent reason. To demonstrate (as I can easily produce this bug), let's build a simple query:

  • <!--- Create a base query of data. --->
  • <cfset qGirl = QueryNew( "id, first_name, last_name" ) />
  •  
  • <!--- Add data to that query. --->
  • <cfset QueryAddRow( qGirl, 3 ) />
  • <cfset qGirl[ "id" ][ 1 ] = 1 />
  • <cfset qGirl[ "first_name" ][ 1 ] = "Molly" />
  • <cfset qGirl[ "last_name" ][ 1 ] = "Clarke" />
  •  
  • <cfset qGirl[ "id" ][ 2 ] = 2 />
  • <cfset qGirl[ "first_name" ][ 2 ] = "Sarah" />
  • <cfset qGirl[ "last_name" ][ 2 ] = "Vivenzio" />
  •  
  • <cfset qGirl[ "id" ][ 3 ] = 3 />
  • <cfset qGirl[ "first_name" ][ 3 ] = "xxxxx" />
  • <cfset qGirl[ "last_name" ][ 3 ] = "yyyyy" />

If you dump this query out, it looks as you would expect:


 
 
 

 
CFDump - qGirl  
 
 
 

Now, let's perform a query of queries on the table and order by the first and last names:

  • <cfquery name="qAllGirls" dbtype="query">
  • SELECT
  • id,
  • first_name,
  • last_name
  • FROM
  • qGirl
  • ORDER BY
  • first_name ASC,
  • last_name ASC
  • </cfquery>

If you dump this query out, again, exactly as you would expect it:


 
 
 

 
qallgirls gif  
 
 
 

Now, let's run that same exact query again, but this time, you prefix the column names with the tablet that they are coming from (which is exactly what you would have to do in a JOIN situation):

  • <cfquery name="qAllGirls2" dbtype="query">
  • SELECT
  • qGirl.id,
  • qGirl.first_name,
  • qGirl.last_name
  • FROM
  • qGirl
  • ORDER BY
  • qGirl.first_name ASC,
  • qGirl.last_name ASC
  • </cfquery>

Dumping this query out, you will clearly see that the last column in the ORDER BY clause is getting selected twice. And, it always seems to be the last column; if I were to switch the first and last name columns, then the first name would be selected twice.


 
 
 

 
qallgirls2 gif  
 
 
 

Now, in the above example, I am only using two ORDER BY rules. If you put in more than two, the results get even more crazy. In the following, I am going to order by ID as well:

  • <cfquery name="qAllGirls3" dbtype="query">
  • SELECT
  • qGirl.id,
  • qGirl.first_name,
  • qGirl.last_name
  • FROM
  • qGirl
  • ORDER BY
  • <!--- Added the ID sort here. --->
  • qGirl.id ASC,
  • qGirl.first_name ASC,
  • qGirl.last_name ASC
  • </cfquery>

WHAT!?! Now, BOTH first name and last name are getting selected twice! My SELECT statement has not changed at all.


 
 
 

 
qallgirls3 gif  
 
 
 

Ok, for one final little experiment, I am trying only order by ID, but order by ID several times:

  • <cfquery name="qAllGirls4" dbtype="query">
  • SELECT
  • qGirl.id,
  • qGirl.first_name,
  • qGirl.last_name
  • FROM
  • qGirl
  • ORDER BY
  • qGirl.id ASC,
  • qGirl.id ASC,
  • qGirl.id ASC
  • </cfquery>

Dumping this out, the results are just too odd to explain:


 
 
 

 
qallgirls4 gif  
 
 
 

When I look in the ColdFusion Live Docs, I am not seeing anything about this bug in the ORDER BY documentation.


Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

Also, Cold Fusion Query-Of-Queries does not have a precision mechanism on date/time fields in the ORDER BY clause. So...

<cfquery name="qGirl5" dbtype="query">
SELECT id, first_name, last_updated_date
FROM qGirl
ORDER BY last_updated_date ASC, first_name ASC
</cfquery>

-1/1/2010 Sara
-1/1/2010 Tom

...might in fact end up something like...

-1/1/2010 23:12:00:0000 Tom
-1/1/2010 23:59:00:0000 Sara

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.