Invalid references to table aliases when joining multiple tables

[yan-hoose]

First of all I'd like to thank the creators and maintainers of Ransack - it's been a valuable tool in many of my projects. Projects ranging from hobby stuff to big commercial applications. Thank you for your work!

Now, I ran into an issue where an invalid table alias is being used in the generated SQL. There are other issues currently open here that are similar but slightly different, that is why I created a new issue. In addition, many people reported here that their issue disappeared with an upgrade to Rails 6.1, but this issue persists with 6.1 too.

Consider this test case:

require 'bundler/inline'

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

  #gem 'activerecord', '6.0.4', require: 'active_record'
  #gem 'activerecord', '6.1.4', require: 'active_record'
  gem 'activerecord', github: 'rails/rails', branch: '6-1-stable', require: 'active_record'

  gem 'ransack', github: 'activerecord-hackery/ransack'
  #gem 'ransack', '2.4.2'
  gem 'sqlite3'
end

require 'minitest/autorun'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')
ActiveRecord::Base.logger = Logger.new(STDOUT)

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

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

  create_table :groups_users, force: true do |t|
    t.integer :group_id
    t.integer :user_id
  end

  create_table :tickets, force: true do |t|
    t.integer :created_by_id
    t.integer :user_id
  end
end

class User < ActiveRecord::Base
  has_many :groups_users
  has_many :groups, through: :groups_users
end

class GroupsUser < ActiveRecord::Base
  belongs_to :group
  belongs_to :user
end

class Group < ActiveRecord::Base
  has_many :groups_users
  has_many :users, through: :groups_users
end

class Ticket < ActiveRecord::Base
  belongs_to :created_by, class_name: 'User'
  belongs_to :user
end

class BugTest < Minitest::Test
  def test_bug
    q = Ticket.ransack({created_by_groups_users_group_id_eq: 1})

    tickets = q.result.joins('LEFT OUTER JOIN users ON tickets.user_id = users.id')

    puts tickets.to_sql
  end
end

The resulting SQL is this:

SELECT "tickets".*
FROM "tickets"
LEFT OUTER JOIN "users" "created_bies_tickets" ON "created_bies_tickets"."id" = "tickets"."created_by_id"
LEFT OUTER JOIN "groups_users" ON "groups_users"."user_id" = "users"."id"
LEFT OUTER JOIN users ON tickets.user_id = users.id
WHERE "groups_users"."group_id" = 1;

As you can see line nr 4 of this SQL it references the table users.id, even though it should use the alias of created_bies_tickets.id.

This results in an error message on PostgreSQL 13.3:

ERROR:  invalid reference to FROM-clause entry for table "users"
LINE 1: ...JOIN "groups_users" ON "groups_users"."user_id" = "users"."i...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "created_bies_tickets".

This invalid reference only happens when I add a manual JOIN to the result of Ransack query. Without the manual JOIN, the query will be valid because it does not have to use a table alias:

SELECT "tickets".*
FROM "tickets"
LEFT OUTER JOIN "users" ON "users"."id" = "tickets"."created_by_id"
LEFT OUTER JOIN "groups_users" ON "groups_users"."user_id" = "users"."id"
WHERE "groups_users"."group_id" = 1

Versions which I tested and where this issue manifests:
Ruby: 2.6.7
Rails: 6.0.4, 6.1.4, 6-1-stable
Ransack: 2.4.2, master
PostgreSQL: 13.3

[ozzyaaron]

Is this similar/same as #1217 ?

That issue is about table aliasing being broken under Rails 6.1

[yan-hoose]

These two look pretty similar but with the difference that your issue references an alias that has not been defined but in my case the alias has been defined but is not being used.

[ozzyaaron]

Thanks @yan-hoose you're right!

I wonder if they're related in some way as in ours the problem is losing the table alias reference for the clause and in your case it creates an alias for the joins but then doesn't use it correctly.

It is almost like there is some logic trying to de-dupe joins that is being done incorrectly and it effects your query at the join/gathering phase and effects ours in the clause phase.

Hopefully either of these issues will see some attention :)

[ozzyaaron]

This looks related to a Rails bug.

#1217 (comment)

[ozzyaaron]

Here is a fix that fixed the issues for us, but may re-introduce an eager load issue.

#1217 (comment)

[yan-hoose]

Thanks Aaron! I'll check it out!

[deivid-rodriguez]

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