Maxrows Issue in CFQuery?

A problem has recently arisen on our servers that is driving bit mad, and I am curious if it is related to the recently released Hotfix 2. Basically the problem seems to be that the maxrows attribute in cfquery isn't differentiated when ColdFusion is using a pooled statement (the max pooled statements is in the advanced settings of your dsn). What this means is that if you have a table query that returns say 15 rows, but you set a maxrows of 3 and then run the same query without a maxrows attribute or with a maxrows set to -1 (i.e. all), you will still only see 3 records returned.Take the following example:

<cfquery name="test" datasource="www"> SELECT      n.newsID, n.siteID FROM      tblNews n WHERE siteID = <cfqueryparam value="starwars" cfsqltype="cf_sql_varchar"> </cfquery> <P>
<p><cfoutput>#test.recordCount#</cfoutput></p> <P>
<cfquery name="test" datasource="www" maxrows="-1"> SELECT      n.newsID, n.siteID FROM      tblNews n WHERE siteID = <cfqueryparam value="starwars" cfsqltype="cf_sql_varchar"> </cfquery> <P>
<p><cfoutput>#test.recordCount#</cfoutput></p> <P>
<cfquery name="test" datasource="www" maxrows="3"> SELECT      n.newsID, n.siteID FROM      tblNews n WHERE siteID = <cfqueryparam value="starwars" cfsqltype="cf_sql_varchar"> </cfquery> <P>
<p><cfoutput>#test.recordCount#</cfoutput></p> <P>
<cfquery name="test" datasource="www"> SELECT      n.newsID, n.siteID FROM      tblNews n WHERE siteID = <cfqueryparam value="starwars" cfsqltype="cf_sql_varchar"> </cfquery> <P>
<p><cfoutput>#test.recordCount#</cfoutput></p> <P>
<cfquery name="test" datasource="www" maxrows="-1"> SELECT      n.newsID, n.siteID FROM      tblNews n WHERE siteID = <cfqueryparam value="starwars" cfsqltype="cf_sql_varchar"> </cfquery> <P>
<p><cfoutput>#test.recordCount#</cfoutput></p>

On the first run, this returned:

391 <P>
391 <P>
3 <P>
3 <P>
3

Notice how the last two returned only the 3 rows even though they are identical to the first two queries. On a second pass, all the queries will only return 3. The only way to change this appears to be either 1) set an different explicit max rows number other than -1 or 2) set the max pooled statements for the DSN to 0 temporarily to reset it. The problem with solution 1 is rather obvious, and solution 2 only works until you use the maxrows attribute.

I know this code has worked in the past, and the unless it is somehow specific to all of our servers, the only other potential culprit at this point seems to be HotFix 2. In HotFix 2 they did make a change related to maxrows:

61508 -   cfquery, cfstoredproc maxrows not passed to the JDBC driver (performance issue on large tables)

Could that be what has changed? Has anyone else run into this issue? I may be dense, but I cannot seem to figure out how you uninstall a hotfix, any suggestions? Just to note, I am running this against MS SQL Server 2000.

You're right, it is HotFix 2. I ran your test on my dev box that was hotfix l, results ok. Then I downloaded hotfix 2 and installed and got the results you are experiencing. In my case, I tested it against Oracle and I'm using Enterprise edition in the multiserver configuration.

Though I really don't like this solution, if you don't use cfqueryparam, the error doesn't occur. I'm still digging into it.

What kind of database are you using? MySQL has a LIMIT clause that you can insert into your statement that acts the same as maxrows: SELECT

Sorry, I was going to add: SELECT n.newsID, n.siteID FROM tblNews n WHERE siteID = <cfqueryparam value="starwars" cfsqltype="cf_sql_varchar"> LIMIT 3