EF Core: a table-valued function is not wrapped in a derived table after LATERAL, so every correlated queryable function fails

[azabluda]

Summary

FbQuerySqlGenerator already knows that Firebird will not accept a bare source after LATERAL: VisitCrossApply and VisitOuterApply both special-case a TableExpression and emit (SELECT * FROM "T") AS "t". The same branch is missing for a TableValuedFunctionExpression, so a correlated queryable function is emitted as a bare call and the statement does not parse.

I think this is the cause of the 14 [NotSupportedOnFirebirdFact] skips in UdfDbFunctionFbTests (all the QF_*Correlated* / QF_*Apply* ones). The store supports the query; only the generated SQL is malformed.

Repro

EF Core 10.0.0, FirebirdSql.EntityFrameworkCore.Firebird 13.0.0, Firebird 5.0.3 embedded.

create procedure "PostsOf" (blogId int)
returns ("PostId" int, "Heading" varchar(450))
as
begin
    for select "Id", "Heading" from "Posts" where "BlogId" = :blogId
    into :"PostId", :"Heading" do
    begin
        suspend;
    end
end

public IQueryable<PostRow> PostsOf(int blogId)
    => FromExpression(() => PostsOf(blogId));

// OnModelCreating
modelBuilder.Entity<PostRow>().HasNoKey().ToTable((string?)null);
modelBuilder.HasDbFunction(typeof(MyContext).GetMethod(nameof(PostsOf))!);

var rows = (from blog in context.Blogs
            from post in context.PostsOf(blog.Id)
            select new { blog.Id, post.Heading }).ToList();

Actual

SELECT "b"."Id", "p"."Heading"
FROM "Blogs" AS "b"
JOIN LATERAL "PostsOf"("b"."Id") AS "p" ON TRUE

FbException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 14
"PostsOf"

The uncorrelated form is fine, because it does not go through VisitCrossApply:

SELECT "p"."Heading", "p"."PostId" FROM "PostsOf"(@blogId) AS "p"   -- works

Expected

JOIN LATERAL (SELECT * FROM "PostsOf"("b"."Id")) AS "p" ON TRUE

I ran both forms directly against Firebird 5.0.3 before reporting. The wrapped form returns the correct rows. The implicit form FROM "Blogs" b, "PostsOf"(b."Id") p also works, if that is preferable to a derived table.

Suggested fix

Add a TableValuedFunctionExpression branch beside the existing TableExpression one in both VisitCrossApply and VisitOuterApply, writing the function call inside (SELECT * FROM ...) and putting the alias on the derived table rather than on the call. I verified this by subclassing FbQuerySqlGenerator and replacing IQuerySqlGeneratorFactory: the query above then returns the expected rows.

Happy to open a PR if that would help.

[cincuranet]

Sure. Open a PR.

[azabluda]

Opened #1278. The new tests and the fixture objects they need are gated on ServerLessThan4(), since LATERAL starts at FB4.

[mrotteveel]

Technically the presence of lateral isn't really necessary, as joins with selectable procedures are (and always have been) lateral implicitly.

[azabluda]

@mrotteveel Thanks for this view of the problem. I closed #1278 and opened #1279.

The new fix does not emit LATERAL before a procedure call. The wrapping existed only to make that LATERAL legal, so it is gone too. The change is smaller than in my first PR, and the tests now pass on Firebird 3.

Two tests still seem to genuinely require LATERAL, so I skip those on FB3.