Possible Bug in ColdFusion 10 Query Caching

[Carl Von Stetten]

I may have stumbled onto a bug when caching queries in ColdFusion 10.  I wanted to see if anyone else had seen this before I submitted a bug report.  It will be difficult to put together a complete test case, so hopefully a description will be enough to confirm if this is reproducible.

First, I create an array of structures.  Each structure is a set of key/value pairs of arguments that will be passed to a query.  The first two keys hold numeric values, the third a string value.  I am querying a Microsoft SQL Server database.  My query is inside an array loop.  The query has the CachedWithin attribute set to 1 hour. In the query, I pass the values in the structure into the WHERE clause using CFQueryParam.

If two of the sets of numeric values are similar enough but not identical, ColdFusion will jumble the values and pull back an incorrect cached query result set.  As an example, here are the values in my array:

[{BookNo=97,PageNo=1,MapType="Assessor's Map"},{BookNo=212,PageNo=50,MapType="Assessor's Map"},{BookNo=250,PageNo=12,MapType="Assessor's Map"},{BookNo=97,PageNo=1,MapType="Assessor's Map"}]

When I run the page for the first time with an empty query cache, here is what I get:

1. Results for BookNo 97, PageNo 1. (not cached)
2. Results for BookNo 212, PageNo 50. (not cached)
3. Results for BookNo 212, PageNo 50. (cached)
4. Results for BookNo 97, PageNo 1. (cached)

What I should get:

1. Results for BookNo 97, PageNo 1. (not cached)
2. Results for BookNo 212, PageNo 50. (not cached)
3. Results for BookNo 250, PageNo 12. (not cached)
4. Results for BookNo 97, PageNo 1. (cached)

Notice the problem with the third iteration - it's pulling back the same results from the second iteration.  If I remove the CachedWithin, I get the expected results.  If I create my own CacheID, I get the expected results.  It's only when I let ColdFusion handle the cache on it's own that I get the erroneous results.

Anyone seen anthing like this?  Does this look like a legitimate bug that should be filed?

-Carl V.

[Adam Cameron.]

Sounds like a bug to me. Looks like the process that creates the cache key doesn't respect the order of the params or something  like that?

I'm not in front of my dev PC @ the mo', but will have a look @ this when I get back to civilisation (tomorrow, but won't be doing any code until Fri).

--

Adam

[Carl Von Stetten]

Thanks @Adam!

-Carl V.

[BKBK]

Carl Von Stetten wrote:

Possible Bug in ColdFusion 10 Query Caching

It's too complex for me to say. I have these questions, for example:

1) When the loop is processed, does ColdFusion process the 4 queries consecutively?

2) A comparison of  SQL statements is one of the checks ColdFusion performs to decide whether to run a given query or use cached data. Does data binding by means of cfqueryparam have an effect on the SQL statement?

3) Place a cfdump of the query as the last statement in the loop. A query dump tells you whether or not a query is cached. Can you see which queries are cached?

[Carl Von Stetten · Accepted solution]

@BKBK,

It's too complex for me to say. I have these questions, for example:

1) When the loop is processed, does ColdFusion process the 4 queries consecutively?

2) A comparison of  SQL statements is one of the checks ColdFusion performs to decide whether to run a given query or use cached data. Does data binding by means of cfqueryparam have an effect on the SQL statement?

3) Place a cfdump of the query as the last statement in the loop. A query dump tells you whether or not a query is cached. Can you see which queries are cached?

1. Yes.
2. Oooh!  I have been using cfqueryparam.  I removed the cfqueryparams, and now it behaves as expected.  Seems there is a bug when combining cfqueryparam and caching.
3. Already had this, and it was serving the cached results from the second query when supposedly processing the third query (see original post for the expected and actual results).

-Carl V.

Update: And it looks like this is a known bug after all with cached queries and cfqueryparam: https://bugbase.adobe.com/index.cfm?event=bug&id=3369530.  I'm going to add my vote, and maybe others can too.

Update 2: And looking more closely, it looks like it has been fixed in a yet-to-be-released build.  Hopefully we'll get it soon.  One workaround suggested was to place the cfqueryparam values into a SQL comment, which seems to help CF recognize the unique query statements and cache them properly.

Message was edited by: Carl Von Stetten

[BKBK]

Thanks for sharing this very important result!

[Carl Von Stetten]

@BKBK,

No problem.  I probably wouldn't have even started the thread if I'd thought about CFQueryParam being a possible component of the problem.  I remember now seeing that bug announced on Twitter a few months back, and looking at it on the bug site.  Before posting here, I did a bug search, but looked under "Caching" instead of "Database".  Oh well.

-Carl V.