Fix `relation.exists?` with giving `distinct`, `offset` and `order` for joined table

The error happens in PostgreSQL when using `relation.exists?` with
`distinct`, `offset` and `order` for joined table.
However, the error does not happen if either `distinct` or `offset` is
removed. This behavior is confusing.

Fixes #36632

2 files changed, 6 insertions, 1 deletions

diff --git a/activerecord/lib/active_record/relation/finder_methods.rb b/activerecord/lib/active_record/relation/finder_methods.rb
index 6deb9c7da8..b4a5aa0d34 100644
--- a/activerecord/lib/active_record/relation/finder_methods.rb
+++ b/activerecord/lib/active_record/relation/finder_methods.rb
@@ -354,7 +354,7 @@ module ActiveRecord
         conditions = sanitize_forbidden_attributes(conditions)
 
         if distinct_value && offset_value
-          relation = limit(1)
+          relation = except(:order).limit(1)
         else
           relation = except(:select, :distinct, :order)._select!(ONE_AS_ONE).limit!(1)
         end
diff --git a/activerecord/test/cases/finder_test.rb b/activerecord/test/cases/finder_test.rb
index 3752fd42e3..1f2058cc0a 100644
--- a/activerecord/test/cases/finder_test.rb
+++ b/activerecord/test/cases/finder_test.rb
@@ -283,6 +283,11 @@ class FinderTest < ActiveRecord::TestCase
     assert_not Post.select(:body).distinct.offset(4).exists?
   end
 
+  def test_exists_with_distinct_and_offset_and_eagerload_and_order
+    assert Post.eager_load(:comments).distinct.offset(10).merge(Comment.order(post_id: :asc)).exists?
+    assert_not Post.eager_load(:comments).distinct.offset(11).merge(Comment.order(post_id: :asc)).exists?
+  end
+
   # Ensure +exists?+ runs without an error by excluding distinct value.
   # See https://github.com/rails/rails/pull/26981.
   def test_exists_with_order_and_distinct