Optimization of fetch strategy breaks filterMany

[cgaunet]

Expected behavior

When querying a big chunk of data, ebean will optimize the fetch strategy.
For this java code:

database.find(SqlCollection::class.java)
            ...
            .fetch("collectionDesigns.design.templates.simulations")
            .filterMany("collectionDesigns.design.templates.simulations")
            .eq("background.productFormat", ProductType.DOUBLE_SIDED.displayName)
            ...

And around 600 simulations for one collection, it will optimize as a query join mode and thus should produce:

select t0.id, t0.preview_url, t1.url, t1.template_id, t1.simulation_background_id from template t0 left join template_simulation_background t1 on t1.template_id = t0.id left join simulation_background t2 on t2.id = t1.simulation_background_id where (t0.design_id) = any(?) and t0.active = ? and t2.product_type = ? order by t0.id;

Doing so it works most of the time (when batching isn't involved), but with this query and batching involved, I'm getting an error with PSQL, which is normal because ebean won't replace the object properties by the corresponding SQL.

Actual behavior

Produced "SQL" query (notice the simulations.simulations.background.productFormat = ? at the end)

t0.id, t0.preview_url, t1.url, t1.template_id, t1.simulation_background_id from template t0 left join template_simulation_background t1 on t1.template_id = t0.id where (t0.design_id) = any(?) and t0.active = ? and simulations.simulations.background.productFormat = ? order by t0.id

Steps to reproduce

Having around 600 simulations for one collection, it will optimize as a query join mode

database.find(SqlCollection::class.java)
            ...
            .fetch("collectionDesigns.design.templates.simulations")
            .filterMany("collectionDesigns.design.templates.simulations")
            .eq("background.productFormat", ProductType.DOUBLE_SIDED.displayName)
            ...

ERROR: cross-database references are not implemented: simulations.simulations.background.productformat

More information

I managed to "fix" the problem by explicitly setting FetchConfig.ofQuery() when I fetch the simulations which is why I think the problem comes from the optimization done by ebean when it sees that ofQuery is better in terms of performance the a classical join.

By the way, thanks for your amazing work, this ORM is a game changer in the java ecosystem 🙏👏

[rbygrave]

I think we will need an example that reproduces this issue. Are you able to build a failing test case? Perhaps as a fork of https://github.com/ebean-orm-examples/example-minimal ?