Nested cfloops with less records than outer loop cause "array index out of range" error

Question

I'm very curious why this is happening. I've run into it twice now, and after a ton of googling/so'ing, I haven't found any reason I actually understand. The gist of it:

Query 1: selectContent (6 records; no blanks/nulls etc)

Query 2: selectPricing (5 records; no blanks/nulls etc)

Output:

<cfloop query="selectContent">
    <section>
        #selectContent.h2#
        <cfif selectContent.id eq 3>
            <cfloop query="selectPricing" group="groupCol">
                <table class="pricing">
                <thead>
                    <tr>
                        <th>#description#</th>
                        <th>Price</th>
                    </tr>
                </thead>
                <tbody>
                    <cfloop>
                    <tr>
                        <td>#selectPricing.description#</td>
                        <td>#selectPricing.price#</td>
                    </tr>
                    </cfloop>
                </tbody>
                </table>
            </cfloop>
        </cfif>
        #selectContent.content#
    </section>
</cfloop>

This will give the following error: Array index out of range: 5

The error only occurs when the second query has less records than the first. Essentially it feels like that first cfloop takes over the loop iteration from that second one and this causes the issue, but also only if you have that third grouped cfloop in there. The entire inner cfloop runs, as is there in the source.

I've come up with two ways to resolve this:

do this with cfoutput/group, but that's relatively ugly as it means lots of closing of cfoutputs from other parts of the page.
stick a cfbreak on that third cfloop if the currentRow matches the recordcount.

So, two questions:

Why is this even happening?
Should I be using a totally different approach here (the fact that googling/so'ing isn't finding others with this issue certainly seems to imply that...)?

EDIT I've filed this as a Coldfusion bug based on Adam Cameron's feedback below. Bug #3820049

You have no attributes on the inner cfloop tag, is that valid syntax? — Busches
Yes. That loops through the grouped results, ie if your query was grouped by gender, that would return all names by gender (see bennadel.com/blog/…). This works if you don't have it within that first cfloop (before the grouped one). It should basically work the same as cfoutput group, which is what Adam uses below as a workaround, and you can see essentially the same syntax used there. — sckd
Should I be using a totally different approach here I am curious why multiple queries and nested loops as opposed to a single query with a JOIN? — Leigh
@Leigh, I originally had the first query (selectContent) as a larger one with a join. But in the tests I ran it was way faster (like 90ms :)) to split up the query in two. So I did that, made a whole ton of other edits, then moved the pricing into the content and got the error. I could probably have gotten everything in one query, but I'm not sure what value that would add. I think I'd end up returning a lot of data I didn't need since parts of rows would be replicated, and I can't see how I'd actually manage to output it without still resorting to a few inner loops with grouping? — sckd
Usually, the gains are simplicity and reduced database calls. You are only displaying a few columns, so it does not seem like the SQL should be that complex. Adding the proper JOIN usually does not add a significant amount of time, but a lot depends on the tables and indexes involved, as well how the sql is constructed. Hard to say more without seeing it. — Leigh

Adam Cameron Adam Cameron · Accepted Answer · 2014-09-08T10:22:21

Well done, you've found a bug in CF. I can replicate it (PS... it'd've been cool had you included some sample data save me having to do it!)

The work-around is straight forward though:

<cfscript>
selectContent = queryNew("h2,id,content", "varchar,integer,varchar", [
    ["one", 1, "content.1"],
    ["two", 2, "content.2"],
    ["three", 3, "content.3"],
    ["four", 4, "content.4"],
    ["five", 5, "content.5"],
    ["six", 6, "content.6"],
    ["seven", 7, "content.7"]
]);

selectPricing = queryNew("groupCol,description,price", "varchar,varchar,varchar", [
    ["groupCol.1", "description.1", "1.11"],
    ["groupCol.2", "description.2", "2.22"],
    ["groupCol.2", "description.3", "3.33"],
    ["groupCol.3", "description.4", "4.44"],
    ["groupCol.3", "description.5", "5.55"],
    ["groupCol.3", "description.6", "6.66"]
]);

</cfscript>
<cfloop query="selectContent">
    <section>
        <cfoutput>#selectContent.h2#</cfoutput>
        <cfif selectContent.id eq 3>
            <cfoutput query="selectPricing" group="groupCol">
                <table class="pricing">
                <thead>
                    <tr>
                        <th>#description#</th>
                        <th>Price</th>
                    </tr>
                </thead>
                <tbody>
                    <cfoutput>
                    <tr>
                        <td>#description#</td>
                        <td>#price#</td>
                    </tr>
                    </cfoutput>
                </tbody>
                </table>
            </cfoutput>
        </cfif>
        <cfoutput>#selectContent.content#</cfoutput>
    </section>
</cfloop>

Note how I've used <cfoutput> to do the inner looping.

This is a serious bug in ColdFusion (10 and 11), and you should raise it on their bug base (if you do, report the ticket number/URL back here so we can vote on it)

Nested cfloops with less records than outer loop cause "array index out of range" error

1 Answers