ActiveRecord does not preserve order of joins

[nassredean]

On rails 5.0.7, I noticed that when chaining a join after a left join like so:

Model.left_joins(:something).joins(:something_else)

The join clause will always preceed the left join clause, which can cause problems. This is almost certainly related to #32598, but that issue was closed. Take a look at the following test case:

Steps to reproduce

Run this test:

# frozen_string_literal: true

begin
  require 'bundler/inline'
rescue LoadError => e
  warn 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  # Activate the gem you are reporting the issue against.
  gem 'activerecord', '5.0.7'
  # gem 'rails', github: 'rails/rails'
  gem 'pg'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

# Ensure backward compatibility with minitest 4.
Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: 'postgresql')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
  end

  create_table :users, force: true do |t|
  end

  create_table :comments, force: true do |t|
  end

  add_reference :posts, :user
  add_reference :comments, :user
end

class Post < ActiveRecord::Base
  belongs_to :user
end

class Comment < ActiveRecord::Base
  belongs_to :user
end

class User < ActiveRecord::Base
  has_many :posts
  has_many :comments
end

class BugTest < Minitest::Test
  def test_association_stuff
    user = User.create!
    post = Post.create!(user_id: user.id)
    Comment.create!(user_id: user.id)

    posts = Post.left_joins(:user).joins('INNER JOIN "comments" ON "comments"."user_id" = "users"."id"') # fails
    assert_equal 1, posts.length
  end
end

Expected behavior

I expect the AR query not to error and to generate the following SQL:

SELECT "posts".* FROM "posts" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" 

Actual behavior

The following incorrect SQL is generated:

SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id"

which yields a PG::UndefinedTable: ERROR: missing FROM-clause entry for table "users" error in ruby land.

It would be great if we didn't have to specifiy raw sql here, but you can do this posts = Post.left_joins(:user).joins(user: :comments) can't do this either because you will get a PG::DuplicateAlias error joining to users twice, which is a separate issue

System configuration

Rails version:
5.0.7
Latest master

Ruby version:
2.5.1

[rails-bot]

This issue has been automatically marked as stale because it has not been commented on for at least three months.
The resources of the Rails team are limited, and so we are asking for your help.
If you can still reproduce this error on the 5-2-stable branch or on master, please reply with all of the information you have about it in order to keep the issue open.
Thank you for all your contributions.