CF10 vs CF2018 unintentional query cache

[zpm007]

I've been working on migrating code from CF10 to CF2018.  I just noticed that in CF2018, my queries are unintentionally using a CF-like query cache.  This is noticable everytime I alter the table structure, my CF2018 session is still referencing a cached version of the query.  This happens even when I try to clear the query cache in CFADMIN, it doesnt affect the results.  Different behavior is produced with CF10 (see below).  If I add a comment to the query, it returns the correct results.  Is there a default query cache option enabled with CF2018?

My Code:

<cfquery name=”qTest”>

select * from users

</cfquery>

<cfdump var=”#qTest#”>

CF2018 Results:

Displays table columns names correctly

Cache is false

CF10 Results:

Displays table columns names correctly

Cache is false

Then I rename a column in oracle, and run the same page

<cfquery name=”qTest”>

select * from users

</cfquery>

<cfdump var=”#qTest”>

CF2018 Results:

Does not display new column name

Cache is false

CF10 Results:

Displays table columns correctly

Cache is false

If I add a comment to the query, things work

<cfquery name=”qTest”>

select * from users

--test1234

</cfquery>

<cfdump var=”#qTest”>

CF2018 Results:

Displays table columns names correctly

Cache is false

CF10 Results:

Displays table columns names correctly

Cache is false

[Dave Watts]

There are all sorts of caching going on. I suspect that, in this case, it's not the results of the query that are cached but rather the prepared statement that's sent to the database and the other stuff related to that prepared statement. This, by the way, is one of the reasons you shouldn't use SELECT *, because it's implicitly saying you don't know what you're going to get back!

So, when you query the database, CF doesn't send the literal string "SELECT * FROM whatever", it builds something called a prepared statement, and that statement goes into a pool. And other stuff may go into the same pool - specifically in this case, whatever column names you get back when you use "SELECT * ...".

Once you change the actual SQL, the prepared statement has to be generated from scratch, and whatever information was related to that in the JDBC pool is also generated from scratch.

I'm probably oversimplifying things a bit here, but you might find this interesting:

https://docs.oracle.com/cd/E13222_01/wls/docs81/ConsoleHelp/jdbc_connection_pools.html#1107805

You can check this pretty quickly by changing one of the field values in the database, rather than one of the column names. I'll bet you get the right values when you output the query.

Dave Watts, Eidolon LLC

[Charlie Arehart]

Dave's definitely on the right track. And I will add a couple of more points:

• it may also be caching in the DBMS, where it's caching a query plan for the given SQL statement (which is perhaps not being updated simply because you add a column). So there are ways to tell the DBMS to either clear the plan cache for that one query, or for all queries in a given database, or for all queries in the entire DB (which is "throwing the baby out with the bathwater")
• your issue is 100% not about CF query caching, because you can see that the debug output you show indicates "false" for that in all cases.
• Finally, FWIW, there is no setting in the CF Admin to "clear the query cache". There is only the option to set the number of cached results (on the CF Admin "caching" page, under "max number of cached queries"