An inner join between two EF Core tables fails: anonymous Comparator with no body, via EnumerableMergeJoin

[wasabii]

An inner join between two EF Core tables plans into the Enumerable convention and then fails to execute:

System.NotSupportedException: An anonymous 'java.util.Comparator' declares 2 methods
and no single one of them carries the body.

Measured against Apache.Calcite.EntityFrameworkCore.Adapter 1.0.0-pre.63 with Apache.Calcite.Data 2.0.1-pre.64, net10.0, over SQL Server.

SELECT u."Id", p."Name"
FROM "sql"."User" AS u
JOIN "sql"."UserProfile" AS p ON p."UserId" = u."Id"
FETCH FIRST 2 ROWS ONLY

EnumerableToClrAsyncEnumerableConverter
  EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
    EnumerableLimit(fetch=[2])
      EnumerableMergeJoin(condition=[=($0, $2)], joinType=[inner])
        ClrEnumerableToEnumerableConverter
          ClrAsyncEnumerableToClrEnumerableConverter
            EfCoreToClrAsyncEnumerableConverter
              EfCoreOrderBy(sort0=[$0], dir0=[ASC])
                EfCoreSelect(Id=[$0])
                  EfCoreEntityScan(table=[[sql, User]])
        …

NotSupportedException at execution.

A left join is fine, because it plans into ClrAsyncEnumerableMergeJoin instead and never reaches the sync convention:

SELECT u."Id", p."Name", p."EmailAddress"
FROM "sql"."User" AS u
LEFT JOIN "sql"."UserProfile" AS p ON p."UserId" = u."Id" AND p."DeleteUtcTime" IS NULL
WHERE u."DeleteUtcTime" IS NULL
FETCH FIRST 3 ROWS ONLY

ClrAsyncEnumerableCalc(…)
  ClrAsyncEnumerableLimit(fetch=[3])
    ClrAsyncEnumerableMergeJoin(condition=[=($0, $5)], joinType=[left])
      EfCoreToClrAsyncEnumerableConverter …
      EfCoreToClrAsyncEnumerableConverter …

3 rows.

So the failure tracks the convention the join lands in rather than the join itself. EnumerableMergeJoin builds an anonymous Comparator, and IKVM will not accept one where two methods are declared and neither carries the body — presumably compare alongside a bridge or a default equals.

The second half of it

Both plans join above the adapter, over two separate EfCoreEntityScans. EfCoreJoin exists in Rel/Core but neither plan uses it, so a join between two tables of the same DbContext — which EF Core would render as one SQL JOIN and the server would answer with one query — is instead two full scans and a merge in memory.

Fixing the join push-down would make the Comparator failure unreachable for this shape as a side effect, though it would presumably still be reachable for a join whose sides genuinely come from different schemas.