When using a JOIN like .joins('INNER JOIN posts ON users.id = posts.user_id') instead of .joins(:posts), and then using .merge with a relation with another JOIN defined on (in this case) posts, the JOIN with that other table gets put in the front, which is not something that MySQL is happy about.
This description is probably a bit confusing, but I hope the gist makes clear what I mean (see below).
Steps to reproduce
See https://gist.github.com/tbreier/e388c3f4727f985126a1
Expected behavior
.merge works when the scope to be merged with a) is joined manually (not via symbol describing association) and b) contains a join with another table
Actual behavior
The gist above crashes.
# Running:
SELECT `users`.* FROM `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id`
SELECT `users`.* FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN posts ON users.id = posts.user_id
D, [2016-03-22T14:38:34.101618 #5326] DEBUG -- : User Load (0.6ms) SELECT `users`.* FROM `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id`
D, [2016-03-22T14:38:34.108212 #5326] DEBUG -- : User Load (0.6ms) SELECT `users`.* FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN posts ON users.id = posts.user_id
E
Finished in 0.025738s, 38.8537 runs/s, 0.0000 assertions/s.
1) Error:
BugTest#test_association_stuff:
ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'posts.id' in 'on clause': SELECT `users`.* FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN posts ON users.id = posts.user_id
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/mysql2-0.4.3/lib/mysql2/client.rb:107:in `_query'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/mysql2-0.4.3/lib/mysql2/client.rb:107:in `block in query'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/mysql2-0.4.3/lib/mysql2/client.rb:106:in `handle_interrupt'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/mysql2-0.4.3/lib/mysql2/client.rb:106:in `query'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `block in execute'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_adapter.rb:472:in `block in log'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activesupport-4.2.6/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `execute'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/mysql2_adapter.rb:235:in `exec_query'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract/database_statements.rb:356:in `select'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/querying.rb:39:in `find_by_sql'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/relation.rb:639:in `exec_queries'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/relation.rb:515:in `load'
/Users/tms/.rbenv/versions/2.1.2/lib/ruby/gems/2.1.0/gems/activerecord-4.2.6/lib/active_record/relation.rb:243:in `to_a'
merge_join.rb:63:in `test_association_stuff'
1 runs, 0 assertions, 0 failures, 1 errors, 0 skips
System configuration
ActiveRecord 4.2.6, mysql2 0.4.3, MySQL 5.6.27, Ruby 2.1.2
By the way, I am curious behind the rationale of converting joins contained in the scope to be merged to a LEFT OUTER JOIN. This appears to be intended, as described in #16140.
When using a JOIN like
.joins('INNER JOIN posts ON users.id = posts.user_id')instead of.joins(:posts), and then using.mergewith a relation with another JOIN defined on (in this case)posts, the JOIN with that other table gets put in the front, which is not something that MySQL is happy about.This description is probably a bit confusing, but I hope the gist makes clear what I mean (see below).
Steps to reproduce
See https://gist.github.com/tbreier/e388c3f4727f985126a1
Expected behavior
.merge works when the scope to be merged with a) is joined manually (not via symbol describing association) and b) contains a join with another table
Actual behavior
The gist above crashes.
System configuration
ActiveRecord 4.2.6, mysql2 0.4.3, MySQL 5.6.27, Ruby 2.1.2
By the way, I am curious behind the rationale of converting joins contained in the scope to be merged to a
LEFT OUTER JOIN. This appears to be intended, as described in #16140.