Interesting ColdFusion CFQuery "Feature"

And just to be clear, when I say "feature" I actually mean "completely broken behaviour," though for a change of pace it's not with the compiler.  Probably.  Hopefully.

Consider this innocent-seeming code:

<cfquery datasource="dsn_A" name="">
  insert into test (s) values (
    'dsn_A - record 1'
  )
</cfquery>

Every CF app has this (or equivalent) in a million places.  Now let's change it slightly to make the string we're inserting dynamic (I'll show the 'getMsg' UDF in a moment):

<cfquery datasource="dsn_A" name="">
  insert into test (s) values (
    'dsn_A - record 2 #getMsg()#'
  )
</cfquery>

So far so good.  Now here's the UDF:

<cffunction name="getMsg">
  <cfset var get = "" />
  <cfquery datasource="dsn_A" name="get">
    select count(*) as c
    from test
  </cfquery>
  <cfreturn "(#get.c# records in dsn_A)" />
</cffunction>

What would you expect this to do?  Return a string, of course, containing the recordcount of the 'test' table in "dsn_A", and it does this perfectly.  It also has the glorious side effect of changing the outer CFQUERY's datasource to "dsn_A".  To put that another way, a given query does NOT execute with the datasource passed to the opening CFQUERY tag.  Rather, it executes with the datasource passed to the last CFQUERY tag before the closing CFQUERY tag. Don't believe me?  Here's a test case:

<cffunction name="getMsg">
  <cfset var get = "" />
  <cfquery datasource="dsn_A" name="get">
    select count(*) as c
    from test
  </cfquery>
  <cfreturn "(#get.c# records in dsn_A)" />
</cffunction>

<cfquery datasource="dsn_A" name="">
  insert into test (s) values (
    'dsn_A - record 1'
  )
</cfquery>
<cfquery datasource="dsn_B" name="">
  insert into test (s) values (
    'dsn_B - record 1'
  )
</cfquery>

<cfquery datasource="dsn_A" name="get">
  select * from test
</cfquery>
<!--- one record, as you'd expect --->
<cfdump var="#get#" label="dsn_A" />

<cfquery datasource="dsn_B" name="get">
  select * from test
</cfquery>
<!--- one record, as you'd expect --->
<cfdump var="#get#" label="dsn_B" />

<cfquery datasource="dsn_A" name="">
  insert into test (s) values (
    'dsn_A - record 2 #getMsg()#'
  )
</cfquery>
<cfquery datasource="dsn_B" name="">
  insert into test (s) values (
    'dsn_B - record 2 #getMsg()#'
  )
</cfquery>

<cfquery datasource="dsn_A" name="get">
  select * from test
</cfquery>
<!--- three records!! (the two for dsn_A, plus dsn_B's second record) --->
<cfdump var="#get#" label="dsn_A" />

<cfquery datasource="dsn_B" name="get">
  select * from test
</cfquery>
<!--- one record!! (only dsn_B's first record) --->
<cfdump var="#get#" label="dsn_B" />

In order to run it, you'll need to two DSNs configured ("dsn_A" and "dsn_B"), pointed at two distinct databases, each with a 'test' table containing a single varchar column named 's'.

This was tested on CF8 and CF9, with and without CFQUERYPARAM.  I also tested with implicit and explicit datasources on CF9.  Same behaviour in all cases.  Wheeee…

15 responses to “Interesting ColdFusion CFQuery "Feature"”

  1. Adam Lehman
  2. Adam Lehman

    Thanks. I'll keep an eye on it and see if we can get it fixed for 9.01.

    As far an HTML-based bug tracker goes… no problem. We'll get to work on it.

    -Adam

  3. Raymond Camden

    There is a bug filed for this. I filed it a few months ago.

  4. Raymond Camden

    Took me a while, but I found it:

    http://www.coldfusionjedi.com/index.cfm/2009/8/11/Interesting-CFQUERY-Bug

    Reported back in August. :)

  5. Jake Munson

    I was confused at first because you have a mistake in your example code. Your second and third example code blocks both use 'dsn_A'. I think you meant one of those to be 'dsn_B'.

  6. Jake Munson

    Ok, well I guess I'm still confused then. You said, "it executes with the datasource passed to the last CFQUERY tag before the closing CFQUERY tag" but looking at your snippets before that statement, they all use the same datasource. So I don't see a problem, your inner query is updating the outer query's datasource, but it was the same to begin with. Or am I just being thick?

  7. Ben Nadel

    The first time I saw someone run a CFQuery tag *inside* another CFQuery tag, I was blown away that it worked AT ALL. Keep in mind, that was *inline* nested CFQuery tags. The use of a function makes it a bit more natural feeling.

  8. Ali

    @Ben -

    Sorry to go off another tangent.
    I'm curious, why would someone want to run an inline nested CFQuery for anything?

    I mean it makes sense to me, in modularizing code to put queries in their own functions and then you'd
    need to do soemthing like this.

    But still I'm interested in what the old-school example you ran into. I can't ever see a reason for nesting
    a CFQuery inside another. Because at that point you can do whatever you need to in almost T-SQL syntax.

  9. Ben Nadel

    @Ali,

    I think the person was doing something like:

    select id from users where …..
    select * from orders where u.id = #users.id#

    There's no reason that the nested CFQuery couldn't just be moved outside the parent CFQuery. Just struct me as very odd the first time I saw this.

  10. Michael

    Does anyone know if this bug was fixed? If so, do you have the bug ID? I can't seem to find it.

  11. Raymond Camden

    CF10 is in public beta – someone test it. :)

  12. Raymond Camden

    I can confirm this bug was not fixed in ColdFusion 10.