Fix OrmQueryProperties.filterManyHasNestedProperty() with real nested property check

filterMany() expressions referencing a nested/dotted property (e.g.
.eq("group.name", "x"), alone or mixed with root properties) were
attached to a LEFT JOIN's ON clause via the "extra join" mechanism.

That only nulls the extra join's own columns on non-match - it does
not exclude the parent/many-side row, so the predicate had no actual
filtering effect (confirmed by data, not just SQL shape).

## Fix:

force any many-property fetch whose filterMany expression
references a nested property onto a query-join (secondary select
restoring filterMany's original always-fetchQuery behaviour for this
with a genuine WHERE clause) instead of leaving it as an inline JOIN, case.

- SpiExpressionValidation: track all visited property names (allProperties()), not just unknown ones.
- OrmQueryProperties: filterManyHasNestedProperty() walks the filterMany expression for any dotted property reference.
- OrmQueryDetail.markQueryJoins(): route nested-property filterMany chunks to markForQueryJoin() instead of the inline fetch-join slot, without consuming that slot for other many-paths.

Also simplifies the earlier root-only-predicate join-placement fix
(CQueryPredicates/DefaultDbSqlContext/SqlTreeNodeManyRoot) now that
a nested-property filterMany can never reach the JOIN-based code
path: removed the now-unreachable "deepest path" fallback branch,
the includeFilterMany() idempotency guard, and the redundant fallback
call in SqlTreeNodeManyRoot - isFilterManyAttachPoint() remains as
the single, always-used attach mechanism.

Updated TestQueryFilterMany/TestQueryFilterManySimple assertions to
expect the corrected query-join (2 statement) behaviour.

ebean-api/src/main/java/io/ebean/config/dbplatform/DatabasePlatform.java

