tracker issue : CF-3820493

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

CFLOOP on large query recordset with more than 65534 records only processes first 65534 records in query

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Claire Harmony / Claire Harmony (Claire Harmony)

Created: 09/08/2014

Components: Database

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Major / All users will encounter

Locale/System: ALL / Win 2008 Server R2 64 bit

Vote Count: 0

Listed in the version 11.0.03.292480 Issues Fixed doc 
Problem Description:I run a query that returns several hundred thousand records.  Then I do a CFLOOP on the returned recordset and the loop only processes the first 65534 records and then stops.

Steps to Reproduce:Create a CFQUERY that returns several hundred thousand records.  Run a CFLOOP on it and you will see that it stops looping after 65534 records

Actual Result:

Expected Result:

Any Workarounds:  No workaround found, please fix it

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3820493

External Customer Info:
External Company:  
External Customer Name: Quinnster
External Customer Email:  
External Test Config: My Hardware and Environment details:  Windows Server 2008 R2 Standard 64 bit, Coldfusion 11.

Attachments:

Comments:

Note, the script runs just fine on CF 9, the problem is not observed there.
Comment by External U.
10964 | September 08, 2014 07:37:46 PM GMT
Hi, I tried replicating the issue using the below mentioned code snippet: SQL code: create table bugCF-3820493 ( id int identity(1,1), name varchar(20) ) DECLARE @a INT SET @a = 1 WHILE (@a <=500000) BEGIN insert bugCF-3820493(name) values('Code') SET @a = @a + 1 END GO select * from bugCF-3820493 CFML code: <cfquery datasource="Bugs" name="q1" result="rs"> select * from bugCF-3820493 </cfquery> <cfloop query="q1"> <cfoutput>#ID# - #name#</cfoutput><br> </cfloop> But it is rendering all 500,000 record without any issue. Please let me know if I am missing something. Can you also share your repro case? Regards, Nimit
Comment by Nimit S.
10965 | September 09, 2014 05:15:09 AM GMT
Try it like this: <CFOUTPUT> <CFQUERY name="GetIPtoCountry" datasource="mydb"> SELECT * FROM iptocountry ORDER BY ip_from, ip_to, iso2; </CFQUERY> Records=#GetIPtoCountry.recordcount#<br> <!---It writes 296000 here---> <CFLOOP Query="GetIPtoCountry"> #GetIPtoCountry.currentRow# <CFFLUSH> </CFLOOP> </CFOUTPUT>
Comment by External U.
10966 | September 09, 2014 06:01:52 AM GMT
Hi, It looks like it is happening because of <cfflush> tag. Can you please remove <cfflush> for testing and see if it works for you? Regards, Nimit
Comment by Nimit S.
10967 | September 09, 2014 07:13:45 AM GMT
I removed all <CFFLUSH> commands and the problem still happens. It only loops through record 65535 to 65534 as previously reported.
Comment by External U.
10968 | September 09, 2014 07:37:41 AM GMT
I was unable to verify this on CF11 Update 3 (11,0,03,292245(PreRelease)) w/ a 544925-row MSSQL table (w/ and w/o <cfflush>). Case 1 (successfully output all 544925 IDs): <CFOUTPUT> <CFQUERY name="q"> SELECT * FROM myTable </CFQUERY> Records=#q.RecordCount#<br> <!---It writes 544925 here---> <CFLOOP Query="q"> #q.currentRow# <CFFLUSH> </CFLOOP> </CFOUTPUT> Case 2 (successfully output all 544925 IDs): <CFOUTPUT> <CFQUERY name="q" datasource="ItisdesignSQL"> SELECT * FROM itisesCounter </CFQUERY> Records=#q.RecordCount#<br> <!---It writes 544925 here---> <CFLOOP Query="q"> #q.currentRow# <!---CFFLUSH---> </CFLOOP> </CFOUTPUT> Thanks!, -Aaron
Comment by External U.
10969 | November 24, 2014 03:52:01 AM GMT
I have just re-tested this in CF 11 update 3, and the problem appears to be fixed now. Thanks Adobe.
Comment by External U.
10970 | November 24, 2014 02:29:33 PM GMT
Thanks Quinnster for confirming. Hence, I am closing this bug.
Comment by Nimit S.
10971 | November 26, 2014 12:26:26 AM GMT