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 CFinNC 2009 (Raleigh, North Carolina) with:

QUERY.ColumnList Does Not Return True Column Ordering

By Ben Nadel on
Tags: ColdFusion

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?

Reply to this Comment

@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?

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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).

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

@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!

Reply to this Comment

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!

Reply to this Comment

Mike, that is exactly what I have been looking for to automate some query-> reporting functions I have been building :)

Thanks,

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.