Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query: GroupBy with Where produces invalid SQL #10870

Closed
Suchiman opened this issue Feb 4, 2018 · 5 comments
Closed

Query: GroupBy with Where produces invalid SQL #10870

Suchiman opened this issue Feb 4, 2018 · 5 comments
Assignees
Milestone

Comments

@Suchiman
Copy link

@Suchiman Suchiman commented Feb 4, 2018

Using GroupBy with Where produces invalid SQL

Exception message:
   Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'misuse of aggregate function COUNT()'.'
Stack trace:
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.<PrepareAndEnumerateStatements>d__62.MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_GroupBy>d__19`3.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at MegaDedup.Program.Main(String[] args) in Program.cs:line 24

Steps to reproduce

class Program
{
    public static readonly LoggerFactory MyLoggerFactory = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

    static void Main(string[] args)
    {
        var context = new FileContext();
        context.Database.EnsureCreated();

        context.FileEntries
            .GroupBy(x => x.Size)
            .Where(x => x.Count() > 1)
            .Select(x => x.Key)
            .ToList();
    }
}

class FileContext : DbContext
{
    public DbSet<FileEntry> FileEntries { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var builder = new SqliteConnectionStringBuilder();
        builder.DataSource = "db.sqlite";
        optionsBuilder
            .UseLoggerFactory(Program.MyLoggerFactory)
            .UseSqlite(builder.ToString());
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<FileEntry>()
            .HasIndex(x => x.Size);
    }
}

class FileEntry
{
    public long Id { get; set; }
    public long Size { get; set; }
    public string Path { get; set; }
}

Produced SQL

SELECT "x"."Id", "x"."Path", "x"."Size"
FROM "FileEntries" AS "x"
WHERE COUNT(*) > 1
ORDER BY "x"."Size"

Expected SQL

SELECT "x"."Size"
FROM "FileEntries" AS "x"
GROUP BY "x"."Size"
HAVING COUNT(*) > 1

Further technical details

EF Core version: 2.1.0-preview1-28226
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10 Pro
IDE: Visual Studio 2017 15.6 p3

@smitpatel smitpatel added the type-bug label Feb 4, 2018
@smitpatel smitpatel self-assigned this Feb 4, 2018
@smitpatel
Copy link
Member

@smitpatel smitpatel commented Feb 4, 2018

@Suchiman - Would following SQL work?

SELECT "t"."Size"
FROM (
    SELECT "x"."Size", COUNT(*) AS c
    FROM "FileEntries" AS "x"
    GROUP BY "x"."Size"
) AS "t"
WHERE "t"."c" > 1

Related #10012

@Suchiman
Copy link
Author

@Suchiman Suchiman commented Feb 4, 2018

@smitpatel Of course, thats pretty much the SQL EF6 did generate (for MSSQL, but works for SQLite as well).

SELECT 
[GroupBy1].[K1] AS [Size]
FROM ( SELECT 
    [Extent1].[Size] AS [K1], 
    COUNT(1) AS [A1]
    FROM [dbo].[FileEntries] AS [Extent1]
    GROUP BY [Extent1].[Size]
)  AS [GroupBy1]
WHERE [GroupBy1].[A1] > 1
@ajcvickers ajcvickers added this to the 2.1.0 milestone Feb 5, 2018
@smitpatel
Copy link
Member

@smitpatel smitpatel commented Feb 22, 2018

It seems like almost all relational providers have Having clause supported in SelectExpression. We will try to translate to Having clause instead of causing a subquery (like EF6).

smitpatel added a commit that referenced this issue Mar 13, 2018
- Add support for translating OrderBy after GroupBy operator
- Add support for `HAVING` clause in SQL which would be generated when translating predicate after GroupByAggregate Resolves #10870
- Make sure client eval warning is not issued when translating GroupByAggregate Resolves #11157
- GroupBy Aggregate works when key/element/result selector is DTO instead of anonymous type Resolves #11176
- Make sure that SQL added to GROUP BY clause is not aliased Resolves #11218

Part of #10012
smitpatel added a commit that referenced this issue Mar 14, 2018
- Add support for translating OrderBy after GroupBy operator
- Add support for `HAVING` clause in SQL which would be generated when translating predicate after GroupByAggregate Resolves #10870
- Make sure client eval warning is not issued when translating GroupByAggregate Resolves #11157
- GroupBy Aggregate works when key/element/result selector is DTO instead of anonymous type Resolves #11176
- Make sure that SQL added to GROUP BY clause is not aliased Resolves #11218
- Translate GroupBy Constant/Parameter with aggregates Resolves #9969

Part of #10012
smitpatel added a commit that referenced this issue Mar 14, 2018
- Add support for translating OrderBy after GroupBy operator
- Add support for `HAVING` clause in SQL which would be generated when translating predicate after GroupByAggregate Resolves #10870
- Make sure client eval warning is not issued when translating GroupByAggregate Resolves #11157
- GroupBy Aggregate works when element/result selector is DTO instead of anonymous type Resolves #11176 (KeySelector has to be client evaluated)
- Make sure that SQL added to GROUP BY clause is not aliased Resolves #11218
- Translate GroupBy Constant/Parameter with aggregates Resolves #9969

Part of #10012
Part of #2341
smitpatel added a commit that referenced this issue Mar 14, 2018
- Add support for translating OrderBy after GroupBy operator
- Add support for `HAVING` clause in SQL which would be generated when translating predicate after GroupByAggregate Resolves #10870
- Make sure client eval warning is not issued when translating GroupByAggregate Resolves #11157
- GroupBy Aggregate works when element/result selector is DTO instead of anonymous type Resolves #11176 (KeySelector has to be client evaluated)
- Make sure that SQL added to GROUP BY clause is not aliased Resolves #11218
- Translate GroupBy Constant/Parameter with aggregates Resolves #9969

Part of #10012
Part of #2341
smitpatel added a commit that referenced this issue Mar 14, 2018
- Add support for translating OrderBy after GroupBy operator
- Add support for `HAVING` clause in SQL which would be generated when translating predicate after GroupByAggregate Resolves #10870
- Make sure client eval warning is not issued when translating GroupByAggregate Resolves #11157
- GroupBy Aggregate works when element/result selector is DTO instead of anonymous type Resolves #11176 (KeySelector has to be client evaluated)
- Make sure that SQL added to GROUP BY clause is not aliased Resolves #11218
- Translate GroupBy Constant/Parameter with aggregates Resolves #9969

Part of #10012
Part of #2341
@smitpatel
Copy link
Member

@smitpatel smitpatel commented Mar 14, 2018

We generate SQL like this now

SELECT [o].[CustomerID] AS [Key], COUNT(*) AS [Count]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerID]
HAVING COUNT(*) > 4"
@roji
Copy link
Member

@roji roji commented Mar 14, 2018

Unless I'm mistaken, HAVING is part of the SQL standard so it should be fine to use it. It's great that EF Core will be using it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
4 participants