tracker issue : CF-4198980

select a category, or use search below
(searches all categories and all time range)
Title:

Re-querying a cached query doesn't return the initial cached query after result variable is modified

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/Duplicate

Reporter/Name(from Bugbase): James Moberg / James Moberg ()

Created: 07/03/2017

Components: Caching

Versions: 2016

Failure Type: Data Corruption

Found In Build/Fixed In Build: 2016,0,04,302561 /

Priority/Frequency: Normal / All users will encounter

Locale/System: / Win 2012 Server x64

Vote Count: 1

Problem Description: Performing a query that is already cached will not return the initial, based query that was cached.  If you add columns or modify values post-query, they become a permanent part of the initial cached query.  NOTE: This has worked consistently in ColdFusion 4-11 (and Railo/Lucee) until ColdFusion 2016 (which we're required to upgrade to due to CF10 EOL.)

Steps to Reproduce:
1) Perform a cached query.
2) Modify one of the column values (ie, QueryName.ColumnName[1] = 'Something Else';)
3) Re-perform the cached query... the query result set should be the same as the initial cached query (#1), but it's not.

Here's some sample code:
https://gist.github.com/JamoCA/fe7ff5be43560ba6f8b6c513fdf9f723

Actual Result: The modified results of the query #2.

Expected Result:  The initial, base query from the database (1) without any retained post-query modifications (as is consistent with ColdFusion 3, 4, 5, 6, 7, 8, 9, 10 & 11.)

Any Workarounds: None.

Attachments:

  1. July 03, 2017 00:00:00: CF2016QueryCache.cfm
  2. July 13, 2017 00:00:00: CFQueryCache_20170713081847.png

Comments:

Yup, cached queries continue to be a bit of a disaster in CF2016. A couple more repros: <!--- ticket_4198980 table is: id | myColumn --------------- 1 | NULL ---> <cfscript> q = queryExecute("SELECT 10 AS test, myColumn FROM ticket_4198980", [], {cachedwithin=createTimeSpan(0,0,0,3)}); writeOutput(q.test & q.myColumn & '<br>');//returns "10" (good) //Begin making some changes to q resultset q.test[1] = 2016; q.myColumn[1] = "some value"; q.addColumn("addedColumn", "integer", [1]); writeOutput(q.test & q.myColumn & q.addedColumn & '<br>');//returns "2016some value1" (good) //End making some changes to q resultset q = queryExecute("SELECT 10 AS test, myColumn FROM ticket_4198980", [], {cachedwithin=createTimeSpan(0,0,0,3)}); writeOutput(q.ColumnList & '<br>');//returns "MYCOLUMN,TEST" (good) writeOutput(q.test & q.myColumn & '<br>');//CF2016 Update 1 and 3 return "2016some value" (bad). CF11 returns "10" (good). </cfscript> <hr> <cfscript> q1 = queryNew("foobar", "", [["foo"]]); writeOutput(q1.foobar & '<br>');//returns "foo" (good) q2 = queryExecute("SELECT foobar FROM q1", [], {dbtype="query", cachedWithin=createTimeSpan(0,0,0,3)}); q2.setCell("foobar", "bar", 1); q2.addColumn("addedColumn", "integer", [1]); writeOutput(q1.foobar & '<br>');//returns "foo" (good) q3 = queryExecute("SELECT foobar FROM q1", [], {dbtype="query", cachedWithin=createTimeSpan(0,0,0,3)}); writeOutput(q3.ColumnList & '<br>');//returns "FOOBAR" (good) writeOutput(q3.foobar);//CF2016 Update 1 and 3 return "bar" (bad). CF11 returns "foo" (good). </cfscript> Thanks!, -Aaron
Comment by Aaron N.
533 | July 11, 2017 09:40:34 PM GMT
+1 - After filing CF-4170628, I simply didn't test further to find this issue. I no longer use(d) cached queries in CF2016. Use of cached queries in CF2016 can cause data corruption. Spread the word. Have a Public Beta for Aether.
Vote by Aaron N.
541 | July 11, 2017 09:42:05 PM GMT
Please note: I was NOT able to reproduce the specific issue of "If you add columns....they become a permanent part of the initial cached query". For me, appending columns to a query result never affected the original query's schema. Is anyone else seeing appended columns becoming part of the original query's schema? Thanks!, -Aaron
Comment by Aaron N.
534 | July 11, 2017 09:45:59 PM GMT
Hi All, There are two issues reported in this bug: 1. "If you modify values post query....they become a permanent part of the initial cached query". 2. "If you add columns....they become a permanent part of the initial cached query". I am able to repro issue #1 and fixed it. We are in the process of testing the fix. Please let us know if you need early access to the patch. However, I am unable to repro issue #2. -Nimit
Comment by Nimit S.
535 | July 12, 2017 05:21:10 AM GMT
James/Aaron, This is a duplicate of bug #CF-4198816. Please check and let me know so that I can close this bug as a duplicate. I am tracking this issue as part of bug #CF-4198816.
Comment by Nimit S.
536 | July 12, 2017 05:31:02 AM GMT
I asked Sandip Halder and he responded on 7/3/2017 that this bug hadn't been reported yet. The status of #CF-4198816 is "to test". Is that better than "to fix"? (Prior to reporting, I did search and didn't find the other reported bug.) If these are duplicates, feel free to close this one.
Comment by James M.
537 | July 12, 2017 01:39:40 PM GMT
Thanks James for replying. "To Test" means it has been fixed and it is in a testing phase. So, I am closing this bug. I will update the original bug whenever the patch is available.
Comment by Nimit S.
538 | July 12, 2017 01:49:14 PM GMT
Hi Nimit, Ah, yes, I hadn't seen CF-4198816. I've just went and voted for that one. Thanks for the fast resolution!!, -Aaron
Comment by Aaron N.
539 | July 12, 2017 09:20:25 PM GMT
This is just a quick follow-up. Even though this reported bug is similar to the other one, it was discovered afterwards that the notation I reported was still broken even though the other reported bug was "fixed". (I used "QueryName.QueryField[Number]" instead of only updating the first query row using "QueryName.QueryField".) CF-4198816 and this bug now both fixed.
Comment by James M.
540 | August 04, 2017 10:01:41 PM GMT