Arel and reverse_order interaction

arel_reverse_order_issues.rb

# frozen_string_literal: true

# In this example we have an application concerned about the length of a string,
# and is pushing the work to the database for efficiency.  There is an index
# on the length of a Post's title, and the scopes and orderings have been 
# neatly refactored to minimise the amount of Arel required.
#
# A bug has now arisen in the behaviour of `last`.  It is giving the first.
#
# Subsequent investigation has shown that customers were also complaining that
# sorting by lower-case titles also has a problem.
#
# The issue occurs because `reverse_order` mishandles Arel nodes that are
# neither strings nor attributes nor already explicitly ordered, by just passing
# them straight through.
#
# Since any valid SQL sort expression with the default ordering can, by definition,
# be reversed with a DESC, and since it a SQL syntax error to have the DESC keyword
# anywhere but following a sort expression (you can't even place it inside a
# parenthetical grouping), it follows that any valid Arel::Nodes::NodeExpression
# can & should be reversed with a `desc` (except for an Arel::Nodes::Ordering which
# knows how to flip its direction).
#
# However, this is not what happens:
#

require "bundler/inline"

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

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem "rails", github: "rails/rails"
  gem "sqlite3"
  gem "pry"
end

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

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.string :title
    t.index 'length(title)'
  end
end

class Post < ActiveRecord::Base
  has_many :comments
  scope :short_titles, -> { where(title_length.lt 8) }
  scope :title_length_order, -> { order title_length }

  class << self
    delegate :[], to: :arel_table

    def title_length
      Arel::Nodes::NamedFunction.new "LENGTH", [arel_table[:title]]
    end

    def titles
      pluck(:title)
    end
  end
end

Post.create! [
  { title: 'Post A' },
  { title: 'Post bb' },
  { title: 'Post CCC' },
  { title: 'Post dddd' },
]

class BugTest < Minitest::Test
  def test_short_titles_scope
    assert_equal Post.all.select { |post| post.title.length < 8 }.to_set(&:id), Post.short_titles.ids.to_set
  end

  def test_length_last
    assert_equal Post.titles.sort_by(&:length).last, Post.title_length_order.last.title
  end

  def test_reverse_function_order
    assert_equal Post.titles.sort_by(&:downcase).reverse, Post.order(Post[:title].lower).reverse_order.titles
  end

  #
  # Some extra examples of readily reversible sort expressions
  # that reverse_order neither reverses nor complains about:
  #

  # Arithmetic
  def test_reverse_multiplication_order
    assert_equal Post.ids.reverse, Post.order(Post[:id] * 2).reverse_order.ids
  end

  # Grouping
  def test_reverse_addition_order
    assert_equal Post.ids.reverse, Post.order(Post[:id] + 2).reverse_order.ids
  end

  # Case
  def test_reverse_case_order
    post_table = Post.arel_table
    table_id = Post[:id]

    case_node = Arel::Nodes::Case.new(table_id).when(nil).then(nil).else(table_id) 
    assert_equal Post.ids.reverse, Post.order(case_node).reverse_order.ids
  end
end