Steps to reproduce
# frozen_string_literal: true
require "bundler/inline"
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
if ENV["RAILS_BRANCH"]
gem "rails", github: "rails/rails", branch: ENV["RAILS_BRANCH"]
else
gem "rails", "5.2.3"
end
gem "sqlite3"
end
require "active_record"
require "minitest/autorun"
require "logger"
# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Schema.define do
create_table(:users, force: true)
create_table :posts, force: true do |t|
t.integer :user_id
end
create_table :comments, force: true do |t|
t.integer :post_id
end
end
class User < ActiveRecord::Base
has_many :posts
has_many :comments, through: :posts
end
class Post < ActiveRecord::Base
belongs_to :user
has_many :comments
scope :no_comments, lambda {
left_joins(:comments).where(comments: { id: nil })
}
end
class Comment < ActiveRecord::Base
belongs_to :post
end
puts
puts "ActiveRecord.version = #{ActiveRecord.version}"
puts
class BugTest < Minitest::Test
def test_to_sql
user = User.create(id: 1)
expected = 'SELECT "comments".* FROM "comments" INNER JOIN "posts" ON "comments"."post_id" = "posts"."id" LEFT OUTER JOIN "comments" "comments_posts" ON "comments_posts"."post_id" = "posts"."id" WHERE "posts"."user_id" = 1 AND "comments"."id" IS NULL'
assert_equal expected, user.comments.merge(Post.no_comments).to_sql
end
def test_to_execute_sql
user = User.create(id: 2)
assert_equal 0, user.comments.merge(Post.no_comments).count
end
endv5.2.3
$ ruby issue.rb
ActiveRecord.version = 5.2.3
Run options: --seed 43003
# Running:
D, [2019-05-17T13:54:35.556394 #65246] DEBUG -- : (0.1ms) begin transaction
D, [2019-05-17T13:54:35.558423 #65246] DEBUG -- : User Create (0.3ms) INSERT INTO "users" ("id") VALUES (?) [["id", 2]]
D, [2019-05-17T13:54:35.559239 #65246] DEBUG -- : (0.1ms) commit transaction
D, [2019-05-17T13:54:35.594663 #65246] DEBUG -- : (0.3ms) SELECT COUNT(*) FROM "comments" INNER JOIN "posts" ON "comments"."post_id" = "posts"."id" LEFT OUTER JOIN "comments" "comments_posts" ON "comments_posts"."post_id" = "posts"."id" WHERE "posts"."user_id" = ? AND "comments"."id" IS NULL [["user_id", 2]]
.D, [2019-05-17T13:54:35.595758 #65246] DEBUG -- : (0.1ms) begin transaction
D, [2019-05-17T13:54:35.596739 #65246] DEBUG -- : User Create (0.2ms) INSERT INTO "users" ("id") VALUES (?) [["id", 1]]
D, [2019-05-17T13:54:35.597295 #65246] DEBUG -- : (0.1ms) commit transaction
.
Finished in 0.050594s, 39.5304 runs/s, 39.5304 assertions/s.
2 runs, 2 assertions, 0 failures, 0 errors, 0 skips6-0-stable
$ RAILS_BRANCH=6-0-stable ruby issue.rb
ActiveRecord.version = 6.0.0.rc1
Run options: --seed 2131
# Running:
D, [2019-05-17T13:55:29.849955 #65313] DEBUG -- : (0.1ms) begin transaction
D, [2019-05-17T13:55:29.850242 #65313] DEBUG -- : User Create (0.1ms) INSERT INTO "users" ("id") VALUES (?) [["id", 2]]
D, [2019-05-17T13:55:29.850610 #65313] DEBUG -- : (0.1ms) commit transaction
D, [2019-05-17T13:55:29.885159 #65313] DEBUG -- : (0.3ms) SELECT COUNT(*) FROM "comments" LEFT OUTER JOIN "comments" "comments_posts" ON "comments_posts"."post_id" = "posts"."id" INNER JOIN "posts" ON "comments"."post_id" = "posts"."id" WHERE "posts"."user_id" = ? AND "comments"."id" IS NULL [["user_id", 2]]
E
Error:
BugTest#test_to_execute_sql:
ActiveRecord::StatementInvalid: SQLite3::SQLException: ON clause references tables to its right
rails test issue.rb:68
D, [2019-05-17T13:55:29.886725 #65313] DEBUG -- : (0.1ms) begin transaction
D, [2019-05-17T13:55:29.887007 #65313] DEBUG -- : User Create (0.1ms) INSERT INTO "users" ("id") VALUES (?) [["id", 1]]
D, [2019-05-17T13:55:29.887502 #65313] DEBUG -- : (0.1ms) commit transaction
F
Failure:
BugTest#test_to_sql [issue.rb:65]:
--- expected
+++ actual
@@ -1 +1 @@
-"SELECT \"comments\".* FROM \"comments\" INNER JOIN \"posts\" ON \"comments\".\"post_id\" = \"posts\".\"id\" LEFT OUTER JOIN \"comments\" \"comments_posts\" ON \"comments_posts\".\"post_id\" = \"posts\".\"id\" WHERE \"posts\".\"user_id\" = 1 AND \"comments\".\"id\" IS NULL"
+"SELECT \"comments\".* FROM \"comments\" LEFT OUTER JOIN \"comments\" \"comments_posts\" ON \"comments_posts\".\"post_id\" = \"posts\".\"id\" INNER JOIN \"posts\" ON \"comments\".\"post_id\" = \"posts\".\"id\" WHERE \"posts\".\"user_id\" = 1 AND \"comments\".\"id\" IS NULL"
rails test issue.rb:61
Finished in 0.080781s, 24.7583 runs/s, 12.3791 assertions/s.
2 runs, 1 assertions, 1 failures, 1 errors, 0 skipsExpected behavior
I expected the same behavior as v5.2.3.
Actual behavior
ActiveRecord builds a query which the order of INNER JOIN and LEFT OUTER JOIN reversed.
This query occurs an error because it can not reference the table.
System configuration
Rails version: 6-0-stable
Ruby version: ruby 2.6.3p62 (2019-04-16 revision 67580) [x86_64-darwin18]
Steps to reproduce
v5.2.3
6-0-stable
Expected behavior
I expected the same behavior as v5.2.3.
Actual behavior
ActiveRecord builds a query which the order of
INNER JOINandLEFT OUTER JOINreversed.This query occurs an error because it can not reference the table.
System configuration
Rails version: 6-0-stable
Ruby version: ruby 2.6.3p62 (2019-04-16 revision 67580) [x86_64-darwin18]