From 6d40d2d3d1f3766551e74607a718a5ec97963bbf Mon Sep 17 00:00:00 2001 From: Ryuta Kamizono Date: Sun, 30 Sep 2018 20:17:41 +0900 Subject: Handle UPDATE/DELETE with OFFSET in Arel --- activerecord/lib/active_record/relation.rb | 6 ++++-- activerecord/lib/arel/nodes/delete_statement.rb | 6 ++++-- activerecord/lib/arel/nodes/update_statement.rb | 6 ++++-- activerecord/lib/arel/tree_manager.rb | 5 +++++ activerecord/lib/arel/visitors/mysql.rb | 28 ++++++++++++++----------- activerecord/lib/arel/visitors/to_sql.rb | 3 ++- 6 files changed, 35 insertions(+), 19 deletions(-) diff --git a/activerecord/lib/active_record/relation.rb b/activerecord/lib/active_record/relation.rb index 5bad5bfcc2..d8cff30b88 100644 --- a/activerecord/lib/active_record/relation.rb +++ b/activerecord/lib/active_record/relation.rb @@ -356,11 +356,12 @@ module ActiveRecord stmt.set Arel.sql(klass.sanitize_sql_for_assignment(updates, table.name)) end - if has_join_values? || offset_value + if has_join_values? @klass.connection.join_to_update(stmt, arel, arel_attribute(primary_key)) else stmt.key = arel_attribute(primary_key) stmt.take(arel.limit) + stmt.offset(arel.offset) stmt.order(*arel.orders) stmt.wheres = arel.constraints end @@ -484,11 +485,12 @@ module ActiveRecord stmt = Arel::DeleteManager.new stmt.from(table) - if has_join_values? || offset_value + if has_join_values? @klass.connection.join_to_delete(stmt, arel, arel_attribute(primary_key)) else stmt.key = arel_attribute(primary_key) stmt.take(arel.limit) + stmt.offset(arel.offset) stmt.order(*arel.orders) stmt.wheres = arel.constraints end diff --git a/activerecord/lib/arel/nodes/delete_statement.rb b/activerecord/lib/arel/nodes/delete_statement.rb index 5be42a084a..a419975335 100644 --- a/activerecord/lib/arel/nodes/delete_statement.rb +++ b/activerecord/lib/arel/nodes/delete_statement.rb @@ -3,7 +3,7 @@ module Arel # :nodoc: all module Nodes class DeleteStatement < Arel::Nodes::Node - attr_accessor :left, :right, :orders, :limit, :key + attr_accessor :left, :right, :orders, :limit, :offset, :key alias :relation :left alias :relation= :left= @@ -16,6 +16,7 @@ module Arel # :nodoc: all @right = wheres @orders = [] @limit = nil + @offset = nil @key = nil end @@ -26,7 +27,7 @@ module Arel # :nodoc: all end def hash - [self.class, @left, @right, @orders, @limit, @key].hash + [self.class, @left, @right, @orders, @limit, @offset, @key].hash end def eql?(other) @@ -35,6 +36,7 @@ module Arel # :nodoc: all self.right == other.right && self.orders == other.orders && self.limit == other.limit && + self.offset == other.offset && self.key == other.key end alias :== :eql? diff --git a/activerecord/lib/arel/nodes/update_statement.rb b/activerecord/lib/arel/nodes/update_statement.rb index 017a553c4c..cfaa19e392 100644 --- a/activerecord/lib/arel/nodes/update_statement.rb +++ b/activerecord/lib/arel/nodes/update_statement.rb @@ -3,7 +3,7 @@ module Arel # :nodoc: all module Nodes class UpdateStatement < Arel::Nodes::Node - attr_accessor :relation, :wheres, :values, :orders, :limit, :key + attr_accessor :relation, :wheres, :values, :orders, :limit, :offset, :key def initialize @relation = nil @@ -11,6 +11,7 @@ module Arel # :nodoc: all @values = [] @orders = [] @limit = nil + @offset = nil @key = nil end @@ -21,7 +22,7 @@ module Arel # :nodoc: all end def hash - [@relation, @wheres, @values, @orders, @limit, @key].hash + [@relation, @wheres, @values, @orders, @limit, @offset, @key].hash end def eql?(other) @@ -31,6 +32,7 @@ module Arel # :nodoc: all self.values == other.values && self.orders == other.orders && self.limit == other.limit && + self.offset == other.offset && self.key == other.key end alias :== :eql? diff --git a/activerecord/lib/arel/tree_manager.rb b/activerecord/lib/arel/tree_manager.rb index 149c69ce7a..0476399618 100644 --- a/activerecord/lib/arel/tree_manager.rb +++ b/activerecord/lib/arel/tree_manager.rb @@ -10,6 +10,11 @@ module Arel # :nodoc: all self end + def offset(offset) + @ast.offset = Nodes::Offset.new(Nodes.build_quoted(offset)) if offset + self + end + def order(*expr) @ast.orders = expr self 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)])] -- cgit v1.2.3