ActiveRecord left joins out of order

[ryan-plated]

Steps to reproduce

Combine sql left join with a arel join causes wrong order

https://gist.github.com/ryan-plated/cae50e8eceb621f8a76d41f2e6d1b5ec

Expected behavior

should keep order

Actual behavior

goes out of order

System configuration

Rails version: master

Ruby version: 2.5.0

[AlexKovynev]

https://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql

"Outer joins are not associative"

[ryan-plated]

I am not sure what you are trying to communicate. I read the post and it says outer joins order does matter.

[AlexKovynev]

Read post carefully, order of params does matter (tables, columns), not joins itself.

[rafaelfranca]

Could you explain better the expected behavior and the actual behavior? "should keep order" doesn't tell anything to me. Order of what?

[rafaelfranca]

I think it is related to #15461

[ryanong]

YEA that is exactly it! I'll gladly pick up the stale PR! I just didn't even know where to start.

[heaven]

Here is our example that raises the Unknown column error:

scope :expired, ->(service, time = 1.month) {
  distinct.
    left_joins(:timestamps).
    joins("LEFT JOIN timestamp_kinds...")
}

[ryan-plated]

This might take a while to fix. Right now joins bucketed together.

rails/activerecord/lib/active_record/relation/query_methods.rb

Line 1018 in 7d2400a

def build_join_query(manager, buckets, join_type, aliases)

String joins may or may not have a left join contained in them so they may need to keep order with outer joins.

[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.

[ismailallocator]

This is still an issue

left_joins(:funds) .joins("LEFT JOIN visible_funds ON funds.id = visible_funds.fund_id AND visible_funds.company_id = #{company.id}")

returns error.

[dgmora]

Is there an active PR/issue for this (still existing) issue? #15461 is closed and that one links to #24281, which is kinda related but is not the same, I think

[hassanrehman]

+1 for PR

[mateuscruz]

I'm willing to create a PR for this. I ran into this issue earlier today.

[khiav223577]

Did this bug be fixed? I ran into this issue, too. (Rails 5.2.4)

[kamipo]

Fixed by #35864 in Rails 6.0.