Efcore.pg: Incorrectly generated SQL containing LEFT JOIN LATERAL

[PatrikBak]

Assume I have a table Matches with the primary key MatchId.

The query

from _ in dbContext.Matches.AsQueryable()
select new { X = (from __ in dbContext.Matches.AsQueryable() select 1).ToList() };

generates

SELECT m."MatchId", 1, m0."MatchId"
FROM "Matches" AS m
LEFT JOIN LATERAL "Matches" AS m0 ON TRUE
ORDER BY m."MatchId", m0."MatchId"

which appears to be invalid:

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "AS"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "AS"
    Position: 87
    File: scan.l
    Line: 1149
    Routine: scanner_yyerror

Provider: Npgsql.EntityFrameworkCore 3.1.4.
Database: PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit

[roji]

Simplified repro:

```c#
var x = await ctx.Blogs.Select(b => ctx.Blogs.Select(bb => bb.Name).ToList()).ToListAsync();

SQL Server SQL (works):

```sql
SELECT [b].[Id], [b0].[Name], [b0].[Id]
FROM [Blogs] AS [b]
OUTER APPLY [Blogs] AS [b0]
ORDER BY [b].[Id], [b0].[Id]

PostgreSQL SQL (doesn't work):

SELECT b."Id", b0."Name", b0."Id"
FROM "Blogs" AS b
LEFT JOIN LATERAL "Blogs" AS b0 ON TRUE
ORDER BY b."Id", b0."Id"

It turns out that PostgreSQL doesn't allow a simple table as the target of LATERAL JOIN (because by definition nothing can be correlated). If we wrap with a subquery it does work:

SELECT b."Id", b0."Name", b0."Id"
FROM "Blogs" AS b
LEFT JOIN LATERAL (SELECT * FROM "Blogs") AS b0 ON TRUE
ORDER BY b."Id", b0."Id"

@smitpatel I can make EFCore.PG wrap simple tables with subqueries to work around this. However, wouldn't it be more correct for EF Core to translate this to CROSS JOIN instead of OUTER APPLY?

@PatrikBak did you encounter this in a real-world query (if so, some context could be helpful)? Also, note that you can omit the AsQueryables since DbSets are already IQueryable.

[smitpatel]

This is Select+Select not SelectMany.
Cross Join gives a flat list of records. This is generating list of lists hence Apply operation.

[roji]

I understand maybe at the shaper/materialization side this matters, but I'm talking about the SQL getting sent to SQL Server. In other words, the following two queries seem to return the same results:

SELECT [b].[Id], [b0].[Name], [b0].[Id]
FROM [Blogs] AS [b]
OUTER APPLY [Blogs] AS [b0]
ORDER BY [b].[Id], [b0].[Id]

SELECT [b].[Id], [b0].[Name], [b0].[Id]
FROM [Blogs] AS [b]
CROSS JOIN [Blogs] AS [b0]
ORDER BY [b].[Id], [b0].[Id];

Another way to say it, is why would anyone ever do OUTER APPLY on a simple table (as opposed to a subquery).

[smitpatel]

OUTER APPLY will return a single row with null for right when right is empty. Not the case for CROSS JOIN

[PatrikBak]

@roji

did you encounter this in a real-world query (if so, some context could be helpful)?

I came across this issue while debugging a different query. I thought I would report it just in case.

Also, note that you can omit the AsQueryables since DbSets are already IQueryable.

Sadly, I cannot, as I use System.Linq.Async

[roji]

Thanks @smitpatel, makes sense.

[smitpatel]

See https://github.com/dotnet/efcore/issues/23201

[roji]

Yep, that is pretty much it :) I will work around the PG limitation in EFCore.PG for now, in any case.