QUERY.ColumnList Does Not Return True Column Ordering
Posted April 18, 2007 at 8:39 AM by Ben Nadel
This is a really minor point that I just happened to run into yesterday. I had to rewrite and expand upon some old code in one of our systems (originally written by a crazy man) when I came across a query that used "SELECT *" to get its columns. This really drives me crazy as not only is it slow, it also provides absolutely no insight into what the query is returning. For demonstration's stake, let's build a query to demonstrate how ColumnList ordering messed me up:
- <!--- Create an empty query. --->
- <cfset qGirl = QueryNew( "" ) />
-
- <!---
- Add columns to this empty query and provide
- default column values.
- --->
- <cfset QueryAddColumn(
- qGirl,
- "name",
- "CF_SQL_VARCHAR",
- ListToArray( "Libby,Kit,Sam,Niki" )
- ) />
-
- <cfset QueryAddColumn(
- qGirl,
- "hair",
- "CF_SQL_VARCHAR",
- ListToArray( "Blonde,Blonde,Blonde,Brunetted" )
- ) />
-
- <cfset QueryAddColumn(
- qGirl,
- "age",
- "CF_SQL_INTEGER",
- ListToArray( "28,24,25,27" )
- ) />
Notice that this query now has columns added in this order: Name, Hair, Age. Assuming this was the query that was queried using "SELECT *", I dumped out the ColumnList attribute to give me the columns name. My plan was then to replace the "*" in the select statement with the column names:
- <!--- Get query column names. --->
- <cfdump var="#qGirl.ColumnList#" />
This output the following:
AGE,HAIR,NAME
Notice that the order of these columns is alphabetical and is not reflective of how the query was actually constructed. Most of the time, this would not make an ounce of difference as to what you were doing, even if you were using the column list to loop over the query. In my case, however, I had to join the SAME query from two different data sources. Same query, same structure, just different data sources. After each query was performed, I needed to UNION ALL them together (pretend the qGirlOne and qGirlTwo are similar queries):
- <!---
- Query the Girl queries from the different data sources
- and union them together. Since we don't actually want
- to eliminate any records, use UNION ALL.
- --->
- <cfquery name="qGirlBoth" dbtype="query">
- (
- SELECT
- *
- FROM
- qGirlOne
- )
-
- UNION ALL
-
- (
- SELECT
- *
- FROM
- qGirlTwo
- )
- </cfquery>
This works quite nicely. My problem was that this also makes use of the "SELECT *" statement. So, taking the column list that I CFDump'ed out above, I applied it to the first query in the union:
- <!---
- Query the Girl queries from the different data sources
- and union them together. Since we don't actually want
- to eliminate any records, use UNION ALL.
- --->
- <cfquery name="qGirlBoth" dbtype="query">
- (
- SELECT
- AGE,
- HAIR,
- NAME
- FROM
- qGirlOne
- )
-
- UNION ALL
-
- (
- SELECT
- *
- FROM
- qGirlTwo
- )
- </cfquery>
Notice that I only use the column list in the first query and NOT in the second query. This was very deliberate; once you name the columns in the first query, the names of the columns in subsequent unioned queries are ignored (the column values are used, but only the column names from the first query are acknowledged). I didn't mind leaving the "SELECT *" in the second query as I felt the first one provided enough feedback to the programmer.
But, running the above query, I got the ColdFusion error:
Error Executing Database Query. Query Of Queries runtime error. All resulting columns of queries in a SELECT statement containing a UNION operator must have corresponding types. Columns with index number equal "1" have different types (INTEGER, VARCHAR). The error occurred on line 58.
At first, this really threw me through a loop. I mean, what the heck? They are the same query (just different data sources). Why would their columns not line up? But then it dawned on me - I had no idea how the actual query was constructed (as it was using "SELECT *" from the get-go). And then I thought, those column names look awfully alphabetical.
The problem, as it turns out, is that the two queries in the UNION ALL query had the same columns, but because I was naming the column explicitly in the first one, it had a different column order than the second query in the union. Had I also explicitly name the columns in the second query as well, this would not even have cropped up.
This just goes to show, I think, that "SELECT *" is the devil's tool. It provides no readability or insight to any programmer that comes after you. If you name your columns, I think it makes you a better person.
Reader Comments
yeah, "select *" does suck. However, ordering the columns alphabetically by default is also a pain in the ass. I've also noticed that the same thing happens when I use cfdump to dump a query - the freaking columns are ordered alphabetically even when I have explicitly specified the order. Wtf? Any idea how to remedy that?
@Boyan,
I agree re: alpha listing. It makes debugging a large query more difficult because you have to search for the columns rather than just knowing that it should be the first or second column, etc. And, whats more, if you grab the underlying Java methods of the query, you can get the columns in their given order... why don't they use that?
@Ben, What IS the underlying Java method if you want the query columns in order?
While I agree that SELECT * does suck from a readability standpoint, The "Slowness" argument that I hear from time to time isn't really valid from my experiments. Admittedly, you should only query for the fields you're going to actually use, but if you're using every column (like you are in your examples) then SELECT * isn't any noticeably slower than it's more "specified" counterpart.
@Pete,
I think you can use QUERY.GetColumnNames(). I used it once trying to get the names of the column of a query:
http://bennadel.com/index.cfm?dax=blog:357.view
... but take a look at this site:
http://www.activsoftware.com/mx/undocumentation/query.cfm
It lists out all the underlying Java methods (not all are available as far as I know though).
Using "SELECT *" isn't slower than specifying all of the columns by name, in my experience. But the disadvantage of is that 1) it's slower if you're returning columns you don't need, and 2) if you change/add columns in the table you're reading from, your query results might not reflect the changes immediately because the database use an outdated cache of the column list in the query pre-processing stage.
I remember that crazy man. That was a crazy time in the office. He should be getting Parole soon!
... or chemically castrated (according to MySpace.com)
Peter - you can use getMetaData on the query object now to get the correct order (I beleive).
Ben- ""SELECT *" is the devil's tool. It provides no readability or insight to any programmer that comes after you"
That is quite true, but it is also true that everytime your database changes if you are naming the columns in the query you've got at least one extra place to change it. (But, I still think I prefer naming them).
Yeah, exactly. Even if you have to change a column name, I think that is going to be in the minority of cases when you compare it to how often you might have to open a file and review what is going on.
If you want all columns, in their specified case and in their selected order, use this:
columns = arrayToList( myQuery.getMeta().getColumnLabels() );
getColumnLabels() returns a java string array (string[]), so if you want to use cf array methods on it, you'll need to bounce it through a listToArray(arrayToList()) or similar.
It even gives you the complete list of columns when you: select * from table.
@Mike,
Thanks for the insight.
@Mike,
That was exactly what I was looking for..
Thanks,
Poo
@Mike,
Thank you! Exactly what I was looking for also! Not only does it return them in the correct order, it also keeps their case!
Ben...once again I run into a problem, and you have a blog post on the very topic. This was helpful!
I was doing something where I needed to output the data into an Excel file, but it kept putting the data in the wrong order (alpha). I needed it in a specific order to make the Excel doc more meaningful to the user.
I looked here and then a thread on Adobe, and ended up using this:
<cfset colHeaderNames = ArrayToList(qTableSelected.getColumnList()) />
That spit back the order of the columns I put the query in, rather than the alpha order.
Sooner or later I'll find a problem you haven't already touched on. It's my new mission!
@Bret,
Exactly the same reason I came here. The solution worked great, thanks!
Mike, that is exactly what I have been looking for to automate some query-> reporting functions I have been building :)
Thanks,
