SQL Generation Bug: Variable state leakage in findAll(include="...") when mixed nested and shallow joins are used

[mikegrogan]

Describe the bug
When passing a multi-association string to the include argument of findAll(), if a deeply nested association (e.g., relation(nestedRelation)) is placed after standard shallow/flat associations in the comma-delimited string, CFWheels leaks the inner-join constraint conditions backward into preceding sibling join structures.

This generates invalid SQL containing orphan aliases inside earlier LEFT OUTER JOIN clauses, resulting in a database exception (e.g., ORA-00904: invalid identifier in Oracle or equivalent scope errors in other database drivers).

To Reproduce
Steps to reproduce the behavior:

// Main Model: adminGroupEntityStaffAssignment
hasOne(name="admingrouprole", foreignKey="groupid,roleid");
hasOne(name="adminrole", foreignKey="roleid");
hasOne(name="adminStaff", foreignKey="personnumber"); // maps to admin$staff table

// Associated Model: adminStaff
hasOne(name="ubperson", foreignKey="person_number", joinKey="personnumber");

Broken Execuation order

var result = model("adminGroupEntityStaffAssignment").findAll(
    include = "admingrouprole,adminrole,adminStaff(ubperson)"
);

Generated SQL Output showing the Leak:
Note how "ADMIN$STAFF"."PERSONNUMBER" = "PERSON_PRIM"."PERSON_NUMBER" is erroneously injected into the unrelated ADMIN$GROUPROLES and ADMIN$ROLE join definitions before ADMIN$STAFF is actually introduced to the query scope:

SELECT ... 
FROM "ADMIN$GROUPENTITYSTAFFASSIGNMENT" 
LEFT OUTER JOIN ("ADMIN$GROUPROLES" INNER JOIN "PERSON_PRIM" ON "ADMIN$STAFF"."PERSONNUMBER" = "PERSON_PRIM"."PERSON_NUMBER") ON ...
LEFT OUTER JOIN ("ADMIN$ROLE" INNER JOIN "PERSON_PRIM" ON "ADMIN$STAFF"."PERSONNUMBER" = "PERSON_PRIM"."PERSON_NUMBER") ON ... 
LEFT OUTER JOIN ("ADMIN$STAFF" INNER JOIN "PERSON_PRIM" ON "ADMIN$STAFF"."PERSONNUMBER" = "PERSON_PRIM"."PERSON_NUMBER") ON ...

Current Workaround
Simply rearranging the string so the nested property runs first isolates the query generation array cleanly:

// This works perfectly without SQL leakage
include = "adminStaff(ubperson),admingrouprole,adminrole"

Expected behavior
Should return the joins without the leakage

Working Code fix to review
Review the for loop starting at

wheels/vendor/wheels/model/sql.cfc

Line 179 in fc59492

for (local.i = 1; local.i <= ArrayLen(local.outerJoins); local.i++) {

Replace it with this new code

for (local.i = 1; local.i <= ArrayLen(local.outerJoins); local.i++) {
  local.outerJoin = local.outerJoins[local.i];
  
  // If we have inner joins, we need to group them in the outer join
  if (ArrayLen(local.innerJoins) > 0) {
    // Find the table being joined in the outer join
    local.joinTableMatch = ReFindNoCase("LEFT OUTER JOIN ([^\s]+)", local.outerJoin, 1, true);
    if (ArrayLen(local.joinTableMatch.pos) >= 2 && local.joinTableMatch.pos[2] > 0) {
      local.joinTable = Mid(local.outerJoin, local.joinTableMatch.pos[2], local.joinTableMatch.len[2]);
      
      // Track if an inner join actually belongs to this specific outer join scope
      local.hasApplicableInnerJoin = false;
      local.groupedInner = "(" & local.joinTable;
      
      for (local.j = 1; local.j <= ArrayLen(local.innerJoins); local.j++) {
        // CLEANUP FIX: Only inject the inner join if it references the current parent outer join table or the table alias
        // We strip quotes from the joinTable comparison to guarantee a match across database adapters
        local.cleanJoinTable = Replace(local.joinTable, '"', '', 'all');
        
        if (FindNoCase(local.cleanJoinTable, local.innerJoins[local.j])) {
          local.groupedInner &= " " & local.innerJoins[local.j];
          local.hasApplicableInnerJoin = true;
        }
      }
      local.groupedInner &= ")";
      
      // Only rewrite the SQL clause if a relevant sub-join was actually discovered
      if (local.hasApplicableInnerJoin) {
        local.outerJoin = Replace(local.outerJoin, "LEFT OUTER JOIN " & local.joinTable, "LEFT OUTER JOIN " & local.groupedInner);
      }
    }
  }
  
  local.rv = ListAppend(local.rv, local.outerJoin, " ");
}

Now when I run this (nested join at the end), it still returns the expected results.

var result = model("adminGroupEntityStaffAssignment").findAll(
    include = "admingrouprole,adminrole,adminStaff(ubperson)"
);

I'm hoping this new code could be reviewed and possibly submitted as a bug fix. Thanks.