@@ -162,6 +162,18 @@ public class DatabasePlatform {
   protected boolean selectCountWithAlias;
   protected boolean selectCountWithColumnAlias;
 
+  /**
+   * Set true for platforms where {@code exists(...)} can only be used as a predicate
+   * and not as a directly selectable scalar boolean expression (e.g. SQL Server, Oracle).
+   */
+  protected boolean existsWithCaseWhen;
+
+  /**
+   * Clause appended after the {@code case when exists(...) then 1 else 0 end} exists query
+   * for platforms that require a FROM clause on every select (e.g. {@code from dual} on Oracle).
+   */
+  protected String existsFromClause = "";
+
   /**
    * If set then use the FORWARD ONLY hint when creating ResultSets for
    * findIterate() and findVisit().
@@ -660,6 +672,21 @@ public class DatabasePlatform {
     return selectCountWithColumnAlias;
   }
 
+  /**
+   * Return true if a scalar boolean {@code exists(...)} expression is not supported
+   * as a select expression and needs to be wrapped as {@code case when exists(...) then 1 else 0 end}.
+   */
+  public boolean existsWithCaseWhen() {
+    return existsWithCaseWhen;
+  }
+
+  /**
+   * Return the clause to append after the exists case-when wrapping (e.g. {@code from dual} on Oracle).
+   */
+  public String existsFromClause() {
+    return existsFromClause;
+  }
+
 
   public String completeSql(String sql, Query<?> query) {
     if (query.isForUpdate()) {

ebean-core/src/main/java/io/ebeaninternal/api/SpiExpressionValidation.java

@@ -12,6 +12,7 @@ public final class SpiExpressionValidation {
 
   private final BeanType<?> desc;
   private final LinkedHashSet<String> unknown = new LinkedHashSet<>();
+  private final LinkedHashSet<String> all = new LinkedHashSet<>();
 
   public SpiExpressionValidation(BeanType<?> desc) {
     this.desc = desc;
@@ -21,6 +22,7 @@ public final class SpiExpressionValidation {
    * Validate that the property expression (path) is valid.
    */
   public void validate(String propertyName) {
+    all.add(propertyName);
     if (!desc.isValidExpression(propertyName)) {
       unknown.add(propertyName);
     }
@@ -33,4 +35,14 @@ public final class SpiExpressionValidation {
     return unknown;
   }
 
+  /**
+   * Return the set of all property names visited during this validation, regardless of
+   * whether they were considered valid against the bean type. Used to inspect the shape of
+   * an expression (for example, to check whether it references any associated/joined path)
+   * without needing a correctly-typed bean descriptor.
+   */
+  public Set<String> allProperties() {
+    return all;
+  }
+
 }

ebean-core/src/main/java/io/ebeaninternal/server/deploy/DbSqlContext.java

@@ -181,4 +181,11 @@ public interface DbSqlContext {
    * Include the filter many predicates if specified into the JOIN clause.
    */
   void includeFilterMany();
+
+  /**
+   * Return true if the given fetch path (relative to the query root) is the exact join clause
+   * that the pending filterMany predicate must be attached to - i.e. the deepest path the
+   * filterMany expression itself references.
+   */
+  boolean isFilterManyAttachPoint(String prefix);
 }

ebean-core/src/main/java/io/ebeaninternal/server/query/CQueryBuilder.java

@@ -321,6 +321,46 @@ final class CQueryBuilder {
     return lastFound;
   }
 
+  /**
+   * Find the index of the top-level (non-nested) "select" keyword in sql. Used to detect if sql
+   * starts with a WITH clause (CTE) header - SQL Server does not support a WITH clause nested
+   * inside a subquery/derived table, so it must be hoisted in front of a wrapping SELECT
+   * (count/exists) rather than wrapped along with the rest of the query.
+   * <p>
+   * Returns 0 if there is no leading WITH clause (sql starts directly with SELECT), or -1 if no
+   * top-level SELECT is found at all.
+   */
+  static int topLevelSelectStart(String sql) {
+    int depth = 0;
+    int len = sql.length();
+    for (int i = 0; i < len; i++) {
+      char c = sql.charAt(i);
+      if (c == '(') {
+        depth++;
+      } else if (c == ')') {
+        depth--;
+      } else if (depth == 0 && sql.regionMatches(true, i, "select", 0, 6)
+        && (i == 0 || !Character.isLetterOrDigit(sql.charAt(i - 1)))
+        && (i + 6 == len || !Character.isLetterOrDigit(sql.charAt(i + 6)))) {
+        return i;
+      }
+    }
+    return -1;
+  }
+
+  /**
+   * Split off a leading WITH clause (CTE header) from sql, returning {@code {header, body}} so the
+   * header can be hoisted in front of a wrapping SELECT. Returns an empty header (unchanged sql as
+   * the body) when there is no leading WITH clause.
+   */
+  static String[] splitCteHeader(String sql) {
+    int pos = topLevelSelectStart(sql);
+    if (pos <= 0) {
+      return new String[]{"", sql};
+    }
+    return new String[]{sql.substring(0, pos), sql.substring(pos)};
+  }
+
   static String inlineSqlCommentLabel(String label, ProfileLocation profileLocation, boolean secondary, String simpleName) {
     if (label != null) {
       return secondary ? label : CQueryPlan.planLabelWithType(label, simpleName);
@@ -329,15 +369,22 @@ final class CQueryBuilder {
   }
 
   private String wrapSelectCount(String sql) {
-    sql = "select count(*) from ( " + sql + ")";
+    String[] parts = splitCteHeader(sql);
+    sql = parts[0] + "select count(*) from ( " + parts[1] + ")";
     if (selectCountWithAlias) {
       sql += " as c";
     }
     return sql;
   }
 
-  private String wrapSelectExists(String sql) {
-    return "select exists(" + sql + ")";
+  static String wrapSelectExists(String sql, boolean existsWithCaseWhen, String existsFromClause) {
+    String[] parts = splitCteHeader(sql);
+    String header = parts[0];
+    String body = parts[1];
+    if (existsWithCaseWhen) {
+      return header + "select case when exists(" + body + ") then 1 else 0 end" + existsFromClause;
+    }
+    return header + "select exists(" + body + ")";
   }
 
   /**
@@ -366,7 +413,7 @@ final class CQueryBuilder {
     }
 
     SqlLimitResponse s = buildSql("select 1", request, predicates, sqlTree);
-    String sql = wrapSelectExists(s.getSql());
+    String sql = wrapSelectExists(s.getSql(), dbPlatform.existsWithCaseWhen(), dbPlatform.existsFromClause());
 
     queryPlan = new CQueryPlan(request, sql, sqlTree.plan(), predicates.logWhereSql());
     request.putQueryPlan(queryPlan);

ebean-core/src/main/java/io/ebeaninternal/server/query/CQueryPredicates.java

@@ -78,6 +78,11 @@ public final class CQueryPredicates {
    */
   private Set<String> predicateIncludes;
   private Set<String> orderByIncludes;
+  /**
+   * The fetch path (relative to the query root) of the many-root whose own join clause the
+   * filterMany-in-JOIN predicate is attached to
+   */
+  private String filterManyAttachPath;
 
   CQueryPredicates(Binder binder, OrmQueryRequest<?> request) {
     this.binder = binder;
@@ -222,6 +227,8 @@ public final class CQueryPredicates {
           filterMany = new DefaultExpressionRequest(request, deployParser, binder, filterManyExpr);
           if (buildSql) {
             dbFilterMany = filterMany.buildSql();
+            // safe as filterManyJoin only holds when the expression is root-property only -
+            filterManyAttachPath = manyProperty.path();
           }
         }
       }
@@ -399,6 +406,14 @@ public final class CQueryPredicates {
     return filterManyJoin ? dbFilterMany : null;
   }
 
+  /**
+   * Return the fetch path of the filterMany-in-JOIN predicate - the path whose own join clause
+   * the predicate must be appended to (or null if there is no filterMany-in-JOIN predicate at all).
+   */
+  String filterManyAttachPath() {
+    return filterManyJoin ? filterManyAttachPath : null;
+  }
+
   /**
    * Return the db column version of the order by clause.
    */

ebean-core/src/main/java/io/ebeaninternal/server/query/DefaultDbSqlContext.java

@@ -23,6 +23,7 @@ final class DefaultDbSqlContext implements DbSqlContext {
   private final ArrayStack<String> prefixStack = new ArrayStack<>();
   private final String fromForUpdate;
   private final String dbFilterManyJoin;
+  private final String filterManyAttachPath;
   private boolean useColumnAlias;
   private int columnIndex;
   private int asOfTableCount;
@@ -42,7 +43,8 @@ final class DefaultDbSqlContext implements DbSqlContext {
   private boolean joinSuppressed;
 
   DefaultDbSqlContext(SqlTreeAlias alias, String columnAliasPrefix, CQueryHistorySupport historySupport,
-                      CQueryDraftSupport draftSupport, String fromForUpdate, String dbFilterManyJoin) {
+                      CQueryDraftSupport draftSupport, String fromForUpdate, String dbFilterManyJoin,
+                      String filterManyAttachPath) {
     this.alias = alias;
     this.columnAliasPrefix = columnAliasPrefix;
     this.useColumnAlias = columnAliasPrefix != null;
@@ -51,6 +53,12 @@ final class DefaultDbSqlContext implements DbSqlContext {
     this.historyQuery = (historySupport != null);
     this.fromForUpdate = fromForUpdate;
     this.dbFilterManyJoin = dbFilterManyJoin;
+    this.filterManyAttachPath = filterManyAttachPath;
+  }
+
+  @Override
+  public boolean isFilterManyAttachPoint(String prefix) {
+    return dbFilterManyJoin != null && filterManyAttachPath != null && filterManyAttachPath.equals(prefix);
   }
 
   @Override

ebean-core/src/main/java/io/ebeaninternal/server/query/SqlTreeBuilder.java

@@ -108,7 +108,7 @@ public final class SqlTreeBuilder {
     CQueryHistorySupport historySupport = builder.historySupport(query);
     CQueryDraftSupport draftSupport = builder.draftSupport(query);
     String colAlias = subQuery || rootNode.isSingleProperty() ? null : columnAliasPrefix;
-    this.ctx = new DefaultDbSqlContext(alias, colAlias, historySupport, draftSupport, fromForUpdate, predicates.dbFilterManyJoin());
+    this.ctx = new DefaultDbSqlContext(alias, colAlias, historySupport, draftSupport, fromForUpdate, predicates.dbFilterManyJoin(), predicates.filterManyAttachPath());
   }
 
   /**

ebean-core/src/main/java/io/ebeaninternal/server/query/SqlTreeNodeBean.java

@@ -342,6 +342,10 @@ class SqlTreeNodeBean implements SqlTreeNode {
     if (desc.isSoftDelete() && temporalMode != SpiQuery.TemporalMode.SOFT_DELETED) {
       ctx.append(" and ").append(desc.softDeletePredicate(ctx.tableAlias(prefix)));
     }
+    if (prefix != null && ctx.isFilterManyAttachPoint(prefix)) {
+      // this node is where we inline the filterMany predicate
+      ctx.includeFilterMany();
+    }
     return sqlJoinType;
   }
 

ebean-core/src/main/java/io/ebeaninternal/server/query/SqlTreeNodeManyRoot.java

@@ -45,6 +45,5 @@ final class SqlTreeNodeManyRoot extends SqlTreeNodeBean {
   @Override
   public void appendFrom(DbSqlContext ctx, SqlJoinType joinType) {
     super.appendFrom(ctx, joinType.autoToOuter());
-    ctx.includeFilterMany();
   }
 }

ebean-core/src/main/java/io/ebeaninternal/server/querydefn/OrmQueryDetail.java

@@ -384,14 +384,18 @@ public final class OrmQueryDetail implements Serializable {
         OrmQueryProperties chunk = pair.getProperties();
         if (isQueryJoinCandidate(lazyLoadManyPath, chunk)) {
           // this is a 'fetch join' (included in main query)
-          if (fetchJoinFirstMany) {
+          BeanDescriptor<?> targetDescriptor = ((BeanPropertyAssoc<?>) elProp.beanProperty()).targetDescriptor();
+          if (fetchJoinFirstMany && !chunk.filterManyHasNestedProperty(targetDescriptor)) {
             // letting the first one remain a 'fetch join'
             fetchJoinFirstMany = false;
             manyFetchProperty = pair.getPath();
             chunk.filterManyInline();
             many = elProp;
           } else {
-            // convert this one over to a 'query join'
+            // convert this one over to a 'query join' - either because another many has already claimed the
+            // 'fetch join' slot, or because its filterMany references a property that requires crossing into
+            // an associated bean and can't safely be included as a JOIN predicate (see
+            // OrmQueryProperties.filterManyHasNestedProperty)
             chunk.markForQueryJoin();
           }
         }

ebean-core/src/main/java/io/ebeaninternal/server/querydefn/OrmQueryProperties.java

@@ -9,7 +9,10 @@ import io.ebean.util.SplitName;
 import io.ebeaninternal.api.SpiExpression;
 import io.ebeaninternal.api.SpiExpressionFactory;
 import io.ebeaninternal.api.SpiExpressionList;
+import io.ebeaninternal.api.SpiExpressionValidation;
 import io.ebeaninternal.api.SpiQuery;
+import io.ebeaninternal.server.deploy.BeanDescriptor;
+import io.ebeaninternal.server.el.ElPropertyValue;
 import io.ebeaninternal.server.expression.FilterExprPath;
 import io.ebeaninternal.server.expression.FilterExpressionList;
 
@@ -234,6 +237,26 @@ public final class OrmQueryProperties implements Serializable {
     return filterMany != null && !markForQueryJoin;
   }
 
+  /**
+   * Return true if the filterMany expression (if any) references a property that requires
+   * crossing into an associated bean/join - e.g. {@code "group.name"} - rather than only
+   * plain/embedded properties resolving to columns on the many bean's own base table.
+   */
+  boolean filterManyHasNestedProperty(BeanDescriptor<?> targetDescriptor) {
+    if (filterMany == null) {
+      return false;
+    }
+    SpiExpressionValidation validation = new SpiExpressionValidation(targetDescriptor);
+    filterMany.validate(validation);
+    for (String property : validation.allProperties()) {
+      ElPropertyValue elProp = targetDescriptor.elGetValue(property);
+      if (elProp != null && elProp.isAssocProperty()) {
+        return true;
+      }
+    }
+    return false;
+  }
+
   /**
    * Adjust filterMany expressions for inclusion in main query.
    */

ebean-core/src/test/java/io/ebeaninternal/server/query/CQueryBuilderTest.java

@@ -117,6 +117,88 @@ class CQueryBuilderTest {
     assertThat(countSql).isEqualTo("select count(*) from ( select t0.id from ad t0) as c");
   }
 
+  @Test
+  void topLevelSelectStart_noLeadingCte_returnsZero() {
+    String sql = "select 1 from o_order t0 where t0.id > ?";
+    assertThat(CQueryBuilder.topLevelSelectStart(sql)).isEqualTo(0);
+  }
+
+  @Test
+  void topLevelSelectStart_leadingCte_findsOuterSelect() {
+    // The only depth-0 "select" is the outer query - the CTE body's "select" is nested in parens.
+    String sql = "with order_totals as (" +
+      "  select o.id as order_id," +
+      "         sum(d.order_qty * d.unit_price) as total_amount" +
+      "  from o_order o" +
+      "  join o_order_detail d on d.order_id = o.id" +
+      "  group by o.id" +
+      ")" +
+      " select order_id, total_amount" +
+      " from order_totals" +
+      " where total_amount > ?";
+
+    int pos = CQueryBuilder.topLevelSelectStart(sql);
+    assertThat(sql.substring(pos)).startsWith("select order_id, total_amount");
+  }
+
+  /**
+   * SQL Server does not support a WITH clause (CTE) nested inside a subquery/derived table - see
+   * https://github.com/ebean-orm/ebean/issues/3848 (findCount() wraps raw sql in "select count(*)
+   * from ( ... )" which breaks when the raw sql is a CTE). The CTE header must be hoisted in front
+   * of the wrapping SELECT.
+   */
+  @Test
+  void splitCteHeader_hoistsLeadingWithClause() {
+    String sql = "with order_totals as (" +
+      "  select o.id as order_id," +
+      "         sum(d.order_qty * d.unit_price) as total_amount" +
+      "  from o_order o" +
+      "  join o_order_detail d on d.order_id = o.id" +
+      "  group by o.id" +
+      ")" +
+      " select order_id, total_amount" +
+      " from order_totals" +
+      " where total_amount > ?";
+
+    String[] parts = CQueryBuilder.splitCteHeader(sql);
+    assertThat(parts[0] + parts[1]).isEqualTo(sql);
+    assertThat(parts[0]).startsWith("with order_totals as (").endsWith(") ");
+    assertThat(parts[1]).isEqualTo("select order_id, total_amount from order_totals where total_amount > ?");
+  }
+
+  @Test
+  void splitCteHeader_noCte_returnsEmptyHeader() {
+    String sql = "select 1 from o_order t0 where t0.id > ?";
+    String[] parts = CQueryBuilder.splitCteHeader(sql);
+    assertThat(parts[0]).isEmpty();
+    assertThat(parts[1]).isEqualTo(sql);
+  }
+
+  @Test
+  void wrapSelectExists_default_usesScalarExists() {
+    String sql = CQueryBuilder.wrapSelectExists("select 1 from o_order t0 where t0.id > ?", false, "");
+    assertThat(sql).isEqualTo("select exists(select 1 from o_order t0 where t0.id > ?)");
+  }
+
+  /**
+   * SQL Server does not support exists(...) as a directly selectable scalar
+   * boolean expression - see https://github.com/ebean-orm/ebean/issues/3848
+   */
+  @Test
+  void wrapSelectExists_existsWithCaseWhen_wrapsAsCaseWhen() {
+    String sql = CQueryBuilder.wrapSelectExists("select 1 from o_order t0 where t0.id > ?", true, "");
+    assertThat(sql).isEqualTo("select case when exists(select 1 from o_order t0 where t0.id > ?) then 1 else 0 end");
+  }
+
+  /**
+   * Oracle also requires a FROM clause on every select (from dual) - see https://github.com/ebean-orm/ebean/issues/3848
+   */
+  @Test
+  void wrapSelectExists_existsWithCaseWhenAndFromClause_appendsFromClause() {
+    String sql = CQueryBuilder.wrapSelectExists("select 1 from o_order t0 where t0.id > ?", true, " from dual");
+    assertThat(sql).isEqualTo("select case when exists(select 1 from o_order t0 where t0.id > ?) then 1 else 0 end from dual");
+  }
+
   @Test
   void inlineSqlCommentLabel_rootExplicitLabel_prefixesBeanType() {
     String label = CQueryBuilder.inlineSqlCommentLabel("fetchMachineFleets", null, false, "COrganisationMachine");

ebean-test/src/test/java/org/tests/insert/TestInsertCheckUnique.java

@@ -112,7 +112,9 @@ public class TestInsertCheckUnique extends BaseTestCase {
       assertThat(DB.checkUniqueness(doc2).toString()).contains("title");
       List<String> sql = LoggedSql.stop();
       assertThat(sql).hasSize(1);
-      assertThat(sql.get(0)).contains("select exists(select 1 from document t0 where t0.title = ?)");
+      if (isH2() || isPostgresCompatible()) {
+        assertThat(sql.get(0)).contains("select exists(select 1 from document t0 where t0.title = ?)");
+      }
 
 
     }
@@ -135,8 +137,10 @@ public class TestInsertCheckUnique extends BaseTestCase {
     assertThat(DB.getDefault().checkUniqueness(basic, null, true, false)).isEmpty();
     List<String> sql = LoggedSql.stop();
     assertThat(sql).hasSize(2);
-    assertThat(sql.get(0)).contains("select exists(select 1 from e_basicverucon t0 where t0.name = ?)");
-    assertThat(sql.get(1)).contains("select exists(select 1 from e_basicverucon t0 where t0.other = ? and t0.other_one = ?)");
+    if (isH2() || isPostgresCompatible()) {
+      assertThat(sql.get(0)).contains("select exists(select 1 from e_basicverucon t0 where t0.name = ?)");
+      assertThat(sql.get(1)).contains("select exists(select 1 from e_basicverucon t0 where t0.other = ? and t0.other_one = ?)");
+    }
     DB.save(basic);
     try {
       // reload from database
@@ -147,8 +151,10 @@ public class TestInsertCheckUnique extends BaseTestCase {
       assertThat(DB.getDefault().checkUniqueness(basic, null, true, false)).isEmpty();
       sql = LoggedSql.stop();
       assertThat(sql).hasSize(2);
-      assertThat(sql.get(0)).contains("select exists(select 1 from e_basicverucon t0 where t0.id <> ? and t0.name = ?)");
-      assertThat(sql.get(1)).contains("select exists(select 1 from e_basicverucon t0 where t0.id <> ? and t0.other = ? and t0.other_one = ?)");
+      if (isH2() || isPostgresCompatible()) {
+        assertThat(sql.get(0)).contains("select exists(select 1 from e_basicverucon t0 where t0.id <> ? and t0.name = ?)");
+        assertThat(sql.get(1)).contains("select exists(select 1 from e_basicverucon t0 where t0.id <> ? and t0.other = ? and t0.other_one = ?)");
+      }
 
       // and check again - expect to hit query cache
       LoggedSql.start();
@@ -187,8 +193,10 @@ public class TestInsertCheckUnique extends BaseTestCase {
     assertThat(DB.getDefault().checkUniqueness(basic, null, false, true)).isEmpty();
     List<String> sql = LoggedSql.stop();
     assertThat(sql).hasSize(2);
-    assertThat(sql.get(0)).contains("select exists(select 1 from e_basicverucon t0 where t0.name = ?)");
-    assertThat(sql.get(1)).contains("select exists(select 1 from e_basicverucon t0 where t0.other = ? and t0.other_one = ?)");
+    if (isH2() || isPostgresCompatible()) {
+      assertThat(sql.get(0)).contains("select exists(select 1 from e_basicverucon t0 where t0.name = ?)");
+      assertThat(sql.get(1)).contains("select exists(select 1 from e_basicverucon t0 where t0.other = ? and t0.other_one = ?)");
+    }
     DB.save(basic);
     try (Transaction txn = DB.beginTransaction()) {
       // reload from database

ebean-test/src/test/java/org/tests/query/TestQueryFilterMany.java

@@ -174,7 +174,7 @@ public class TestQueryFilterMany extends BaseTestCase {
     assertThat(customers).isNotEmpty();
     List<String> sqlList = LoggedSql.stop();
     assertEquals(1, sqlList.size());
-    assertThat(sqlList.get(0)).contains(" left join o_customer t2 on t2.id = t1.kcustomer_id and t1.status = ? where ");
+    assertThat(sqlList.get(0)).contains(" left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null and t1.status = ? left join o_customer t2 on t2.id = t1.kcustomer_id where ");
     assertThat(sqlList.get(0)).contains(" where lower(t0.name) = ? order by t0.id");
   }
 
@@ -211,7 +211,7 @@ public class TestQueryFilterMany extends BaseTestCase {
     assertThat(result).isNotEmpty();
     List<String> sql = LoggedSql.stop();
     assertThat(sql).hasSize(1);
-    assertThat(sql.get(0)).contains("from o_customer t0 left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null left join o_customer t2 on t2.id = t1.kcustomer_id and t1.order_date is not null order by t0.id");
+    assertThat(sql.get(0)).contains("from o_customer t0 left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null and t1.order_date is not null left join o_customer t2 on t2.id = t1.kcustomer_id order by t0.id");
   }
 
   @Test
@@ -340,9 +340,9 @@ public class TestQueryFilterMany extends BaseTestCase {
     List<String> sqlList = LoggedSql.stop();
     assertEquals(1, sqlList.size());
     if (isPostgresCompatible()) {
-      assertThat(sqlList.get(0)).contains("left join o_customer t2 on t2.id = t1.kcustomer_id and t1.status = any(?) order by t0.id");
+      assertThat(sqlList.get(0)).contains("left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null and t1.status = any(?) left join o_customer t2 on t2.id = t1.kcustomer_id order by t0.id");
     } else {
-      assertThat(sqlList.get(0)).contains("left join o_customer t2 on t2.id = t1.kcustomer_id and t1.status in (?) order by t0.id");
+      assertThat(sqlList.get(0)).contains("left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null and t1.status in (?) left join o_customer t2 on t2.id = t1.kcustomer_id order by t0.id");
     }
   }
 
@@ -386,7 +386,7 @@ public class TestQueryFilterMany extends BaseTestCase {
 
     List<String> sql = LoggedSql.stop();
     assertEquals(1, sql.size());
-    assertSql(sql.get(0)).contains(" left join o_customer t2 on t2.id = t1.kcustomer_id and (t1.status = ? or t1.order_date = ?) order by t0.id");
+    assertSql(sql.get(0)).contains(" left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null and (t1.status = ? or t1.order_date = ?) left join o_customer t2 on t2.id = t1.kcustomer_id order by t0.id");
   }
 
   @Test
@@ -459,7 +459,10 @@ public class TestQueryFilterMany extends BaseTestCase {
 
     List<String> sql = LoggedSql.stop();
 
-    assertThat(sql).hasSize(1);
-    assertSql(sql.get(0)).contains(" from o_customer t0 left join contact t1 on t1.customer_id = t0.id left join contact_group t2 on t2.id = t1.group_id and t2.name = ? and t1.cretime is not null order by t0.id");
+    // nested "group.name" reference forces this to a query join so the filter is applied
+    // as a genuine WHERE clause (not misapplied to a LEFT JOIN's ON clause)
+    assertThat(sql).hasSize(2);
+    assertSql(sql.get(1)).contains(" from contact t0 left join contact_group t1 on t1.id = t0.group_id where (t0.customer_id) in (");
+    assertSql(sql.get(1)).contains(" and t1.name = ? and t0.cretime is not null");
   }
 }

ebean-test/src/test/java/org/tests/query/TestQueryFilterManySimple.java

@@ -35,7 +35,7 @@ public class TestQueryFilterManySimple extends BaseTestCase {
     list.get(0).getOrders().size();
     List<String> sql = LoggedSql.stop();
     assertThat(sql).hasSize(2);
-    assertThat(sql.get(0)).contains("left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null left join o_customer t2 on t2.id = t1.kcustomer_id and t1.status = ? and t1.order_date > ?");
+    assertThat(sql.get(0)).contains("left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null and t1.status = ? and t1.order_date > ? left join o_customer t2 on t2.id = t1.kcustomer_id");
     assertThat(sql.get(0)).contains("order by t0.id");
     if (isPostgresCompatible()) {
       assertThat(sql.get(1)).contains("from contact t0 where (t0.customer_id) = any(?) and t0.first_name is not null;");
@@ -55,7 +55,10 @@ public class TestQueryFilterManySimple extends BaseTestCase {
       .findList();
 
     List<String> sql = LoggedSql.stop();
-    assertThat(sql).hasSize(1);
-    assertThat(sql.get(0)).contains("from o_customer t0 left join o_order t1 on t1.kcustomer_id = t0.id and t1.order_date is not null left join o_customer t2 on t2.id = t1.kcustomer_id and t2.status = ? order by t0.id");
+    // nested "customer.status" reference forces this to a query join so the filter is
+    // applied as a genuine WHERE clause (not misapplied to a LEFT JOIN's ON clause)
+    assertThat(sql).hasSize(2);
+    assertThat(sql.get(1)).contains("from o_order t0 join o_customer t1 on t1.id = t0.kcustomer_id where t0.order_date is not null and (t0.kcustomer_id) in (");
+    assertThat(sql.get(1)).contains(" and t1.status = ?");
   }
 }

ebean-test/src/test/java/org/tests/query/TestQueryFilterManyWithDeeperNestedFetch.java

@@ -0,0 +1,73 @@
+package org.tests.query;
+
+import io.ebean.DB;
+import io.ebean.test.LoggedSql;
+import io.ebean.xtest.BaseTestCase;
+import org.junit.jupiter.api.Test;
+import org.tests.model.basic.Contact;
+import org.tests.model.basic.Customer;
+import org.tests.model.basic.ResetBasicData;
+
+import java.util.List;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+/**
+ * Reproduces a bug where filterMany() on a property of the many-root itself is misapplied
+ * to the ON clause of a deeper, unrelated nested fetch join instead of the many-root's own join
+ * clause - when an additional fetch path exists beneath the many property that the filterMany
+ * expression itself does not reference.
+ */
+public class TestQueryFilterManyWithDeeperNestedFetch extends BaseTestCase {
+
+  @Test
+  void filterMany_onRootProperty_withUnrelatedDeeperNestedFetch_expectFilterOnOwnJoin() {
+    ResetBasicData.reset();
+
+    Customer customer = DB.find(Customer.class).where().ieq("name", "Rob").findOne();
+    assertThat(customer).isNotNull();
+
+    List<Contact> allContacts = DB.find(Contact.class).where().eq("customer", customer).findList();
+    assertThat(allContacts).isNotEmpty();
+
+    // ensure at least one contact isMember=true and one isMember=false
+    Contact memberContact = allContacts.get(0);
+    memberContact.setMember(true);
+    DB.save(memberContact);
+    Contact nonMemberContact;
+    if (allContacts.size() > 1) {
+      nonMemberContact = allContacts.get(1);
+    } else {
+      nonMemberContact = new Contact();
+      nonMemberContact.setFirstName("Extra");
+      nonMemberContact.setLastName("NonMember");
+      nonMemberContact.setCustomer(customer);
+    }
+    nonMemberContact.setMember(false);
+    DB.save(nonMemberContact);
+
+    LoggedSql.start();
+    // filterMany only references "isMember" (a property of Contact - the many-root itself) but
+    // the query ALSO fetches a further nested path beneath "contacts" (contacts.group) that the
+    // filterMany expression does NOT reference at all.
+    List<Customer> found = DB.find(Customer.class)
+      .setBeanCacheMode(io.ebean.CacheMode.OFF)
+      .setPersistenceContextScope(io.ebean.PersistenceContextScope.QUERY)
+      .fetch("contacts", "id,firstName,lastName,isMember")
+      .fetch("contacts.group", "id,name")
+      .filterMany("contacts").eq("isMember", true)
+      .where().idEq(customer.getId())
+      .findList();
+
+    List<String> sql = LoggedSql.stop();
+    assertThat(found).isNotEmpty();
+    assertThat(sql).hasSize(1);
+    // the filterMany predicate must be on contact's own join (t1), not misapplied to the
+    // unrelated deeper contact_group join (t2)
+    assertThat(sql.get(0)).contains("left join contact t1 on t1.customer_id = t0.id and t1.is_member = ? left join contact_group t2 on t2.id = t1.group_id");
+
+    // every contact returned must be a member - the filter must actually exclude non-members
+    assertThat(found.get(0).getContacts()).isNotEmpty();
+    assertThat(found.get(0).getContacts()).allMatch(Contact::isMember);
+  }
+}

platforms/oracle/src/main/java/io/ebean/platform/oracle/OraclePlatform.java

@@ -33,6 +33,8 @@ public class OraclePlatform extends DatabasePlatform {
     this.dbDefaultValue.setTrue("1");
     this.dbDefaultValue.setNow("current_timestamp");
     this.likeClauseRaw = "like ?";
+    this.existsWithCaseWhen = true;
+    this.existsFromClause = " from dual";
 
     this.exceptionTranslator =
       new SqlErrorCodes()

platforms/oracle/src/test/java/io/ebean/platform/oracle/OraclePlatformTest.java

@@ -45,4 +45,11 @@ class OraclePlatformTest {
     DbPlatformType dbType = platform.dbTypeMap().get(DbPlatformType.UUID);
     assertThat(dbType.renderType(0, 0)).isEqualTo("raw(16)");
   }
+
+  @Test
+  void existsWithCaseWhen_trueForOracle() {
+    OraclePlatform platform = new OraclePlatform();
+    assertThat(platform.existsWithCaseWhen()).isTrue();
+    assertThat(platform.existsFromClause()).isEqualTo(" from dual");
+  }
 }

platforms/sqlserver/src/main/java/io/ebean/platform/sqlserver/SqlServerBasePlatform.java

@@ -26,6 +26,7 @@ abstract class SqlServerBasePlatform extends DatabasePlatform {
     this.idInExpandedForm = true;
     this.selectCountWithAlias = true;
     this.selectCountWithColumnAlias = true;
+    this.existsWithCaseWhen = true;
     this.sqlLimiter = new SqlServerSqlLimiter();
     this.basicSqlLimiter = new SqlServerBasicSqlLimiter();
     this.historySupport = new SqlServerHistorySupport();

platforms/sqlserver/src/test/java/io/ebean/platform/sqlserver/SqlServerPlatformTest.java

@@ -40,6 +40,12 @@ class SqlServerPlatformTest {
     assertEquals(dbPlatform.unQuote("[firstName]"), "firstName");
   }
 
+  @Test
+  public void existsWithCaseWhen_trueForSqlServer() {
+    SqlServer17Platform dbPlatform = new SqlServer17Platform();
+    assertEquals(dbPlatform.existsWithCaseWhen(), true);
+  }
+
   @Test
   public void defaultTypesForDecimalAndVarchar() {
     DatabasePlatform dbPlatform = new DatabasePlatform();