Fix `COUNT(DISTINCT ...)` with `ORDER BY` and `LIMIT`

Since #26972, `ORDER BY` is kept if `LIMIT` is presented for
performance. But in most SQL servers (e.g. PostgreSQL, SQL Server, etc),
`ORDER BY` expressions must appear in select list for `SELECT DISTINCT`.
We should not replace existing select list in that case.

1 files changed, 19 insertions, 10 deletions

diff --git a/activerecord/lib/active_record/relation/calculations.rb b/activerecord/lib/active_record/relation/calculations.rb
index c105abe774..a4714f138a 100644
--- a/activerecord/lib/active_record/relation/calculations.rb
+++ b/activerecord/lib/active_record/relation/calculations.rb
@@ -131,7 +131,7 @@ module ActiveRecord
     def calculate(operation, column_name)
       if has_include?(column_name)
         relation = construct_relation_for_association_calculations
-        relation = relation.distinct if operation.to_s.downcase == "count"
+        relation.distinct! if operation.to_s.downcase == "count"
 
         relation.calculate(operation, column_name)
       else
@@ -214,8 +214,13 @@ module ActiveRecord
 
         if operation == "count"
           column_name ||= select_for_count
-          column_name = primary_key if column_name == :all && distinct
-          distinct = nil if column_name =~ /\s*DISTINCT[\s(]+/i
+          if column_name == :all
+            if distinct && !(has_limit_or_offset? && order_values.any?)
+              column_name = primary_key
+            end
+          elsif column_name =~ /\s*DISTINCT[\s(]+/i
+            distinct = nil
+          end
         end
 
         if group_values.any?
@@ -242,7 +247,7 @@ module ActiveRecord
       def execute_simple_calculation(operation, column_name, distinct) #:nodoc:
         column_alias = column_name
 
-        if operation == "count" && (limit_value || offset_value)
+        if operation == "count" && has_limit_or_offset?
           # Shortcut when limit is zero.
           return 0 if limit_value == 0
 
@@ -381,14 +386,18 @@ module ActiveRecord
       end
 
       def build_count_subquery(relation, column_name, distinct)
-        column_alias = Arel.sql("count_column")
-        subquery_alias = Arel.sql("subquery_for_count")
+        relation.select_values = [
+          if column_name == :all
+            distinct ? Arel.star : Arel.sql("1")
+          else
+            column_alias = Arel.sql("count_column")
+            aggregate_column(column_name).as(column_alias)
+          end
+        ]
 
-        aliased_column = aggregate_column(column_name == :all ? 1 : column_name).as(column_alias)
-        relation.select_values = [aliased_column]
-        subquery = relation.arel.as(subquery_alias)
+        subquery = relation.arel.as(Arel.sql("subquery_for_count"))
+        select_value = operation_over_aggregate_column(column_alias || Arel.star, "count", false)
 
-        select_value = operation_over_aggregate_column(column_alias, "count", distinct)
         Arel::SelectManager.new(subquery).project(select_value)
       end
   end