SqlServerCompiler removing 'GROUP BY' clause after Compile()

[mark-at-tusksoft]

SqlKata Version: 1.1.7

.NET Framework: 4.7.2

Observed Behavior: After compiling a query that contains multiple clauses (and nested query), the final clause which is a group by is silently removed from the clauses collection.

Expected behavior: The group by clause is added to the resulting SQL and not removed from the clauses collection. Additionally, if the compiler cannot add it, an informative exception is thrown.

[ahmad-moussawi]

@mark-at-tusksoft could you please post a reproducible scenario?

[ahmad-moussawi]

I was not able to reproduce the case, I will close it for now, feel free to reopen if the issue persist.

[anorderh]

I also experienced this issue and believe its coming from lines 129:136 in QueryBuilder/Compilers/Compiler.cs.

if (query.Method == "aggregate")
{
    query.ClearComponent("limit")
        .ClearComponent("order")
        .ClearComponent("group");

    query = TransformAggregateQuery(query);
}

I am using sqlkata to generate queries, incremently adding clauses onto a Query via custom helpers.

When trying to add an Aggregate clause .AsCount() to a select query with 1 column specified, and then trying to group based on that 1 column, I receive the following Query obj.

Then when compiling with...

string sql = compiler.Compile(query).ToString();

The query object changes to this.

I am not sure if all queries with an aggregate clause should have their Method labeled as aggregate. I suspect TransformAggregateQuery may be used to supplement this, but dont yet understand its purpose and at that point the input groupings are lost.

I got past this by changing Query's Method to select after grouping.

[daerogami]

Great find @anorderh! That's disconcerting that Sqlkata is altering the composed query instead of failing the compilation.

[ahmad-moussawi]

@anorderh The aggregate method AsCount() is the underlying method for the executable Count() method, where it cannot be used with a Group By clause, so I think your usage here for the AsCount is incorrect.

Could you elaborate more on why it's needed in your case?

[anorderh]

@ahmad-moussawi In my case, I am using SqlKata to generate valid Query objects to quiz users on SQL querying. Take for example a table Customers that includes customers' country of origin.

Query query = new Query("Customers").Select("origin");

SELECT origin FROM Customers;

With the query so far, I would like to add on AsCount() and GroupBy() to find the number of customers from each country.

query = new Query("Customers").Select("origin").AsCount().GroupBy("origin")

By neglecting any params for AsCount(), it defaults to "*" for my desired result, but when compiling to a string, the grouping and "origin" selection are removed entirely.

/* Expected result */
SELECT origin, COUNT(*) AS count FROM Customers GROUP BY origin;

/* Actual result */
SELECT COUNT(*) AS count FROM Customers;

I found using SelectRaw() is suitable for getting past this per the docs, but feel my suggested usage could be more intuitive. I also recently found there are built-in 'SelectAggregate functions', but wasn't able to invoke these in my .NET env.

[areevesat]

I have a use case where this issue is impacting my solution. I construct a query counting the number of organization rows that have one or more "org_storage" records. If the user has specified to search by name, I narrow this down by name.

totalQuery = new Query("org_storage")
    .Select("organization_id")
    .LeftJoin("organizations", "org_storage.organization_id", "organizations.id")
    .GroupBy("organization_id")
    .AsCount();
if (!string.IsNullOrWhiteSpace(searchParams.Name))
{
    totalQuery.WhereContains("name", searchParams.Name);
}

I would expect this to generate SQL comparable to the below

SELECT count(*) FROM (SELECT organization_id FROM org_storage LEFT JOIN organizations ON organizations.id = org_storage.organization_id WHERE name LIKE '%query%')

Now, it might be better to construct this as one query and then use that as an inner query and use .AsCount() on the outer query (which is the workaround I ended up using). But it took a long time debugging this, digging around into the generated SQL commands, and 3 developers on and off looking at this before we figured out what was going on. It would make much more sense for the compiler to either generate the code as written or throw an error message that it can't be compiled. Silently removing part of the query runs very counter to user expectations.