ColdFusion Query of Query Oddity With Table-Prefixed Columns And ORDER BY
Posted August 3, 2006 at 9:14 AM by Ben Nadel
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:
| | | | ||
| |  | | ||
| | | |
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:
| | | | ||
| |  | | ||
| | | |
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.
| | | | ||
| |  | | ||
| | | |
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.
| | | | ||
| |  | | ||
| | | |
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:
| | | | ||
| |  | | ||
| | | |
When I look in the ColdFusion Live Docs, I am not seeing anything about this bug in the ORDER BY documentation.
Reader Comments
Send this to Damon Cooper at Adobe.
Came across the same bug independently. Thanks for the nice documentation!
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
@Nate,
Ah, thanks for the insight. Query of queries - so awesome, so frustrating.
