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

Regimentals

You have no attributes on the inner cfloop tag, is that valid syntax? – Monte

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. – Regimentals

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? – Tobytobye

@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? – Regimentals

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. – Tobytobye

Answer 1

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)

Association

Sorry about the missing data. And boy, can't believe I've found a bug. I originally had fixed it with cfoutput as well (see OP), but preferred the second option on this specific page as there is both code above and below this that otherwise required lots of opening/closing of cfoutput to avoid nesting configuration errors. That's one of the reasons I'm digging the cfloop group in general. I'll file this as a bug and link back here and update the original post as well. – Regimentals

No worries re data. Your question was a helluva lot better-worded than most of the ones asked here recently. Just a suggestion for next time, that's all: the more self-contained & free-standing your repro case is, the easier it is for us to copy/paste & run it ourselves. – Association