Handle UPDATE/DELETE with OFFSET in Arel

2 files changed, 18 insertions, 13 deletions

diff --git a/activerecord/lib/arel/visitors/mysql.rb b/activerecord/lib/arel/visitors/mysql.rb
index 0f7d5aa803..eb8a449079 100644
--- a/activerecord/lib/arel/visitors/mysql.rb
+++ b/activerecord/lib/arel/visitors/mysql.rb
@@ -56,18 +56,6 @@ module Arel # :nodoc: all
           super
         end
 
-        def visit_Arel_Nodes_UpdateStatement(o, collector)
-          collector << "UPDATE "
-          collector = visit o.relation, collector
-
-          unless o.values.empty?
-            collector << " SET "
-            collector = inject_join o.values, collector, ", "
-          end
-
-          collect_where_for(o, collector)
-        end
-
         def visit_Arel_Nodes_Concat(o, collector)
           collector << " CONCAT("
           visit o.left, collector
@@ -77,7 +65,23 @@ module Arel # :nodoc: all
           collector
         end
 
+        def build_subselect(key, o)
+          subselect = super
+
+          # Materialize subquery by adding distinct
+          # to work with MySQL 5.7.6 which sets optimizer_switch='derived_merge=on'
+          subselect.distinct unless subselect.limit || subselect.offset || subselect.orders.any?
+
+          Nodes::SelectStatement.new.tap do |stmt|
+            core = stmt.cores.last
+            core.froms = Nodes::Grouping.new(subselect).as("__active_record_temp")
+            core.projections = [Arel.sql(quote_column_name(key.name))]
+          end
+        end
+
         def collect_where_for(o, collector)
+          return super if o.offset
+
           unless o.wheres.empty?
             collector << " WHERE "
             collector = inject_join o.wheres, collector, " AND "
diff --git a/activerecord/lib/arel/visitors/to_sql.rb b/activerecord/lib/arel/visitors/to_sql.rb
index 575bfd6e36..0172204fc8 100644
--- a/activerecord/lib/arel/visitors/to_sql.rb
+++ b/activerecord/lib/arel/visitors/to_sql.rb
@@ -88,6 +88,7 @@ module Arel # :nodoc: all
           core.wheres      = o.wheres
           core.projections = [key]
           stmt.limit       = o.limit
+          stmt.offset      = o.offset
           stmt.orders      = o.orders
           stmt
         end
@@ -800,7 +801,7 @@ module Arel # :nodoc: all
         end
 
         def collect_where_for(o, collector)
-          if o.orders.empty? && o.limit.nil?
+          if o.orders.empty? && o.limit.nil? && o.offset.nil?
             wheres = o.wheres
           else
             wheres = [Nodes::In.new(o.key, [build_subselect(o.key, o)])]