Fix `count(:all)` with eager loading and having an order other than the driving table

This is a regression caused by 6beb4de.

In PostgreSQL, ORDER BY expressions must appear in SELECT list when
using DISTINCT.

When using `count(:all)` with eager loading, Active Record enforces
DISTINCT to count the driving table records only. 6beb4de was caused the
regression because `count(:all)` with DISTINCT path no longer removes
ORDER BY.

We need to ignore ORDER BY when DISTINCT is enforced, otherwise not
always generated valid SQL for PostgreSQL.

Fixes #31783.

4 files changed, 27 insertions, 2 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 09df0763e7..af77eaae0e 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,9 @@
+*   Fix `count(:all)` with eager loading and having an order other than the driving table.
+
+    Fixes #31783.
+
+    *Ryuta Kamizono*
+
 *   Clear the transaction state when an Active Record object is duped.
 
     Fixes #31670.
diff --git a/activerecord/lib/active_record/relation/calculations.rb b/activerecord/lib/active_record/relation/calculations.rb
index cb0b06cfdc..decd60c87f 100644
--- a/activerecord/lib/active_record/relation/calculations.rb
+++ b/activerecord/lib/active_record/relation/calculations.rb
@@ -131,7 +131,14 @@ module ActiveRecord
     def calculate(operation, column_name)
       if has_include?(column_name)
         relation = apply_join_dependency
-        relation.distinct! if operation.to_s.downcase == "count"
+
+        if operation.to_s.downcase == "count" && !distinct_value
+          relation.distinct!
+          # PostgreSQL: ORDER BY expressions must appear in SELECT list when using DISTINCT
+          if (column_name == :all || column_name.nil?) && select_values.empty?
+            relation.order_values = []
+          end
+        end
 
         relation.calculate(operation, column_name)
       else
diff --git a/activerecord/test/cases/calculations_test.rb b/activerecord/test/cases/calculations_test.rb
index e682da6fed..82b15e565b 100644
--- a/activerecord/test/cases/calculations_test.rb
+++ b/activerecord/test/cases/calculations_test.rb
@@ -21,7 +21,7 @@ require "models/comment"
 require "models/rating"
 
 class CalculationsTest < ActiveRecord::TestCase
-  fixtures :companies, :accounts, :topics, :speedometers, :minivans, :books
+  fixtures :companies, :accounts, :topics, :speedometers, :minivans, :books, :posts, :comments
 
   def test_should_sum_field
     assert_equal 318, Account.sum(:credit_limit)
@@ -236,6 +236,12 @@ class CalculationsTest < ActiveRecord::TestCase
     end
   end
 
+  def test_count_with_eager_loading_and_custom_order
+    posts = Post.includes(:comments).order("comments.id")
+    assert_queries(1) { assert_equal 11, posts.count }
+    assert_queries(1) { assert_equal 11, posts.count(:all) }
+  end
+
   def test_distinct_count_all_with_custom_select_and_order
     accounts = Account.distinct.select("credit_limit % 10").order(Arel.sql("credit_limit % 10"))
     assert_queries(1) { assert_equal 3, accounts.count(:all) }
diff --git a/activerecord/test/cases/relations_test.rb b/activerecord/test/cases/relations_test.rb
index 19489f3c71..673ff95bc2 100644
--- a/activerecord/test/cases/relations_test.rb
+++ b/activerecord/test/cases/relations_test.rb
@@ -969,6 +969,12 @@ class RelationTest < ActiveRecord::TestCase
     assert_queries(1) { assert_equal 8, posts.load.size }
   end
 
+  def test_size_with_eager_loading_and_custom_order
+    posts = Post.includes(:comments).order("comments.id")
+    assert_queries(1) { assert_equal 11, posts.size }
+    assert_queries(1) { assert_equal 11, posts.load.size }
+  end
+
   def test_update_all_with_scope
     tag = Tag.first
     Post.tagged_with(tag.id).update_all title: "rofl"