search

activerecord-hackery / ransack

Object-based searching.
https://activerecord-hackery.github.io/ransack/
MIT License
5.66k stars 799 forks source link

Ransack broke ActiveRecord sql when same table and different "belongs_to" joining (even though not use ransack search) #1253

Closed kei-p closed 11 months ago

kei-p commented 3 years ago

I just updated the ransack gem from 2.3.2 to 2.4.2 and found a puzzling bug. This bug is lost "JOIN" query partially when same table and different "belongs_to" joining query even though not use ransack search method. I found this bug caused by ransack 2.4.2 ( maybe 2.4.x) by below example code and results.

Results

rails ransack test
6-1-stable v2.4.2 Failed
6-0-stable v2.4.2 Failed
6-1-stable master(4caf7c2) Failed
6-0-stable v2.3.2 Pass
6-1-stable no use (comment out "ransack") Pass

Example code

unless File.exist?('Gemfile')
  File.write('Gemfile', <<-GEMFILE)
    source 'https://rubygems.org'

    # Rails master
    gem 'rails', github: 'rails/rails', branch: '6-1-stable'

    gem 'pg'
    gem 'ransack', github: 'activerecord-hackery/ransack', tag: 'v2.4.2'
  GEMFILE

  system 'bundle install'
end

require 'bundler'
Bundler.setup(:default)

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

# This connection will do for database-independent bug reports.
`psql -c "DROP DATABASE IF EXISTS ransack_bug_report;"`
`psql -c "CREATE DATABASE ransack_bug_report;"`
ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'ransack_bug_report')
ActiveRecord::Base.logger = Logger.new(STDOUT)

# Display versions.
message = "Running test case with Ruby #{RUBY_VERSION}, Active Record #{
  ::ActiveRecord::VERSION::STRING}, Arel #{Arel::VERSION} and #{
  ::ActiveRecord::Base.connection.adapter_name}"
line = '=' * message.length
puts line, message, line

ActiveRecord::Schema.define do
  create_table :tasks, force: true do |t|
    t.bigint :user_id
    t.bigint :reviewer_id
  end

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

class Task < ActiveRecord::Base
  belongs_to :user
  belongs_to :reviewer, class_name: 'User', optional: true
end

class User < ActiveRecord::Base
  has_many :tasks
end

class BugTest < Minitest::Test
  def test_active_record_join
    user = User.create!
    Task.create!(user: user, reviewer: nil)

    sql = Task.joins(:user).left_outer_joins(:reviewer).to_sql
    assert_match /INNER JOIN "users"/, sql
    assert_match /LEFT OUTER JOIN "users" "reviewers_tasks"/, sql
  end
end

Result detail ( case: "rails=6-1-stable, ransack=2.4.2" )

