Possible Bug in ColdFusion 10 Query Caching

Question

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:

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

What I should get:

Results for BookNo 97, PageNo 1. (not cached)
Results for BookNo 212, PageNo 50. (not cached)
Results for BookNo 250, PageNo 12. (not cached)
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.

Carl Von Stetten · Accepted Answer

@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?Yes.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.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 · Answer

Carl Von Stetten wrote:Possible Bug in ColdFusion 10 Query CachingIt'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?

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded