Support Optimizer Hints

We as Arm Treasure Data are using Optimizer Hints with a monkey patch
(https://gist.github.com/kamipo/4c8539f0ce4acf85075cf5a6b0d9712e),
especially in order to use `MAX_EXECUTION_TIME` (refer #31129).

Example:

```ruby
class Job < ApplicationRecord
  default_scope { optimizer_hints("MAX_EXECUTION_TIME(50000) NO_INDEX_MERGE(jobs)") }
end
```

Optimizer Hints is supported not only for MySQL but also for most
databases (PostgreSQL on RDS, Oracle, SQL Server, etc), it is really
helpful to turn heavy queries for large scale applications.

6 files changed, 45 insertions, 0 deletions

diff --git a/activerecord/lib/arel/visitors/depth_first.rb b/activerecord/lib/arel/visitors/depth_first.rb
index 92d309453c..892d670c79 100644
--- a/activerecord/lib/arel/visitors/depth_first.rb
+++ b/activerecord/lib/arel/visitors/depth_first.rb
@@ -35,6 +35,7 @@ module Arel # :nodoc: all
         alias :visit_Arel_Nodes_Ascending         :unary
         alias :visit_Arel_Nodes_Descending        :unary
         alias :visit_Arel_Nodes_UnqualifiedColumn :unary
+        alias :visit_Arel_Nodes_OptimizerHints    :unary
 
         def function(o)
           visit o.expressions
diff --git a/activerecord/lib/arel/visitors/dot.rb b/activerecord/lib/arel/visitors/dot.rb
index 6389c875cb..ffcbb7a7ab 100644
--- a/activerecord/lib/arel/visitors/dot.rb
+++ b/activerecord/lib/arel/visitors/dot.rb
@@ -82,6 +82,7 @@ module Arel # :nodoc: all
         alias :visit_Arel_Nodes_Offset            :unary
         alias :visit_Arel_Nodes_On                :unary
         alias :visit_Arel_Nodes_UnqualifiedColumn :unary
+        alias :visit_Arel_Nodes_OptimizerHints    :unary
         alias :visit_Arel_Nodes_Preceding         :unary
         alias :visit_Arel_Nodes_Following         :unary
         alias :visit_Arel_Nodes_Rows              :unary
diff --git a/activerecord/lib/arel/visitors/ibm_db.rb b/activerecord/lib/arel/visitors/ibm_db.rb
index 73166054da..0ffc0725f7 100644
--- a/activerecord/lib/arel/visitors/ibm_db.rb
+++ b/activerecord/lib/arel/visitors/ibm_db.rb
@@ -4,6 +4,14 @@ module Arel # :nodoc: all
   module Visitors
     class IBM_DB < Arel::Visitors::ToSql
       private
+        def visit_Arel_Nodes_SelectCore(o, collector)
+          collector = super
+          maybe_visit o.optimizer_hints, collector
+        end
+
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "/* <OPTGUIDELINES>#{sanitize_as_sql_comment(o).join}</OPTGUIDELINES> */"
+        end
 
         def visit_Arel_Nodes_Limit(o, collector)
           collector << "FETCH FIRST "
@@ -16,6 +24,10 @@ module Arel # :nodoc: all
           collector = visit [o.left, o.right, 0, 1], collector
           collector << ")"
         end
+
+        def collect_optimizer_hints(o, collector)
+          collector
+        end
     end
   end
 end
diff --git a/activerecord/lib/arel/visitors/informix.rb b/activerecord/lib/arel/visitors/informix.rb
index 208fa15aef..cd43be8858 100644
--- a/activerecord/lib/arel/visitors/informix.rb
+++ b/activerecord/lib/arel/visitors/informix.rb
@@ -42,10 +42,15 @@ module Arel # :nodoc: all
           collector
         end
 
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "/*+ #{sanitize_as_sql_comment(o).join(", ")} */"
+        end
+
         def visit_Arel_Nodes_Offset(o, collector)
           collector << "SKIP "
           visit o.expr, collector
         end
+
         def visit_Arel_Nodes_Limit(o, collector)
           collector << "FIRST "
           visit o.expr, collector
diff --git a/activerecord/lib/arel/visitors/mssql.rb b/activerecord/lib/arel/visitors/mssql.rb
index fdd864b40d..85815baca2 100644
--- a/activerecord/lib/arel/visitors/mssql.rb
+++ b/activerecord/lib/arel/visitors/mssql.rb
@@ -76,6 +76,15 @@ module Arel # :nodoc: all
           end
         end
 
+        def visit_Arel_Nodes_SelectCore(o, collector)
+          collector = super
+          maybe_visit o.optimizer_hints, collector
+        end
+
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "OPTION (#{sanitize_as_sql_comment(o).join(", ")})"
+        end
+
         def get_offset_limit_clause(o)
           first_row = o.offset ? o.offset.expr.to_i + 1 : 1
           last_row  = o.limit ? o.limit.expr.to_i - 1 + first_row : nil
@@ -103,6 +112,10 @@ module Arel # :nodoc: all
           end
         end
 
+        def collect_optimizer_hints(o, collector)
+          collector
+        end
+
         def determine_order_by(orders, x)
           if orders.any?
             orders
diff --git a/activerecord/lib/arel/visitors/to_sql.rb b/activerecord/lib/arel/visitors/to_sql.rb
index d0dec63860..7e3e265208 100644
--- a/activerecord/lib/arel/visitors/to_sql.rb
+++ b/activerecord/lib/arel/visitors/to_sql.rb
@@ -219,6 +219,7 @@ module Arel # :nodoc: all
         def visit_Arel_Nodes_SelectCore(o, collector)
           collector << "SELECT"
 
+          collector = collect_optimizer_hints(o, collector)
           collector = maybe_visit o.set_quantifier, collector
 
           collect_nodes_for o.projections, collector, SPACE
@@ -236,6 +237,10 @@ module Arel # :nodoc: all
           collector
         end
 
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "/*+ #{sanitize_as_sql_comment(o).join(" ")} */"
+        end
+
         def collect_nodes_for(nodes, collector, spacer, connector = COMMA)
           unless nodes.empty?
             collector << spacer
@@ -799,6 +804,14 @@ module Arel # :nodoc: all
           @connection.quote_column_name(name)
         end
 
+        def sanitize_as_sql_comment(o)
+          o.expr.map { |v| v.gsub(%r{ /\*\+?\s* | \s*\*/ }x, "") }
+        end
+
+        def collect_optimizer_hints(o, collector)
+          maybe_visit o.optimizer_hints, collector
+        end
+
         def maybe_visit(thing, collector)
           return collector unless thing
           collector << " "