ColdFusion Query of Query Oddity With Table-Prefixed Columns And ORDER BY
Posted August 3, 2006 at 9:14 AM
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:
Launch code in new window » Download code as text file »
- <!--- 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:
Launch code in new window » Download code as text file »
- <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):
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
ColdFusion JavaCast() Adds No Performance Hit
Older Post
GMail Chooses ONLY Non-HTML Mail If Given The Option
Reader Comments
Send this to Damon Cooper at Adobe.
Came across the same bug independently. Thanks for the nice documentation!
