SqlServerCompiler removing 'GROUP BY' clause after Compile() #330

Activity

1. ahmad-moussawi commented on Jan 5, 2020

   

   ahmad-moussawi

   on Jan 5, 2020

   Contributor

   More actions

   @mark-at-tusksoft could you please post a reproducible scenario?
2. ahmad-moussawi commented on Jun 10, 2020

   

   ahmad-moussawi

   on Jun 10, 2020

   Contributor

   More actions

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

   closed this as completedon Jun 10, 2020
4. anorderh commented on Oct 5, 2023

   

   anorderh

   on Oct 5, 2023

   More actions

   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.

   🚀React with 🚀1Reacted by Mark Clark
5. daerogami commented on Oct 6, 2023

   

   daerogami

   on Oct 6, 2023

   More actions

   Great find @anorderh! That's disconcerting that Sqlkata is altering the composed query instead of failing the compilation.
6. ahmad-moussawi commented on Oct 8, 2023

   

   ahmad-moussawi

   on Oct 8, 2023

   Contributor

   More actions

   @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?
7. anorderh commented on Oct 8, 2023

   

   anorderh

   on Oct 8, 2023

   More actions

   @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.
8. areevesat commented on Dec 11, 2025

   

   areevesat

   on Dec 11, 2025

   Last edited by areevesat

   More actions

   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%' GROUP BY organization_id)

   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.

   👍React with 👍2Reacted by mark-at-tusksoft and Ryan Morris