The filterMany puts predicate in ON clause before the filtered table is joined, causing SQLSyntaxErrorException

[JoshuaForssmanNedap]

Our database version is MySql 8.0.43

After upgrading from Ebean 17.1.1 to 17.2.x, we see a possible problem when the filterMany references another association (e.g. archetypeConcept.modules.uuid.in(...)) (e.g. archetypeConcept.modules.uuid.in(...)), Ebean generates invalid SQL. The error I'm getting is SQLSyntaxErrorException: Unknown column 't3.uuid' in 'on clause'.

The predicate t3.uuid in (?) is placed in the ON clause of an earlier join (archetype_version / t2) instead of in the WHERE clause, so it references table t3 (module) before that table is joined, which causes execution to fail. In 17.1.1 the same predicate was correctly emitted in the WHERE clause.

Code path:

// 1. Build query with fetches on a many-to-many and a nested association
QArchetypeCategory query = new QArchetypeCategory()
    .archetypeConcepts.fetch()
    .archetypeConcepts.currentVersion.fetch();

// 2. Apply module filter via filterMany on archetypeConcepts using a sub-query that filters on concept.modules.uuid
QueryUtil.withFilteredModules(moduleUuids, query);  // see implementation below

// 3. This throws (wrong SQL: predicate on module.uuid appears in ON clause before module is joined)
List<ArchetypeCategory> categories = query.findList();

What QueryUtil.withFilteredModules(moduleUuids, query) does for QArchetypeCategory

public static void withFilteredModules(List<UUID> uuids, QArchetypeCategory query) {
    QArchetypeConcept qArchetypeConcept = new QArchetypeConcept();
    withFilteredModules(uuids, qArchetypeConcept);   // qArchetypeConcept.modules.uuid.in(uuids)

    if (uuids != null) {
        query.archetypeConcepts.isNotNull();
        query.archetypeConcepts.filterMany(qArchetypeConcept.getExpressionList());
    }
}

Generated Query before the upgrade(the working one):

select /* ArchetypeCategoryResource.getArchetypeCategories */
  distinct t0.id, t0.name, t0.created_at, t0.updated_at,
  t1.id, t1.archetype_concept_id, t1.root_node, t1.enabled,
  t1.global_archetype, t1.create_authorization_active, t1.read_authorization_active, t1.update_authorization_active,
  t1.singleton, t1.created_at, t1.updated_at,
  t4.id,
  t2.id, t2.archetype_id, t2.hashed_archetype, t2.archetype_concept_id, t2.created_at, t2.updated_at
from archetype_category t0
  left join archetype_category_content t1z_ on t1z_.archetype_category_id = t0.id
  left join archetype_concept t1 on t1.id = t1z_.archetype_concept_id
  left join archetype_settings t4 on t4.archetype_concept_id = t1.id
  left join archetype_version t2 on t2.id = t1.current_version_id
  left join module_content t3z_ on t3z_.archetype_concept_id = t1.id
  left join module t3 on t3.id = t3z_.module_id where exists (select 1 from archetype_category_content x where x.archetype_category_id = t0.id) and (t1.id is null or (t3.uuid in (?))) order by t0.id;

t3 is joined first, then the predicate (t1.id is null or (t3.uuid in (?))) appears in the WHERE clause.

Generated Query after the upgrade(not working):

select /* ArchetypeCategoryResource.getArchetypeCategories */
  distinct t0.id, t0.name, t0.created_at, t0.updated_at,
  t1.id, t1.archetype_concept_id, t1.root_node, t1.enabled,
  t1.global_archetype, t1.create_authorization_active, t1.read_authorization_active, t1.update_authorization_active,
  t1.singleton, t1.created_at, t1.updated_at,
  t4.id,
  t2.id, t2.archetype_id, t2.hashed_archetype, t2.archetype_concept_id, t2.created_at, t2.updated_at
from archetype_category t0
  left join archetype_category_content t1z_ on t1z_.archetype_category_id = t0.id
  left join archetype_concept t1 on t1.id = t1z_.archetype_concept_id
  left join archetype_settings t4 on t4.archetype_concept_id = t1.id
  left join archetype_version t2 on t2.id = t1.current_version_id and t3.uuid in (?)  (HERE)
  left join module_content t3z_ on t3z_.archetype_concept_id = t1.id
  left join module t3 on t3.id = t3z_.module_id where exists (select 1 from archetype_category_content x where x.archetype_category_id = t0.id) order by t0.id

The and t3.uuid in (?) appears in the ON clause of the archetype_version join (t2) before t3 is joined.
Let me know if there is anything else I can add for more info.