Fetching https://github.com/rails/rails.git
Fetching https://github.com/activerecord-hackery/ransack.git
Fetching gem metadata from https://rubygems.org/.......
Fetching gem metadata from https://rubygems.org/............
Fetching gem metadata from https://rubygems.org/............
Resolving dependencies....
Using rake 13.0.6
Using concurrent-ruby 1.1.9
Using i18n 1.8.10
Using minitest 5.14.4
Using tzinfo 2.0.4
Using zeitwerk 2.4.2
Using activesupport 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using builder 3.2.4
Using erubi 1.10.0
Using mini_portile2 2.6.1
Using racc 1.5.2
Using nokogiri 1.12.5 (x86_64-darwin)
Using rails-dom-testing 2.0.3
Using crass 1.0.6
Using loofah 2.12.0
Using rails-html-sanitizer 1.4.2
Using actionview 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using rack 2.2.3
Using rack-test 1.1.0
Using actionpack 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using nio4r 2.5.8
Using websocket-extensions 0.1.5
Using websocket-driver 0.7.5
Using actioncable 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using globalid 0.5.2
Using activejob 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using activemodel 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using activerecord 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using marcel 1.0.2
Using mini_mime 1.1.1
Using activestorage 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using mail 2.7.1
Using actionmailbox 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using actionmailer 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using actiontext 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using bundler 2.1.4
Using method_source 1.0.0
Using pg 1.2.3
Using thor 1.1.0
Using railties 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using sprockets 4.0.2
Using sprockets-rails 3.2.2
Using rails 6.1.4.1 from https://github.com/rails/rails.git (at 6-1-stable@3be6c2d)
Using ransack 2.4.1 from https://github.com/activerecord-hackery/ransack.git (at v2.4.2@7fc3166)
Bundle complete! 3 Gemfile dependencies, 44 gems now installed.
Use `bundle info [gemname]` to see where a bundled gem is installed.
====================================================================================
Running test case with Ruby 2.7.4, Active Record 6.1.4.1, Arel 10.0.0 and PostgreSQL
====================================================================================
-- create_table(:tasks, {:force=>true})
D, [2021-09-28T10:07:48.797272 #76360] DEBUG -- :    (0.3ms)  DROP TABLE IF EXISTS "tasks"
D, [2021-09-28T10:07:48.804603 #76360] DEBUG -- :    (6.8ms)  CREATE TABLE "tasks" ("id" bigserial primary key, "user_id" bigint, "reviewer_id" bigint)
   -> 0.0082s
-- create_table(:users, {:force=>true})
D, [2021-09-28T10:07:48.805365 #76360] DEBUG -- :    (0.4ms)  DROP TABLE IF EXISTS "users"
D, [2021-09-28T10:07:48.809412 #76360] DEBUG -- :    (3.9ms)  CREATE TABLE "users" ("id" bigserial primary key)
   -> 0.0047s
D, [2021-09-28T10:07:48.892205 #76360] DEBUG -- :    (3.2ms)  CREATE TABLE "ar_internal_metadata" ("key" character varying NOT NULL PRIMARY KEY, "value" character varying, "created_at" timestamp(6) NOT NULL, "updated_at" timestamp(6) NOT NULL)
D, [2021-09-28T10:07:48.912285 #76360] DEBUG -- :   ActiveRecord::InternalMetadata Load (0.4ms)  SELECT "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT $2  [["key", "environment"], ["LIMIT", 1]]
D, [2021-09-28T10:07:48.924445 #76360] DEBUG -- :   TRANSACTION (0.5ms)  BEGIN
D, [2021-09-28T10:07:48.927013 #76360] DEBUG -- :   ActiveRecord::InternalMetadata Create (1.6ms)  INSERT INTO "ar_internal_metadata" ("key", "value", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "key"  [["key", "environment"], ["value", "default_env"], ["created_at", "2021-09-28 01:07:48.923201"], ["updated_at", "2021-09-28 01:07:48.923201"]]
D, [2021-09-28T10:07:48.928197 #76360] DEBUG -- :   TRANSACTION (0.6ms)  COMMIT
Run options: --seed 23250

# Running:

D, [2021-09-28T10:07:49.034579 #76360] DEBUG -- :   TRANSACTION (0.1ms)  BEGIN
D, [2021-09-28T10:07:49.036076 #76360] DEBUG -- :   User Create (1.1ms)  INSERT INTO "users" DEFAULT VALUES RETURNING "id"
D, [2021-09-28T10:07:49.039997 #76360] DEBUG -- :   TRANSACTION (3.5ms)  COMMIT
D, [2021-09-28T10:07:49.052031 #76360] DEBUG -- :   TRANSACTION (0.1ms)  BEGIN
D, [2021-09-28T10:07:49.053070 #76360] DEBUG -- :   Task Create (0.7ms)  INSERT INTO "tasks" ("user_id") VALUES ($1) RETURNING "id"  [["user_id", 1]]
D, [2021-09-28T10:07:49.053777 #76360] DEBUG -- :   TRANSACTION (0.4ms)  COMMIT
F

Failure:
BugTest#test_active_record_join [bug_report_templates/test-ransack-join-queries.rb:64]:
Expected /LEFT OUTER JOIN "users" "reviewers_tasks"/ to match "SELECT \"tasks\".* FROM \"tasks\" INNER JOIN \"users\" ON \"users\".\"id\" = \"tasks\".\"user_id\"".

rails test bug_report_templates/test-ransack-join-queries.rb:58

Finished in 0.036198s, 27.6258 runs/s, 110.5033 assertions/s.
1 runs, 4 assertions, 1 failures, 0 errors, 0 skips
deivid-rodriguez commented 11 months ago

I'm going to close this since #1447 is likely to have fixed this. Feel free to reopen if not though!