Stash `left_joins` into `joins` to deduplicate redundant LEFT JOIN

Originally the `JoinDependency` has the deduplication for eager loading
(LEFT JOIN). This re-uses that deduplication for `left_joins`.

And also, This makes left join order into part of joins, i.e.:

Before:

```
association joins -> stash joins (eager loading, etc) -> string joins -> left joins
```

After:

```
association joins -> stash joins (eager loading, left joins, etc) -> string joins
```

Now string joins are able to refer left joins.

Fixes #34325.
Fixes #34332.
Fixes #34536.

2 files changed, 14 insertions, 1 deletions

diff --git a/activerecord/test/cases/associations/inner_join_association_test.rb b/activerecord/test/cases/associations/inner_join_association_test.rb
index c33dcdee61..e0dac01f4a 100644
--- a/activerecord/test/cases/associations/inner_join_association_test.rb
+++ b/activerecord/test/cases/associations/inner_join_association_test.rb
@@ -29,7 +29,10 @@ class InnerJoinAssociationTest < ActiveRecord::TestCase
 
   def test_construct_finder_sql_does_not_table_name_collide_on_duplicate_associations_with_left_outer_joins
     sql = Person.joins(agents: :agents).left_outer_joins(agents: :agents).to_sql
-    assert_match(/agents_people_4/i, sql)
+    assert_match(/agents_people_2/i, sql)
+    assert_match(/INNER JOIN/i, sql)
+    assert_no_match(/agents_people_4/i, sql)
+    assert_no_match(/LEFT OUTER JOIN/i, sql)
   end
 
   def test_construct_finder_sql_does_not_table_name_collide_with_string_joins
diff --git a/activerecord/test/cases/associations/left_outer_join_association_test.rb b/activerecord/test/cases/associations/left_outer_join_association_test.rb
index 0e54e8c1b0..0a8863c35d 100644
--- a/activerecord/test/cases/associations/left_outer_join_association_test.rb
+++ b/activerecord/test/cases/associations/left_outer_join_association_test.rb
@@ -46,6 +46,12 @@ class LeftOuterJoinAssociationTest < ActiveRecord::TestCase
     assert queries.any? { |sql| /LEFT OUTER JOIN/i.match?(sql) }
   end
 
+  def test_left_outer_joins_is_deduped_when_same_association_is_joined
+    queries = capture_sql { Author.joins(:posts).left_outer_joins(:posts).to_a }
+    assert queries.any? { |sql| /INNER JOIN/i.match?(sql) }
+    assert queries.none? { |sql| /LEFT OUTER JOIN/i.match?(sql) }
+  end
+
   def test_construct_finder_sql_ignores_empty_left_outer_joins_hash
     queries = capture_sql { Author.left_outer_joins({}).to_a }
     assert queries.none? { |sql| /LEFT OUTER JOIN/i.match?(sql) }
@@ -60,6 +66,10 @@ class LeftOuterJoinAssociationTest < ActiveRecord::TestCase
     assert_raise(ArgumentError) { Author.left_outer_joins('LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"').to_a }
   end
 
+  def test_left_outer_joins_with_string_join
+    assert_equal 16, Author.left_outer_joins(:posts).joins("LEFT OUTER JOIN comments ON comments.post_id = posts.id").count
+  end
+
   def test_join_conditions_added_to_join_clause
     queries = capture_sql { Author.left_outer_joins(:essays).to_a }
     assert queries.any? { |sql| /writer_type.*?=.*?(Author|\?|\$1|\:a1)/i.match?(sql) }