I ran into this interesting issue this morning. I had this query to return some data.

view plain print about
1<CFQUERY NAME="getData" DATASOURCE="myDSN">
2 SELECT col1, col2, col3, othertable.*
3 FROM firsttable
4 JOIN othertable ON firsttable.id = othertable.id
5 where firsttable.id = #id#
6</CFQUERY>

I added the join to "othertable" table and just returned the other tables full content. I intended to shrink the column return once I figured out what I wanted to use.

For some reason in the return the "col1" column was always empty. I looked into the table's data and there was data there. So, after a bit of poking around I found the issue.

In the "otherTable" there was a column called "col1". This column had no data. For some reason ColdFusion was overwriting the data of the first column with data from the 2nd. I ran the same query outside of CF and the data was returned correctly. Each column still retained is own data.

As a quick test, you can use the code below. The output is very interesting. No matter what you change the data to in the first column it is always overwritten with the data from the 2nd.

view plain print about
1<cfset myQuery = QueryNew("Name, Name")>
2<cfset newRow = QueryAddRow(MyQuery, 2)>
3<cfset temp = QuerySetCell(myQuery, "Name", "Sample Data col 1", 1)>
4<cfset temp = QuerySetCell(myQuery, "Name", "Sample Data col 2", 1)>
5<CFDUMP var="#myQuery#">

Granted, I don't expect anyone to actually do this. You should always name our columns in your query returns and never return "*".

Till next time...

--Dave