Missing proper implementation for Rails 6 optimizer_hints

[wpolicarpo]

Issue

Rails 6 added a new method called optimizer_hints to allow developers to tweak the query optimizer via query hints.

The SQL Server adapter does not have a proper implementation for that new method and the SQL produced isn't optimized as expected.

To make things worse, SQL Server has both query hints and table hints. Looks like what Rails implements would fit better in the query hints scope for SQL Server and maybe we should also implement a way of setting table hints in the future as part of SQL server adapter.

Expected behavior

Query hints should be applied properly.

Actual behavior

The SQL generated by the adapter is incorrect and query hints are not applied.

How to reproduce

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "pry-byebug"
  gem "tiny_tds"
  gem "activerecord", "6.0.3.5"
  gem "activerecord-sqlserver-adapter", "6.0.1"
end

require "active_record"
require "minitest/autorun"
require "logger"
require "pry"

ActiveRecord::Base.establish_connection(
  adapter:  "sqlserver",
  timeout:  5000,
  pool:     100,
  encoding: "utf8",
  database: "test_database",
  username: "SA",
  password: "super01S3cUr3",
  host:     "localhost",
  port:     1433,
)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  drop_table :bug_tests rescue nil

  create_table :bug_tests, force: true do |t|
    t.bigint :external_id
  end
end

class BugTest < ActiveRecord::Base
end

class TestBugTest < Minitest::Test
  def test_optimizer_hints
    expected_sql = "SELECT [bug_tests].* FROM [bug_tests] OPTION (MAXDOP 2)"
    current_sql = BugTest.optimizer_hints("OPTION (MAXDOP 2)").to_sql
    assert_equal expected_sql, current_sql
  end
end

Details

• Rails version: 6.0.3.5
• SQL Server adapter version: 6.0.1