GroupBy inside Select produces wrong SQL

[orjandh]

Steps to reproduce

Create a query with a GroupBy on a navigational property inside a select statement.

await db.sessions
    .Include(s => s.answers)
    .Select(s => s.answers.GroupBy(a => a.identifier).Count())
    .ToListAsync();

Program.cs

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class Session
    {
        public int id { get; set; }
        public DateTime date { get; set; }
        public IEnumerable<Answer> answers { get; set; }
    }

    public class Answer
    {
        public int id { get; set; }
        public string identifier { get; set; }
        public int sessionId { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<Session> sessions { get; set; }
        public DbSet<Answer> answers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1678";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(connectionString, serverVersion)
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
            //optionsBuilder.UseInMemoryDatabase("Issue1678");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Session>(
                entity =>
                {
                    entity.HasData(
                        new Session
                        {
                            id = 1,
                            date = DateTime.Now
                        });
                });

            modelBuilder.Entity<Answer>(
                entity =>
                {
                    entity.HasData(
                        new Answer
                        {
                            id = 1,
                            sessionId = 1,
                            identifier = "test"
                        });
                });
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var result = context.sessions
                .Include(s => s.answers)
                .Select(s => s.answers.GroupBy(a => a.identifier).Count())
                .ToList();

            Console.WriteLine(string.Join(",", result));
        }
    }
}

The issue

A query like the one above produces the following SQL. I believe the problem is that the alias 's' is not available and the inner clause is missing a JOIN statement.

SELECT (
    SELECT COUNT(*)
    FROM (
        SELECT `a`.`identifier`
        FROM `answers` AS `a`
        WHERE `s`.`id` = `a`.`sessionId`
        GROUP BY `a`.`identifier`
    ) AS `t`
)
FROM `sessions` AS `s`

The exception details are as follows.

An exception occurred while iterating over the results of a query for context type 'IssueConsoleTemplate.Context'.

MySqlConnector.MySqlException (0x80004005): Unknown column 's.id' in 'where clause'
  at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 44
  at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 127
  at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
  at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
  at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 330
  at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 272
  at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
  at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
  at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
  at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

Further technical details

MySQL version: 5.7.36 Operating system: Windows 11 Pomelo.EntityFrameworkCore.MySql version: 6.0.1 Microsoft.AspNetCore.App version: 6.0.6

[mguinness]

Please post the involved model classes and the model definitions (Fluent API), so we can reproduce the issue. Even better would be a small sample app, see https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1612#issuecomment-1026004098 for an example.

[orjandh]

Thank you. The issue has been updated with a minimal reproducible program.

[mguinness]

Thanks for the example. I also ran it using the SQL Server provider and get the same result, see this .NET Fiddle.

@smitpatel Any pointers?

[smitpatel]

From T-SQL perspective it is valid SQL. You can use any of the top level aliases from table inside any level nesting in the projection subqueries. So this could be just database specific limitations in terms of generated SQL. Do you have any alternate translation for the LINQ query?

[mguinness]

It looks like MySQL added support in 8.0.14, see Supporting all kinds of outer references in derived tables. For earlier versions the generated SQL will need to be changed.

[meriturva]

Hi to all, any news here? do we have to wait for LATERAL support from MariaDB https://jira.mariadb.org/browse/MDEV-6373 ?

Thanks!

[lauxjpn]

@meriturva If you write the query like the one in the OP, then for the query to work, there needs to be support for inner queries referencing outer queries in the database server implementation.

The LINQ query in the OP could probably be written in a different manner though, so that there is no need for the inner query to reference the outer one.

[meriturva]

@lauxjpn Yes I mean the one in the original post. Rewriting queries to overcome this database limitation produces not-so-maintainable code.

Already done few times and it makes me so sad!